Master Budget Course – пособие по разработке модели бюджетов компании

0
ПОДЕЛИТЬСЯ
735
ПРОСМОТРЫ

The Master Budget

Overview of the master budget

The Master Budget for a profit oriented organisation seeks to build a set of interrelated budgets which provide a complete “picture” of the operations of the business over some future period, usually twelve months.

The elements of the master budget addresses both operating and financial concerns. The major elements of the Master Budget are:

Master Budget Course - пособие по разработке модели бюджетов компании

The Operating Budget is made up of the SalesBudget, ProductionBudget and ExpensesBudget. As the name suggests the sales budget forecasts the sales for the company over the budgeted horizon. As we will explore shortly, the sales budget will for many business be a budget by product or product line and by territory, again depending on the nature of the business. The Production budget for a manufacturing firm will seek to budget the costs of producing the finished goods including the cost of raw materials and direct labour that go into the manufacturing process as well as overheads in the manufacturing process such as the cost of buildings, light, heat power and supervisory staff within the factory. The expenses budget are for the various sales and administrative costs of doing business.

The Financial Budget is made up of the Cash Flow and Capital Expenditure Budgets and the Budgeted Balance Sheet. The Capital Expenditure budget seeks to budget for purchases of capital items that is necessary for the operations of the business over the coming years. The budgeted Balance Sheets shows the funding impact of factors such as the level of Accounts Receivable flowing from the level of budgeted credit sales.

The Cash Flow budget is the “glue” which binds the whole budget together. Every budget decision from the level of sales to labour costs and spending on capital items has some impact on cash flows. The cash flow budget seeks to show the cash funding necessary for the budget period.

Let us now look at a plan of the Master Budget in more detail:

The production budget for manufacturing firms is made up of three elements:

  • Raw Materials budget which establishes the cost of the material content of the production process
  • Direct Labour budget which budgets for the cost of those staff directly involved in the production of manufactured products
  • Overheadbudget

The expenses budget is made up of:

  • Selling expenses which includes distribution costs, sales and marketing expenses
  • Administrative expenses which includes the cost of overall corporate management and financing costs.

Sales budget

The sales budget is the most important element in the master budget as all other budget assumptions flow from the sales forecasts in the budget. While it can readily be seen that the cost of raw materials and direct labour are directly related to the level of sales, it might not be so obvious how other budget elements relate. Here are some relationships:

  • The level of capital expenditure will depend on the level of sales. If a company is showing rapid sales and therefore production growth, there may not be sufficient manufacturing capacity and the company will need to buy more capacity. This will require capital expenditure.
  • The level of sales which are on credit will influence the Balance Sheet because it drives the level of Accounts Receivable
  • The level of sales will drive selling and marketing expenses because extra sales means extra distribution costs and may relate to bonuses paid to the sales force
  • The mix of sales can relate to the level of selling and marketing expenses. If a company is introducing a new product there may be a high level of sales expenses as new advertising campaigns are ramped up
  • The sales made by a company generates a positive cash flow and so the level of sales are important in determining the cash flow of the company and, in turn, the cost of debt finance for the business

The sales budget will depend on the successful forecasting of a number of factors including:

  • The state of the overall economy
  • The competitive position
  • The plans to introduce new products
  • The success of advertising and marketing campaigns

The sales budget can be expressed in a number of different ways. Some of the possible combination of factors in which the management of the company might be interested in reviewing the budget targets are:

Master Budget Course - пособие по разработке модели бюджетов компании

The factors in which management might be interested are:

  • Product: Here the management will wish to know sales revenue by product line. Budgets may differ from company to company. For some companies, it will be possible to budget down to the individual product line and show budgeted product sales by product code in terms of units of production and revenue per item. An example of a company which will wish to budget in this manner is a motor vehicle manufacturer which will budget not only for the general model but also variations within a model. There will be different revenues per unit for a top of the range model than for the entry level model within that range. It is also very likely that there will be different gross margins for the different models within a range not only in terms of the
  • Period: The periods into which the budget may be divided might be by quarter, by month or even in some companies, by week. As discussed above, the overall budget will typically be for a financial year.
  • Sales territory: As well as breakdowns by product, it will often be necessary to analyse budgeted sales by sales territory. This might be based upon a geographic territory within a country, a breakdown by countries or regions and in some cases by class of customer. An example, for a software company, might be sales through sales channels such as local agents, direct phone sales and licensing arrangements with major customers such as government departments or large companies.

The Sales Budget for a hypothetical company, the Software Factory for the first and fourth quarters for the year ended 31 December 19×1 is shown below.

The Software Factory is a manufacturer of software for the personal computer market. It has two products and two sales territories. It has an unusual spreadsheet the “4D Spreadsheet” which can build multi-dimensional spreadsheets with complex interlinking of spreadsheet elements. The company also has a multi-media database which allows users to store a variety of elements including moving images and photographs.

The sales budget, of which we can see just the first and fourth quarters, shows budgeted sales by product and territory. The unit price in each territory is an average of the three markets serviced by the territory, as discussed above: sales through sales channels such as local agents, direct phone sales and licensing arrangements with major customers such as government departments or large companies.

Master Budget Course - пособие по разработке модели бюджетов компании

Let us look at some of the factors which went on, in building the sales budget.

4D Spreadsheet: The Northern Sales region sells primarily into the domestic or “SoHo” (Small office and Home) market through agents. The Southern Sales region sells primarily in direct sales to large customers. The Northern Sales region is, then, particularly affected by the pre-Christmas period as the home buyers of computers have their busiest season whilst the Southern Sales region has reasonably regular sales throughout the year.

Whilst there is only one recommended retail price for all customers, the amount received by the company varies depending on the method of distribution. For example, the sales in the Northern Sales region to the SoHo market is through agents and the average dollars per package is less than in the more direct sales to large customers by the Southern Sales Region.

Further, whilst there has been no change forecast in the recommended retail price of the spreadsheet, the company is forecasting that the average sales price will decline over the year as the spreadsheet comes under increasing competition from other spreadsheets. So the company is, for both sales regions, forecasting a slow decline in the average price of spreadsheets sold. The decline will come about by increasing discount levels going to corporate customers and “specials” where, for example, for a limited period potential customers who have a competing spreadsheet can, to use an industry-term, “cross-grade” where they are for the purposes of the “special” treated as existing users of the 4D Spreadsheet and only have to pay a normal upgrade cost.

MegaMultiMedia Database:The MegaMultiMedia Database is a relatively new product for the Software Factory. It was launched first, in the Northern Sales Region and has been showing signs of continuing growth in that region: growth which the Marketing director thinks will continue right through the year. It has only just been launched in the Southern Sales region and the lower sales price forecasted in the month of January recognises that many opening specials will continue for January but then the price will be brought into line with the Northern Region as the opening specials are brought to an end. The Marketing Director is forecasting a similar but somewhat lower price reduction in the sales price of the database as is expected for the spreadsheet.

Continuing strong sales growth is forecast in the Southern Region which will nearly double sales in the region, with a 60% growth rate forecast in the Northern Region.

Budgeting for the Cost of Production

Introduction

The elements of the production budget for a manufacturing firm is made up of a budget for the cost of the raw materials that goes directly into the manufactured product, the cost of the labour used in the manufacturing process and the cost of the other inputs into the process such as heating, lighting, supervision and depreciation. This can be seen in the figure below:

Master Budget Course - пособие по разработке модели бюджетов компании

Production Budget

The first thing we must do is to work out how many items of production that will be made in the budgeted period. The production must satisfy those sales in the current period that cannot be supplied from current production and also provide a surplus to put into the finished goods inventory. Of course, many manufacturing processes are “on demand” and will not have any finished goods but the great majority of manufacturing organisations will maintain a level of finished goods inventory.

The level of production in a period will be determined by using the output from the sales budget and will incorporate some assumptions on the desired level of closing inventory:

The production budget for the Software Factory for the first three months of the budget year is shown below:

Master Budget Course - пособие по разработке модели бюджетов компании

The technology and cost of producing the 4D Spreadsheet MegaMultiMedia database is very similar, so the production budget for the Software Factory shows the production and cost combined for both products.

The finished goods inventory, in units, that is required at the end of each period should be sufficient to meet all of the next period’s sales and still have one quarter of the month’s sales left over. So the closing inventory at the end of January is 125% of the February sales rounded to the nearest 10 units. Closing inventory is, then, 770 * 125% = 962.5 units which when rounded to the nearest ten units becomes 960 units. The level of finished goods inventory not only has implications for the number of units to be produced in the manufacturing process but also relates to the Balance Sheet as it will impact on the dollar valuation of finished goods.

So if we know the quantity of opening inventory at the commencement of the period and we can calculate what the quantity of closing inventory should be and we also know what the level of sales for the period are likely to be, we can calculate the level of production in the period. Which, again for the month of January, is shown as:

Master Budget Course - пособие по разработке модели бюджетов компании

Raw Materials Budget

The first of the costs of conversion to take the elements of a manufactured product from its raw materials to the finished product is, not surprisingly, the cost of the raw materials themselves.

The raw materials which go into a product can be very complex. The number of items that go into a typical computer can range up to several hundred separate items once we count in all of the components on the main system board, the casing, keyboard and display screen.

The raw materials that go into producing the software manufactured by the Software Factory would include:

  • Paper for the printing of the manuals. Because it wishes to maintain quality control over the production of the manuals, the Software Factory does all printing in house and, as we will see, is planning to spend a considerable sum on the acquisition of highly advanced printing facilities
  • Reply paid cards – as these require special processing they are contracted in from an outside printer
  • Diskettes
  • Printed boxes to include manuals and diskettes
  • Shrink wrap

Similar factors as apply to the calculation of the quantity of units produced in the production budget will also apply for the raw materials. It will be necessary to be aware of at least the dollar value of raw materials on hand at the end of each period and for some businesses it may well be appropriate to know about the physical quantities of the raw materials that make up each item of manufactured product.

The calculation of raw materials usage and purchases and inventory balances in physical quantities would be as follows:

Master Budget Course - пособие по разработке модели бюджетов компании

To calculate the dollar value of these elements it will be necessary to multiply the units of each type of raw material by the estimated cost of the raw materials.

Of course, for some businesses it may not be appropriate to go right down to the level of physical quantities of raw materials and a calculation of raw materials usage and purchases and inventory balances would be calculated as follows:

The Raw Materials budget for the first quarter of the 19×1 year for the Software Factory is shown below:

Master Budget Course - пособие по разработке модели бюджетов компании

The estimated cost of all of the various items of raw materials in the production process is $12.50 and this is expected to be constant throughout the 19×1 financial year. Because the raw materials are rather generic in nature, being primarily paper and diskettes, and therefore readily obtainable only a relatively small amount of raw materials inventory is required at the end of each period. The budget assumption is that 50% of the requirements for the next month should be on hand at the end of each month. The closing inventory for the month of February is $5,250 or 50% of March’s raw materials consumption of $10,500.

Direct Labour Budget

It is normal to see the direct labour budget as responding directly to production levels. The direct labour budget for a particular item in the product range will usually be calculated as:

The direct labour budget for the first quarter Software Factory for the first quarter of the financial year is shown below:

Master Budget Course - пособие по разработке модели бюджетов компании

Factory Overhead

A variety of overhead costs associated with running the manufacturing process is included in the Factory Overhead budget. Some of the costs which are included in this category include:

  • Incidental materials costs including cleaning and some packaging costs
  • The “on costs” of direct Labor such as idle time, employers’ contribution to the employees’ superannuation fund and payroll taxation
  • Factory rent or lease costs, heating or cooling, power and maintenance
  • Quality assurance
  • Supervision and factory management
  • Depreciation of factory equipment

These costs are of both a fixed and variable nature. Some examples of fixed and variable costs are:

Variable

  • Incidental materials costs including cleaning and some packaging costs
  • The “on costs” of direct Labor such as idle time, employers’ contribution to the employees’ superannuation fund and payroll taxation

Fixed

  • Factory rent or lease costs, heating or cooling, power and maintenance
  • Supervision and factory management
  • Depreciation of factory equipment

For many companies, the budget will calculate an appropriate overhead application rate and apply the overhead to the budgeted production using the application base. Typical application methods are direct labour hours, units of production or machine hours.

Let us look at the factory administration cost structure and the factory administration budget of the Software Factory

Master Budget Course - пособие по разработке модели бюджетов компании

All of the factory overhead for the Software Factory is of a fixed nature. The only change that we can in the first quarter is for factory depreciation which moves from $13,200 in February to $13,600 in March. This has been caused by the purchase of additional manufacturing plant in the month as will be explained, below, in the context of the Capital Expenditure budget.

A summary

Master Budget Course - пособие по разработке модели бюджетов компании

Budgeting for the Cost of Inventory

Application of Costs to Inventory

We now need to consider the calculation of the overall cost of production and the application of overhead costs to particular products.

One of the key benefits of the budget process is to enable management to review the estimated costs of manufacturing products in the budget period. As we have discussed, this may be an iterative process as knowledge of the cost structure of a product may well impact on the sales price and in turn changes in selling prices may impact on sales volumes. Changes in sales volume will, in turn, impact on the cost structure and the cycle repeats itself. The total manufacturing cost for the first and fourth quarters of the Software Factory is:

You will notice that the total cost of the product moves substantially from a high of $44.36 per unit in January to a low of $33.50 in November. What is driving this variation where January is one third more expensive than November”

The answer is to be found in the fixed costs of factory overhead. You will notice that production in January totals 725 units whilst in November is 1,200 units as the company ramps up production for the summer break. The largely fixed costs of factory overhead are being spread over a much larger number of units. This seasonal pattern to production is typical for many types of businesses and is illustrated in a hypothetical example :

In this case the quarterly manufacturing overhead costs are constant at $800,000 per quarter but the production varies considerably from 450,000 units in Quarter 2 to 825,000 units in the fourth quarter. The cost per unit ranges from $1.78 per unit down to $0.94 per unit whereas the average is $1.33 per unit.

To overcome this problem, some companies will budget for under-applied overhead in those budget periods when production is below the average and budget for over-applied overhead in those budget periods when production is above the average.

The accountant for the Software Factory has taken a straightforward approach and allocated costs to the periods as they fall and then used a weighted average inventory valuation method to calculate the costs of inventory.

The calculation of inventory balances for the first and fourth quarters of the budget period is:

You will notice that the quantity and costs of the opening inventory is summed to the quantity and costs of the production for the month and a total calculated. A new average is calculated which is then applied to the cost both of sales in the period as well as the cost of the finished goods.

Budgeting for the Expenses

Expenses budget

The cost of the two expenses categories of selling and marketing and administration costs flow from the

Master Budget Course - пособие по разработке модели бюджетов компании

Selling Expenses budget

The selling expenses budget will normally be made up of a variety of costs that are fixed and variable and what might be called “discretionary”.

Some of these costs are:

  • Variable
    • Sales commissions
    • Distribution costs
    • Some travel costs
    • Warranty costs
    • Sales discounts
  • Fixed
    • Salaries
    • Some travel costs
    • Rates, light, heat and power etc
  • “Discretionary”
    • Marketing costs
    • Advertising campaign costs

So we can see that there is a direct link between the level of sales in dollar terms and the budgeted sales commissions to the sales force and the physical level of sales and distribution costs. Similarly, the more a company sells the greater will be the cost of meeting warranty claims on items sold.

These types of relationships are demonstrated below:

The selling expenses budget for the first and fourth quarters for the Software Factory is:

Master Budget Course - пособие по разработке модели бюджетов компании

You will notice that the sales commission to the sales staff of the Software Factory is three percent of the sales revenue of the company. The distribution costs are $3 per unit of sales. Note that the distribution costs of finished goods are for the company more closely related to the physical units rather than the dollar value of the sales.

You will remember that elements of the company’s product line relates to the SoHo market and is, therefore, dependent on sales at the Christmas break. The marketing program is moulded to fit this cycle. Marketing programs are typically $20,000 per month but the company has planned a major campaign for the end of the year and marketing programmes go up to $75,000 in October and November and peak in December at $85,000. This an example of what might be termed “discretionary” expenses. The depreciation expense and marketing overhead are examples of fixed overhead.

Administrative Expenses budget

The administrative expenses budget encompasses a range of expenses that relate to the general management of the company and to the financing of the company.

Some of the expenses that would be encompassed in the administrative expenses budget include:

  • Wages and salaries of general management
  • Bad and doubtful debts
  • Insurance
  • Interest

While many of the expenses in the administrative expenses are of a fixed nature, some are influenced by the level of sales activity. Some of these relationships are:.

The administration budget for the Software Factory is:

Master Budget Course - пособие по разработке модели бюджетов компании

The interest on debentures is forecasted at 12% of the outstanding level of debentures. This figure is drawn from the budgeted Balance Sheet.

Putting it Together

Capital expenditure budget

A capital item is usually defined as one which will last longer than twelve months and exceeds a certain amount

The capital expenditure budget will be highly dependent on the level of sales forecast in the sales budget:

If we come back to our example of the Software Factory Pty Ltd, the first and fourth quarters of the Capital Expenditure budget is reproduced below:

We can see that, as it happens, the only capital expenditure that is forecast is in the first and fourth quarters and three items of plant are required: A fully automated four colour printing press for the in-house production of manuals, a 150 per hour and floppy duplicator and finally a package assembly line which will take a stock of manuals, and floppies assemble and shrink wrap them in the display boxes. Total spending of $200,000 will be required which will reduce the need for contract labour costs in the production process.

Just as the capital expenditure budget is influenced by activity from, for example, the sales budget, the capital expenditure budget influences other budget elements. Most importantly, in order to fund capital expenditure cash must be found from operating inflows, by borrowing or be raised from the owners. The level of capital expenditure will be one of the most important items in the cash flow budget.

Some of the other relationships which the capital expenditure budget has with other budgets are:

Master Budget Course - пособие по разработке модели бюджетов компании

Let us return for a moment to the factory administration budget of the Software Factory where we see that the budgeted depreciation expense has gone from $13,200 in the month of January to $15,800 in the month of December

Master Budget Course - пособие по разработке модели бюджетов компании

This reflects the budget assumption that additional depreciation is flowing from the acquisition of the various items of capital expenditure.

Budgeted Income Statement

The Sales Budget and the various expense budgets are brought together in a budgeted Income Statement. The format of the Income Statement will vary from company to company depending on how much information is relegated to schedules. In the case of the Software Factory most of the information is contained in the schedules so the Income Statement is of a summary nature only:

Master Budget Course - пособие по разработке модели бюджетов компании

In this budget, the taxation expense is calculated as is the subtotals within the income statement and the closing balance of retained earnings.

Due to the nature of the business, the gross profit for the Software Factory is very high as most of the cost structure in the business is in the selling and administrative expenses. This is due to the very high level of marketing expenditure required in this business sector and the customer support and research and development which is contained in the administrative expenses budget.

Budgeted Balance Sheet

The budgeted balance sheet is impacted by a number of assumptions from all the other budgets. A direct relationship comes from the sales budget which will determine the level of Accounts Receivable arising from Credit Sales as shown below:

Similarly the level of the Raw Materials shown as part of Current Assets on the Balance Sheet has come directly from the Raw Materials budget in the Production Budget.

This in turn has an impact on the Cash Flow Budget as the level of Raw Materials must be funded.

The Balance Sheet has as might be expected a number of relationships with other budgets. The Balance Sheet and some of the relationships the budgeted balances have with other budgets for the Software Factory:

Master Budget Course - пособие по разработке модели бюджетов компании

Some notes on the relationships are:

  • Cash at bank is a “balancing item” and shows the residual of the impact of all of the transactions for the period. It is drawn from the cash flow statement, as we will explore in more detail in a moment.
  • Accounts Receivable is based upon an assumption of the numbers of days outstanding of credit sales which is drawn from the Sales Budget. In this case the budget assumes that none of the sales made in any given month will have been collected in cash at the end of the month but all the cash will be received in the next month.
  • Raw Materials and Finished Goods are drawn completely from the Production budget as we saw above.
  • Provision for Taxation is drawn from both the Income Statement for the expenses and from the cash flow statement where the payment to the taxation office is recorded.
  • Factory Plant, Sales Equipment and Administrative Equipment are driven by the Capital Expenditure budget.
  • Accumulated Depreciation onFactory Plant is driven by the expenses shown in the budget for factory overhead and, similarly;
  • Accumulated Depreciation onSales Equipment and Administrative Equipment is driven by the budget for selling expenses and administrative expenses, respectively.
  • Term Loan balances are influenced by additional loans or repayments shown in the Cash Flow statement.
  • Retained Earnings are drawn from the profit and loss statement.

Budgeted cash flow

The budgeted cash flow can be seen as the glue which holds the budget process together. It is highly significant to the process of the budget as the level of financing may well be at the heart of the very survival of the company. As we will discuss shortly, the impact of changes in budget assumptions can radically impact on the cash generation ability of the firm and on the financing of the business.

One of the more important relationships is the impact of capital expenditure on the cash flow. Capital expenditure, by its very nature, can be very “lumpy” as companies enter into major investments which relate to multi-year projects but which must, of course, be financed in the year of expenditure. The capital expenditure budget impacts on the Balance Sheet as new capital expenditures are capitalised beyond the current financial year as well as the cash flow statement which recognises the financing requirements of the expenditure.

This is just one example of the many interrelationships and other relationships are discussed in the context of the Software Factory. The budgeted cash flow for the final quarter and for the complete financial year for the Software Factory is:

Let us look at some of the more important elements of the company’s Cash Flow budget. Let’s look first at Accounts Receivable and Accounts Payable. In the Software Factory Pty Ltd case all purchases and other expenses are assumed to be paid in cash and so there are no Accounts Payable. Conversely however, all sales are assumed to be on credit and we should, therefore, pay particular attention to the calculation of the cash received from customers, which is calculated as:

The closing balance of cash is the figure which is the “balancing item” and which is the final figure which binds all the various budgets together.

The Master Budget – A Simplified Example – The Personal Software Factory Pty Ltd

Introduction

In this section we can show the overall budget for the Software Factory for the year ended 31 December 19×1. It commences with a summary of the budget position for each of the four quarters and then shows the first and fourth quarters of the detailed budgets.

Budget overview and financials for the 19×1 year

Master Budget Course - пособие по разработке модели бюджетов компании

Sales budget

Production budget

Master Budget Course - пособие по разработке модели бюджетов компании

Expenses budget

Master Budget Course - пособие по разработке модели бюджетов компании

Budgeted cash flow

Master Budget Course - пособие по разработке модели бюджетов компании

Budgeted Income Statement

Master Budget Course - пособие по разработке модели бюджетов компании

Budgeted Balance Sheet

Master Budget Course - пособие по разработке модели бюджетов компании

Flexing the budget

Introduction

As we discussed in the previous topic, a budget may go through several iterations. It is important for the master budget to be built in such a way as to allow the analysis of the budget under all its various different assumptions. Some issues that may be important for a company are:

  • What will be the impact on profits and on financing of changes in the sales volume of products”
  • What will be the impact on profits and on financing of changes in the sales mix of products”
  • What will be the impact on profits and on financing of changes in the raw materials cost of products”
  • What will be the impact on profits and on financing of changes in the value of the local currency in foreign exchange markets
  • What will be the impact on profits and on financing of changes in assumptions on the cost of labour
  • What will be the impact on profits and on financing of changes in assumptions on the level of capital expenditure

The budget should flex to allow us to view the consequences of the

What if at the Software Factory

It would be very desirable for general, marketing, production and financial management to be able to review a number of budget assumptions. As we discussed above, there are many assumptions that could be built into a budget model but we will focus on just two:

  • The level of sales activity
  • The level of the cost of raw materials

Of these two, the level of sales activity is clearly the most important driver of the overall results of the company. Nonetheless changes in the cost of raw materials can make quite a difference to the standing of the company. So we have presented three scenarios to management

  • The “base case” which is the generally agreed position for the company
  • The “absolute best case” which assumes a ten per cent increase in sales $ revenue over the base case and a five per cent reduction in the amount the company has to pay for raw materials
  • The “absolute worst case” which assumes a ten per cent reduction in sales $ revenue over the base case and a five per cent increasein the amount the company has to pay for raw materials

What difference does that make to the level of profitability and cash generation ability of the company”

Base Case

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.

Master Budget Course - пособие по разработке модели бюджетов компании

Very Best Case

The “Very Best Case”showsa 10% improvement in the level of sales and 5% improvement in the cost of raw materials from the master budget.

Master Budget Course - пособие по разработке модели бюджетов компании

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.

Worst Case

The “Very Best Case”showsa 10% reductionin the level of sales and 5% increasein 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.

Analysis of the What If Scenarios

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

  Worst Case Base Case Best Case
Change in Sales Volume -10% +10%
Change in Raw Materials Prices +5% -5%
Sales Revenue $2.2m $2.7m $3.1m
Net Profit after Tax $0.2m $0.5m $0.8m
Cash on Hand $56,412 $338,676 $647,770

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%!.

Master Budget Course - пособие по разработке модели бюджетов компании

Using a Spreadsheet to Build the Budget

Introduction

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:

Master Budget Course - пособие по разработке модели бюджетов компании

Logical Formulae

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.

Источник: ANet

Похожие Статьи

Следующий

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Новые статьи