Hello,

I presently have an excel model for our farms financials, that I would like to recreate in power pivot, query, etc. it presently is made up of different excel workbooks that all inter connect:

I used, to note my main question. 

I AM OPEN TO ANY ADVISE, please keep in mind My accounting skills are self taught and I am working to improve. My system takes into account several different cost factors (ranch cost, equipment repair, overhead) and automates the allocation of these cost to our crops, so we can look at actual cost as a whole. We don’t do monthly journal entries as our books for the accountant are done in cash, but we need to look them more on the accrual side for budgeting. 

Present System Includes:

1.  50+ Profit and Loss statements (pulled from QuickBooks reports, from CVS files, filtered by different classes).

a. 15+ For ranches and used with our ranch budgets.

b. 30+ for crop (classes, in QB)

c.  5+ for overhead allocation.

This way is not automated and required my to save the 50+CVS files, open each CVS files to re-save as Excel files, then pulled them in to one Excel workbook with 50+ sheets (formate chart accounts rows and columns months, over a 3 year period)

2. Budget for each of the 50+Profit and loss statement (formate, Chart of account rows, columns in months),

a. Crop and Ranch are in per acre

b. 5+ Allocated are total cost, overhead, or more fixed cost like office rent. 

4. Excel data workbook for use with calculations.

a. acre for crop break down per ranch. Ex Romain 50 on MB2 ranch, 25 on Sam Ranch.

b. Depreciation

c. % calculation for how each 5+ allocated accounts will spread across each crop for each month (rows crops, Columns month)

The end results are:

1. Excel Workbook that contain

a. Cash flow

b. Profit loss for entire farm

c. Profit loss for each crop (with all allocation assigned) as a whole and per acre. (Formate, chart of account rows, columns month for actual and budget) 

d. to be able to print, each one has a formatted print sheet that is linked to the calculating sheet. Ex Romain has 6 sheets 1. Calculations total by month, 2. calculations total by acre, 3 &4 print sheet for each one by month and 5&6 totals (to look at all three years on one page total and per acre)

NEW METHOD: 

Would included the following:

1. Data “Transaction data” pulled from Quickbooks

a. ”Transaction data” saved fillered by

30+ one for each crop* (in QB as class)

5+ one for each allocation** (in QB as class)

15+ one for each Ranch***(in QB as Jobs)

Possibly, 1 master transaction data, to conspire to the about 50+ to make sure I have everything. 

Is this the best option, worried it will creat lengthy updates. 

*crop would need to manipulated to per acres

**allocation would need to be manipulated to % for each crop (class) and sum with above crop transactions to be included when looking at crops (class)

***Ranch would need to manipulated to per acres, and then sum with crop (class) per acre. 

2. Jobs dim including acre column for calculations, from planting schedule matching QB Jobs

3. Date Dim

4. Budget data for crop and each per acre, and allocated class.

4. Depreciation information (as we use straight line for reports, not what’s in QB.  

With my newly acquired skills,

I am thinking all calculations are done in query or power query and then mapped in power pivot (as apposed to pivot, since they need to be mapped)? 

The end results need to have the capability to drill up and down to crop total, per acre for crop, and field (job) cost. With charts to show per acre crop cost form one planting to another. Ex lettuce planted in winter of 2018, winter 2019, winter 2020. The cost might happen over an 18 month calendar time frame. 

Not an easy model, but feel confided query and power pivot have a lot to offer and are better options then my current excel workbook with links to connect workbooks. 

THANK YOU FOR ANY AND ALL HELP!

Alicia