I just got through making a comprehensive budget/cash forecast for our local Habitat affiliate. I made a budget for affiliate two years ago but I was dissatisfied with the results. I plugged the historical values from our income statement into a spreadsheet and after a few calculations I plugged the resulting values back into QuickBooks. QuickBooks integrates the budget into a couple of reports but the focus is on the income sheet. I published the standard QuickBooks Budget report for a year but we spent few words discussing it except for the fact that we were not raising enough funds to support our proposed construction plan. The budget accomplished little besides creating work for me because it focused on income and expense acoounts and the key Habitat board decisions concern asset accounts. Since then I have thought a lot about budgeting. What I consider to be key indicators for the affiliate led me to a refined conclusion. We need a budget to support board decisions on whether we should build houses, purchase land, and expand our building capacity by hiring personnel. Our capital expenditure plans are directly related to our ability to raise funds and our cash flow.
For a Habitat affiliate the most important cash flows, mortgages receivable, home owner principal payments and construction costs, are asset accounts and do not appear on the income statement. As a result a “useful” budget for a Habitat affiliate must establish goals not only for the income and expense accounts but for the mortgage receivable, construction in process, and land inventory accounts. The first time I made a budget I “winged” it in this area because the construction plan was not available. I knew this was important information but you cannot budget items you do not have a plan for. Since then I have convinced most of the board on the importance of this part of the planning process. Our cash situation is much tighter than two years ago so there considerably less wiggle room. The board placed a much higher priority on planning and executing the plan when I told them we did not have the money.
The budget is almost complete for this year. I modeled the format of the budget on the existing QuickBooks Income and Cash Flow statements. I did re-arrange the Cash Flow statement to highlight the essentials, fund raising/operating costs, home owner principal payments, and construction expenditures. I am going to use spreadsheets to create future financial statements for future time periods that look a lot like the existing statements. Although there are existing budget reports in QuickBooks, they do not model the cash flow adequately. Although I loathe to use reports outside of QuickBooks because it will take additional time on my part, the budget reports in QuickBooks do not do the job for us because of our cash flow forecasting needs.
I used this tutorial, Tutorial: Modeling and What if? analysis with pivot tables – 20 Jul 2006 , on pivot tables to provide me with an easier to use mechanism to summarize data. Basically I have a row with monthly values for all of the pertinent accounts I need to reconstruct the income and cash flow statements. I started off by modeling the first nine months of this fiscal year and making sure it matched existing accounting reports. Then I added three more rows for the last quarter and estimated the values. We should have a good cash forecast for the fiscal year end. My final trick is to model a minimal balance statement.
Putting together a construction plan for the next fiscal year is going to be difficult since the construction/fund raising plan is still in a state of flux. We have land we do not want to build on and land we cannot build on without significant capital improvements. This probably means we need to buy additional lots. The best I can do is guess on the plan. This should be beneficial to the board since they tend to focus best when I give them construction start and finish dates. By tying together the fund raising plan to our construction plan via the cash flow statement, the board should keep focused on the key issues.
