Master Budget Course

First, let's remind ourselves of what the "base case" looks like. There is, of course, no change from the master budget in the level of sales and raw material costs.

What follows is a summarised version of the income Statement, Balance Sheet and Cash Flow of the Software Factory. The company budgets to earn an after tax profit of $491,970 for the year and have $338,676 in cash at the end of the period.

The "Very Best Case" shows a 10% improvement in the level of sales and 5% improvement in the cost of raw materials from the master budget.

The company under the "Very Best Case" scenario would expect to earn an after tax profit of $799,585 for the year and have $930,770 in cash at the end of the period.

The "Very Best Case" shows a 10% reduction in the level of sales and 5% increase in the cost of raw materials from the master budget.

The company under the "Very Worst Case" scenario would expect to earn an after tax profit of $211,077 for the year and have just $56,412 in cash at the end of the period.

We can also put a summary of the what if scenarios in a table and show each case against some key variables such as the Sales Revenue and Net Profit after Tax for the period and Cash on Hand

We can see that moving from the Very Worst to the Very Best case gives rise to major changes in all major variables. Sales would increase by 41% but Net Profit after Tax increases by 300% and Cash on Hand by 1,048%!.

You will have noticed that the analyses in the previous section changed significant variables such as Sales Revenue and yet the Balance Sheet flexed and still balanced. That was because the Budget model for the company was built in a spreadsheet which was designed to be fully integrated and support flexing.

Microcomputer spreadsheets are very useful tools in the support of the preparation of Budgets. With the multi dimensional spreadsheets now available such as Lotus 1-2-3 Release 4.0 and the workbook feature of Microsoft Excel, we can build spreadsheets that contain key elements of information in smaller, easy to audit and maintain spreadsheets. We use linking between the smaller spreadsheets to build an overall spreadsheet that is completely integrated and flexible. A suite of spreadsheets that might make up the overall package and some of the many ways in which they might be linked are shown below:

Some of the formulae that might be used include "IF" statements and formulae that choose some value based upon a predetermined value such as "CHOICE" and "VLOOKUP" and "HLOOKUP".

Copyright (c) 1997 ANet - Accounting Education Resources.