Sponsored links:

If you want to build your own budgeting and forecasting software but don’t know how, you have come to the right place.  I am going to teach you how to create your own budgeting and forecasting software in the step by step in this blog.

Microsoft excel spreadsheet is one of the cheapest and easiest software to use for create budgeting and forecasting software. So I will be using excel and show you step by step on how to build the budgeting and forecasting software.  Upon completion, you will have a spreadsheet that automatic the process of budgeting and forecasting thereby producing a budget and forecast report.

Understanding Top-down and Bottom-up Budgeting Method

From my many years of experience in building budgeting and forecasting software, the best way is to start with the sales figures.  I will use a top-down method which is much easier than the bottom-up method.

For the benefit of those who are not familiar with what is top-down method, I will briefly explain it in the following sentences.  A top-down method is a method whereby you forecast the final income statement first and then you work backwards to breakdown the main revenues and expenses  into detailed revenue or expense lines.

Example, your total sales is $100,000.  Your detailed revenue lines are Product A -$20,000, Product B – $50,000 and Product C – $30,000.

Building budgeting and forecasting spreadsheet

Assuming you are asked to do a budget and forecast for a company and you are given the following details and assumptions:

1. Actual sales $1,200,000 and forecast an incremental of 20% for next four years.

Workings :

Year 2 =  $1,200,000 (Year 1) x 20% + Year 1 = $1,440,000

Year 3 =  $1,440,000 (Year 2) x 20% + Year 2 = $1,728,000

Year 4 =  $1,728,000 (Year 3) x 20% + Year 3 = $2,073,600

Year 5 =  $2,073,600 (Year 4) x 20% + Year 4 = $2,488,320

2. Actual cost of sales $600,000 and forecast an incremental of 20% for the next four years.

Workings :

Year 2 =  $600,000 (Year 1) x 20% + Year 1 = $720,000

Year 3 =  $720,000 (Year 2) x 20% + Year 2 = $864,000

Year 4 =  $864,000 (Year 3) x 20% + Year 3 = $1,036,800

Year 5 =  $1,036,800 (Year 4) x 20% + Year 4 = $1,244,160

3. Actual administrative cost $200,000 and forecast an increase of 10% for next four years.

Year 2 =  $200,000 (Year 1) x 10% + Year 1 = $220,000

Year 3 =  $180,000 (Year 2) x 10% + Year 2 = $242,000

Year 4 =  $162,000 (Year 3) x 10% + Year 3 = $266,200

Year 5 =  $145,800 (Year 4) x 10% + Year 4 = $292,820

4. Actual selling and marketing expense is $100,000 and forecast an incremental of 30% for the next four years.

Year 2 =  $100,000 (Year 1) x 30% + Year 1 = $130,000

Year 3 =  $130,000 (Year 2) x 30% + Year 2 = $169,000

Year 4 =  $169,000 (Year 3) x 30% + Year 3 = $219,700

Year 5 =  $219,700 (Year 4) x 30% + Year 4 = $285,610

Now setup up your excel spreadsheet as follows :

or Download spreadsheet

In the next few postings, I will show you how to breakdown the Top-Bottom budgeting and forecasting report by detailed revenue and expense lines.

If you cannot wait for my postings, you can easily google for some softwares to help you with your budgeting. You will be able to see many relevant sites in the search results.

It is never wrong to invest to educate yourself.