Budgeting, Forecasting and Scenario Planning

Introduction and Context

Budgeting converts an organisation’s objectives into quantified plans for a specified period. Forecasting estimates what is likely to occur, using current information and assumptions. Scenario planning examines how results change when important assumptions differ. Together, these activities support resource allocation, liquidity management, performance evaluation and strategic decision-making.

In accounting and finance, Excel is particularly useful because budgets combine structured data, calculations, assumptions and reporting. A well-designed workbook can link sales, production, operating expenses, capital expenditure, financing and cash flows. However, spreadsheet quality depends on accounting logic, transparent assumptions, appropriate controls and careful interpretation. Excel does not replace professional judgement; it makes that judgement more visible and testable.

Learning Objectives

By the end of this chapter, students should be able to:

1. Explain the purpose and relationships among operating, cash and capital budgets.
2. Construct an integrated budget model in Excel.
3. Calculate and interpret budget variances.
4. Prepare rolling forecasts and distinguish them from fixed budgets.
5. Perform sensitivity analysis, Goal Seek and scenario analysis.
6. Apply Solver to constrained planning decisions.
7. Evaluate model limitations, governance requirements and ethical risks.

1. The Budgeting System

A budget is a formal quantitative statement of planned revenues, costs, resource use and financing. A budgeting system normally contains several linked schedules rather than one isolated report.

1.1 Operating, cash and capital budgets

The operating budget focuses on revenues and operating costs. Typical components include:

• sales budget;
• production or purchases budget;
• direct materials, direct labour and manufacturing overhead budgets;
• selling and administrative expense budget; and
• budgeted income statement.

The cash budget focuses on timing. A profitable organisation can still experience a cash shortage if customers pay slowly, inventory is purchased before sales occur, or debt repayments are concentrated in one period.

The capital budget concerns long-term investments such as equipment, facilities, software and vehicles. Capital expenditure affects cash immediately or over time, while depreciation affects accounting profit but normally does not represent a current-period cash payment.

These budgets are interdependent. Expected sales influence production and purchases; production affects labour and materials; all cash receipts and payments influence financing requirements; capital expenditure affects depreciation, cash and sometimes capacity.

1.2 Budget assumptions

A budget is conditional on assumptions. Important assumptions include selling price, sales volume, collection timing, payment timing, inventory policy, wage rates, tax rates, interest rates, capacity and planned investment. Assumptions should be recorded in a clearly labelled input area rather than hidden inside formulas.

A useful distinction is:

• controllable assumption: a management choice, such as advertising expenditure;
• uncontrollable assumption: an external condition, such as an exchange rate;
• operational assumption: a relationship describing activity, such as materials required per unit;
• timing assumption: when cash is received or paid.

1.3 Incremental and zero-based approaches

An incremental budget begins with a prior budget or actual result and adjusts it. It is efficient but may preserve inefficiencies. A zero-based approach requires activities and expenditures to be justified from the beginning of the budget process. In practice, organisations often use a hybrid approach: routine items are updated incrementally, while significant or discretionary costs receive deeper review.

2. Constructing an Integrated Budget in Excel

A professional workbook should separate inputs, calculations and outputs. A simple architecture is:

Use consistent time periods, preferably months for cash planning. Use cell references rather than repeatedly typing values into formulas. Label units, currencies and signs. For example, an expense may be displayed as a positive cost in an operating schedule but as a negative cash flow in a cash schedule; the convention must be consistent and documented.

2.1 Sales budget

The basic sales budget is:

Units must be measured consistently, and the price must use the same currency and time period. If price varies by product or month, calculate each product-period combination and then sum the results.

2.2 Production budget

For a manufacturing organisation, planned production is calculated as:

This equation assumes that inventory is measured in units and that there is no loss, spoilage or rework. If such factors are material, the model should include them explicitly.

2.3 Direct materials purchases

If each unit requires a known quantity of material:

Purchases are then:

The materials cost budget is:

2.4 Labour and variable overhead

If labour hours per unit and a wage rate are known:

Variable overhead can be modelled using an activity driver such as labour hours or machine hours:

Fixed overhead should be modelled separately when it does not change directly with short-term activity.

2.5 Budgeted income statement

The budgeted income statement normally follows:

This contribution format is useful for planning. A full financial statement may also include depreciation, interest, tax and non-operating items. Care is required: depreciation reduces accounting profit but is not normally included as a cash payment in the cash budget.

3. The Cash Budget

The cash budget forecasts liquidity by period. Its fundamental relationship is:

All amounts must refer to the same period. A minimum desired cash balance may be specified. If projected closing cash is below that minimum, the financing requirement is:

This formula assumes that borrowing is available and ignores interest on new borrowing unless interest is modelled separately. If borrowing is drawn during the period, interest timing must be stated—for example, interest may be calculated on opening debt, average debt or end-of-period debt.

Worked Example 1: Monthly cash budget

A retailer begins April with cash of $18,000. April cash sales are $30,000. Credit sales of $50,000 are collected 60% in April and 40% in May. April purchases paid in cash are $24,000. A prior-month payable of $12,000 is paid in April, wages are $15,000 and other cash expenses are $5,000. The desired minimum cash balance is $20,000.

April receipts are:

• cash sales: $30,000;
• collection of April credit sales: $50,000 × 60% = $30,000;
• total receipts: $60,000.

April payments are:

• purchases: $24,000;
• prior payable: $12,000;
• wages: $15,000;
• other cash expenses: $5,000;
• total payments: $56,000.

Before financing:

Because $22,000 exceeds the $20,000 minimum, no borrowing is required. The May collection of $20,000 is not included in April because a cash budget records timing, not merely revenue recognition.

4. Capital Budgeting

Capital budgeting evaluates long-term investments. Relevant cash flows normally include the initial investment, incremental operating cash flows, working-capital changes, tax effects and terminal proceeds. Sunk costs are excluded because they have already occurred and cannot be changed by the decision. Allocated fixed costs are relevant only if they change because of the project.

The net present value (NPV) of a project is:

where I₀ is the initial investment at time 0, CF_t is the incremental cash flow in period t, r is the required rate of return per period, and n is the number of periods. The formula assumes that cash flows occur at period ends and that the discount rate is appropriate for the project’s risk and timing. A project is financially attractive on an NPV basis when NPV is positive, subject to strategic, legal and resource constraints.

In Excel, periodic project cash flows can be evaluated using NPV for future cash flows and then subtracting the time-zero investment. Users must not accidentally include the initial investment inside a function that assumes all listed cash flows occur at the end of future periods.

Depreciation is not itself a cash flow, but it may reduce taxable income. Therefore, tax treatment must be represented explicitly rather than treating depreciation as a cash payment.

5. Variance Analysis

Variance analysis compares actual outcomes with budgeted expectations and investigates causes. For a revenue item:

A positive revenue variance is generally favourable, although the meaning depends on the organisation’s sign convention and whether the result was achieved through undesirable discounts or excessive risk.

For a cost:

Under this convention, a positive amount is favourable because actual cost is lower than budget. Organisations may instead calculate actual minus budget and label favourable or unfavourable separately. The convention must be stated.

Flexible budgets

A fixed budget may be inappropriate when actual activity differs significantly from planned activity. A flexible budget recalculates variable costs at the actual activity level while retaining appropriate fixed-cost assumptions.

For a variable cost:

The total cost variance can be separated into:

Using an unfavourable-cost convention, the difference can be analysed as:

Thus:

These relationships apply when the flexible budget correctly captures cost behaviour. Mixed costs should be separated into fixed and variable components using a defensible method.

Worked Example 2: Flexible-budget analysis

Budgeted variable cost is $8 per unit and budgeted fixed cost is $20,000. The static budget planned 5,000 units, but actual output was 5,500 units. Actual total cost was $66,000.

Static-budget cost:

Flexible-budget cost:

Total cost variance is $66,000 − $60,000 = $6,000 unfavourable. Activity variance is $64,000 − $60,000 = $4,000 unfavourable. Spending variance is $66,000 − $64,000 = $2,000 unfavourable. The total is reconciled: $4,000 + $2,000 = $6,000.

The analysis indicates that $4,000 arose from producing more units and $2,000 from spending more than the flexible budget allowed. It does not, by itself, establish responsibility or causation; operational investigation is still required.

6. Forecasting and Rolling Forecasts

A forecast is an updated estimate of expected results. A budget generally expresses an approved plan and may serve as a performance benchmark. A forecast asks, “What is now expected?” These purposes should not be confused.

A fixed annual budget may become less informative as conditions change. A rolling forecast continually extends the planning horizon. For example, each month the organisation removes the completed month and adds a new month at the end, maintaining a 12-month forward view.

A simple forecast may use a driver-based relationship:

A time-series method may use a moving average:

A three-month moving average gives equal weight to the three selected months. It is simple but may lag when trends change and may ignore seasonality. Forecast accuracy should be assessed using appropriate measures. Mean absolute error is:

where n is the number of forecast periods. MAE is expressed in the same units as the forecast and treats errors proportionally to their absolute size. No forecast measure eliminates uncertainty; accuracy statistics should be interpreted alongside business knowledge.

7. Sensitivity and Scenario Analysis

7.1 Sensitivity analysis

Sensitivity analysis changes one assumption at a time, or examines a controlled range for one driver, while holding other assumptions constant. It answers questions such as: “How does operating profit change if sales volume falls by 10%?”

A useful measure is the sensitivity of an output to an input:

This ratio is dimensionless. It is an approximation when changes are not infinitesimally small and when the relationship is nonlinear. In Excel, Data Tables can display results across alternative values, but the underlying model should remain auditable.

7.2 Scenario analysis

Scenario analysis changes several linked assumptions together. Typical scenarios include base case, downside case and upside case. A downside scenario might combine lower volume, slower collections, higher material prices and delayed capital expenditure. Such combinations are more realistic than changing one variable in isolation, but scenario definitions involve judgement and should be documented.

A scenario table should identify:

• assumptions changed;
• rationale for the changes;
• outputs affected;
• probability, if probabilities are defensible; and
• management actions under each scenario.

If probabilities are assigned, an expected value may be calculated:

Probabilities should not be invented merely to produce a precise-looking result. Scenario analysis is often most useful for resilience planning rather than for claiming that one forecast is certain.

7.3 Goal Seek

Goal Seek works backward from a desired output by changing one input. For example, it can determine the sales volume required to achieve a target profit. It is suitable when there is one changing variable and one target equation.

If fixed costs are $40,000, selling price is $50 per unit and variable cost is $30 per unit, target operating profit of $20,000 requires:

This calculation assumes constant unit price and variable cost, sufficient capacity, and no step-fixed costs or nonlinear effects. In Excel, Goal Seek should be checked after completion because it changes the selected input and may overwrite an assumption.

7.4 Solver-based planning

Solver is appropriate when an organisation must optimise an objective subject to constraints. A typical model contains:

• objective cell, such as total contribution or NPV;
• decision-variable cells, such as product quantities;
• constraints, such as labour hours, machine hours, demand limits and non-negativity; and
• an appropriate solving method.

For a product-mix problem:

subject to:

and:

where i identifies a product. A linear programming model requires linear objective and constraint relationships. Integer restrictions are needed when decisions must be whole numbers, such as machines or personnel. Solver’s result is conditional on the model and constraints; it is not a substitute for validating demand, quality, contractual and ethical requirements.

8. Illustrative Integrated Case: Northstar Furniture

The following is a fictional illustrative case.

Background

Northstar Furniture produces desks and shelves. Management is preparing a three-month plan. Each desk sells for $400 and requires $220 of variable production cost. Expected sales are 300 desks in April, 360 in May and 330 in June. The company wants ending inventory equal to 20% of the following month’s sales. Beginning April inventory is 60 desks. April fixed operating costs are $30,000 per month. Customers pay 70% in the month of sale and 30% in the following month. For simplicity, assume all variable production costs are paid in the month incurred.

Analysis

April desired ending inventory is 20% of May sales: 72 desks. Therefore:

May desired ending inventory is 20% of June sales: 66 desks. May production is:

June production cannot be fully determined without July sales. Management must either obtain a July forecast or establish a policy for the June ending inventory. This illustrates why a budget is a linked system: a missing downstream assumption affects the current schedule.

April revenue is:

April cash receipts are 70% of April revenue plus the prior-month collection. If March credit sales were $100,000 and 30% is collected in April, receipts are:

April variable production cost is:

April operating profit before any non-operating items is:

Notice that profit uses the variable cost of units sold, whereas cash payments in this simplified case use the cost of units produced. The difference is caused by inventory movement. Confusing production with sales would distort both profit and cash analysis.

Excel approach

Northstar should place sales, prices, collection percentages, inventory policy and costs on an assumptions sheet. The operating schedule should calculate sales, desired inventories, production and costs by month. The cash sheet should link revenue and production schedules while applying collection and payment timing. A scenario sheet should compare:

• base case: planned sales and costs;
• downside: sales 15% lower and material cost 8% higher; and
• upside: sales 10% higher with unchanged unit cost.

Goal Seek could identify the sales volume required to achieve a specified monthly profit. Solver could allocate limited machine hours between desks and shelves to maximise contribution, provided demand and capacity constraints are represented.

Outcome and learning points

The integrated model reveals that Northstar’s accounting profit, production requirement and cash receipts are different measures. It also identifies the need for a July demand assumption and demonstrates how downside conditions may affect both margin and liquidity. Management should respond not only to the base forecast but also prepare actions such as postponing discretionary capital expenditure, negotiating supplier terms or revising production schedules.

9. Spreadsheet Controls, Ethics and Professional Practice

Budget workbooks create risks because formulas can be overwritten, assumptions can be hidden and copied schedules can become inconsistent. Good practice includes:

• separating inputs, calculations and outputs;
• using cell protection and input validation;
• documenting sources and assumptions;
• checking totals and cross-footing schedules;
• reconciling opening and closing balances;
• using version control and change logs;
• testing extreme and zero-value cases;
• avoiding unnecessary hard-coded values in formulas; and
• independently reviewing important models.

Ethically, managers may deliberately create budget slack by understating expected revenue or overstating costs. Others may manipulate forecasts to secure funding or avoid accountability. A responsible accountant should distinguish uncertainty from intentional bias, preserve an audit trail and communicate limitations clearly. Forecasts should not be presented as guarantees.

Confidential payroll, customer and financial data must be protected. Access should be limited according to role, and personally identifiable information should not be unnecessarily copied into scenario models. Where AI-assisted spreadsheet tools are used, outputs must be checked for formula errors, unsupported assumptions, confidentiality breaches and fabricated explanations. Human accountability remains essential.

10. Limitations and Contemporary Developments

Budgets can become rigid, time-consuming and politically contested. They may encourage short-term behaviour, such as cutting essential maintenance to meet an annual target. Forecasts can fail because of structural breaks, unusual events, poor data or unstable relationships. Sensitivity analysis may understate risk when variables move together, while scenarios may reflect management imagination rather than statistical evidence.

Modern planning increasingly uses driver-based models, cloud collaboration, automated data connections, dashboards and rolling forecasts. Machine-learning forecasts may identify complex patterns, but they require reliable historical data and careful validation. An accurate statistical forecast can still be unsuitable if it ignores capacity, strategy, regulation or ethical constraints. The best practice is therefore a combination of sound accounting models, transparent assumptions, statistical evidence and informed professional judgement.

Chapter Summary

Budgeting translates organisational plans into quantified operating, cash and capital schedules. An integrated model links sales, inventory, production, expenses, investment, financing and cash timing. Operating profit and cash flow are different because revenue recognition, inventory and payment timing differ. Capital budgeting evaluates incremental project cash flows, commonly using NPV. Variance analysis compares actual results with budgets and becomes more meaningful when flexible budgets separate activity effects from spending effects. Rolling forecasts update expectations as information changes. Sensitivity analysis changes selected assumptions, scenario analysis changes combinations of assumptions, Goal Seek solves a one-variable target problem, and Solver optimises an objective subject to constraints. Reliable Excel planning requires transparent assumptions, documented logic, testing, access controls and ethical communication of uncertainty.

Key Terms

Budget: A quantified plan for a specified future period.

Forecast: An updated estimate of likely future results.

Operating budget: A plan for revenues and operating costs.

Cash budget: A schedule of expected cash receipts, payments and financing.

Capital budget: A plan and evaluation of long-term investments.

Budget assumption: A stated condition or driver used in preparing a budget.

Rolling forecast: A forecast that continuously extends the planning horizon.

Static budget: A budget prepared for one planned activity level.

Flexible budget: A budget adjusted to the actual activity level.

Variance: The difference between an actual result and a budget or standard.

Favourable variance: A result generally better than the benchmark under the stated sign convention.

Sensitivity analysis: Examination of output changes resulting from changes in selected inputs.

Scenario analysis: Evaluation of outcomes under coherent alternative sets of assumptions.

Goal Seek: An Excel tool that changes one input to achieve a specified output.

Solver: An optimisation tool that changes decision variables subject to constraints.

Contribution: Revenue less variable cost.

Net present value: Present value of incremental future cash flows less initial investment.

Driver-based planning: Forecasting based on operational factors that cause financial results.

Budget slack: Deliberate bias that makes a budget easier to achieve.

Model governance: Policies and controls that support spreadsheet reliability and accountability.

Review Questions

Short Answer Questions

1. Distinguish a budget from a forecast.
2. Why can a profitable organisation experience a cash shortage?
3. State the production budget equation and explain each term.
4. What is the purpose of a flexible budget?
5. When is Goal Seek more appropriate than Solver?

Long Answer Questions

1. Explain how sales, production, materials, operating expense and cash budgets are linked in an integrated Excel model.
2. Discuss the differences between fixed budgets, rolling forecasts and scenario plans.
3. Explain how total cost variance can be decomposed into activity and spending variances.
4. Describe the assumptions and cash-flow considerations underlying NPV in capital budgeting.
5. Evaluate the spreadsheet governance and ethical controls needed for a management budgeting workbook.

Analytical / Application Questions

1. A business budgets 8,000 units at a variable cost of $12 per unit and fixed costs of $45,000. Actual output is 7,500 units and actual total cost is $140,000. Calculate the static-budget cost, flexible-budget cost, activity variance and spending variance, and interpret the results.
2. Design an Excel scenario model for a company facing a 10% sales-volume decline, a 5% price reduction and a 7% increase in variable cost. Identify the outputs that should be monitored and explain two management responses.
3. A manufacturer has two products competing for limited machine hours. Explain how you would formulate a Solver model to maximise contribution, including the objective cell, decision variables, resource constraint, demand constraints and non-negativity requirements.

Multiple Choice Questions with Answers

Question 1

Which budget primarily focuses on the timing of cash receipts and payments?

A. Operating budget

B. Cash budget

C. Capital budget

D. Production budget

Correct Answer: B. Cash budget

Question 2

A company plans sales of 4,000 units, desired ending inventory of 600 units and beginning inventory of 500 units. What production is required?

A. 3,900 units

B. 4,000 units

C. 4,100 units

D. 5,100 units

Correct Answer: C. 4,100 units

Question 3

Budgeted variable cost is $10 per unit and fixed cost is $25,000. At actual activity of 3,000 units, what is the flexible-budget cost?

A. $30,000

B. $33,000

C. $55,000

D. $25,000

Correct Answer: C. $55,000

Question 4

Which tool changes one input to reach a specified output?

A. Goal Seek

B. Solver

C. Data Validation

D. PivotTable

Correct Answer: A. Goal Seek

Question 5

A rolling forecast differs from a fixed annual budget because it:

A. Excludes cash flows

B. Is continuously updated and extends the planning horizon

C. Contains only capital expenditure

D. Uses no assumptions

Correct Answer: B. Is continuously updated and extends the planning horizon

Question 6

If selling price is $60, variable cost is $35 and fixed costs are $50,000, how many units are required for a target profit of $25,000?

A. 1,333 units

B. 2,000 units

C. 3,000 units

D. 4,000 units

Correct Answer: C. 3,000 units

Question 7

Which item is normally excluded as a cash payment from a cash budget?

A. Wages paid

B. Supplier payments

C. Depreciation expense

D. Equipment purchase

Correct Answer: C. Depreciation expense

Question 8

Sensitivity analysis usually examines:

A. Several unrelated accounting systems

B. The effect of changing selected inputs on an output

C. Only historical transactions

D. Whether a journal entry balances

Correct Answer: B. The effect of changing selected inputs on an output

Question 9

In a Solver product-mix model, machine hours are most appropriately represented as:

A. A decision variable only

B. A resource constraint

C. A sunk cost

D. A depreciation adjustment

Correct Answer: B. A resource constraint

Question 10

Under the stated cost convention, actual cost of $66,000 and flexible-budget cost of $64,000 produce a spending variance of:

A. $2,000 favourable

B. $2,000 unfavourable

C. $4,000 favourable

D. $130,000 unfavourable

Correct Answer: B. $2,000 unfavourable