Develop A Comprehensive Five-Year Master Budget Using Scenar

Develop a comprehensive five-year master budget using scenarios of sales growth or profit margin improvement, accounting for all relevant costs and balance sheet forecasts.

Prepare an Excel spreadsheet with forecasts for each line item of the income statement and balance sheet based on two scenarios: one with 50% sales increase over the previous year, and the other with a 3 percentage point improvement in operating profit margin. Incorporate assumptions regarding costs, expenses, receivables, payables, inventories, investments, and other relevant financial and operational metrics. Your forecast should extend over five years, starting from the latest completed fiscal year (March 31, 2020), including at least two prior years of historical data for reference, such as detailed profit and loss statements and balance sheets. Select a company in a B2B or B2C industry (e.g., automotive parts supplier, coatings, consumer electronics, furniture, or textiles).

Model all aspects of the budget with attention to the management accounting principles covered in the course, including cost centers, variable and fixed costs, cost-volume-profit analysis, inventory turnover, cash flow management, and sustainability considerations. The assumptions are as follows:

Scenario 1: Sales Growth of 50% over last year

• Sales increase 50% each year compared to the previous year's sales.
• Contribution margin decreases by 1% annually from the last year's value and remains stable afterward.
• Travel expenses, indirect labor, and utilities increase proportionally with sales.
• Depreciation is based on a 10-year useful life.
• All other costs increase by 10% annually.
• Accounts receivable, accounts payable, and inventories in days remain constant as last year.
• Interest expense is 4% of total costs.
• Investments equal 5% of net sales annually.

Scenario 2: Operating Profit Margin improves by 3 percentage points

• Sales decrease by 3% annually compared to the previous year's sales.
• Contribution margin increases by 3% annually from the last year's value and remains stable.
• Travel expenses, indirect labor, and utilities increase proportionally with sales.
• Depreciation remains based on a 10-year period.
• All other costs decrease by 1% annually.
• Accounts receivable, accounts payable, and inventories in days remain constant.
• Interest expense remains at 4% of total costs.
• Investments equal 1% of net sales each year.

Develop the forecast for all relevant balance sheet accounts, including assets, liabilities, and equity, based on the selected scenario. Present your data with clarity, ensuring all formulas are correctly implemented. The budget should also incorporate a cash flow projection to ensure liquidity management across the forecast period.

In your final Excel file, include the detailed income statement, balance sheet, cash flow statement, and supporting assumptions. Your work will be assessed on the accuracy of formulas, the logical consistency of assumptions, completeness of the forecast, and professionalism of the presentation. This project accounts for 40% of your final grade and demonstrates your ability to apply management accounting concepts to real-world budgeting challenges.

Summary of Deliverables:

• Complete Excel master budget forecast for five years based on your chosen scenario.
• Detailed assumptions, and logical linking of income statement, balance sheet, and cash flow forecast.
• Documentation of key formulas and rationale behind assumptions.
• In-class presentation of the master budget, supported by a slide deck summarizing key data insights and strategic implications.

References

• Drury, C. (2018). Management and Cost Accounting. Cengage Learning.
• Horngren, C. T., Sundem, G. L., Stratton, W. O., & Burgstahler, D. (2018). Introduction to Management Accounting. Pearson.
• Shim, J. K., & Siegel, J. G. (2019). Financial Management in the Hospitality Industry. Wiley.
• Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2018). Managerial Accounting. McGraw-Hill Education.
• Higgins, R. C. (2018). Analysis for Financial Management. McGraw-Hill Education.
• Hirst, M., & Brown, P. (2019). Accounting and Finance: An Introduction. Routledge.
• Kaplan, R. S., & Atkinson, A. A. (2015). Advanced Management Accounting. Pearson.
• Olechowski, A., & Hyatt, D. (2018). Strategic Cost Management. Routledge.
• Foster, G., & Delozier, L. (2019). Cost Management: A Strategic Emphasis. McGraw-Hill Education.
• Wild, J. J., Subramanyam, K. R., & Halsey, R. F. (2019). Financial Statement Analysis. McGraw-Hill Education.