Using MS Excel to Prepare Pro Forma Financial Statements

Using spreadsheets to solve complex problems is a key competence – in fact a requirement – for many middle management positions in the workplace. Corporate finance provides many opportunities to use MS Excel to solve problems that cannot easily be solved manually.

In this assignment, you will use MS Excel to prepare pro forma financial statements and also calculate free cash flow for a multi-year business opportunity. While you will not need to use any special functions for this assignment, you will need to consider how you can structure your spreadsheet to be flexible enough to handle sensitivity analysis.

1. Use the scenario below to answer the questions.
2. Use Excel for all calculations.
3. Submit your report in Word format. Attach any supporting Excel documents to the D2L assignment tool.
4. See the rubric below for the marking criteria.

Mary Manic, president of Manic Manufacturing Inc., has asked you to analyze the feasibility of a business opportunity. The company is considering a new line of power tools aimed at the growing home renovation market. Based on their preliminary investigations, management has projected sales of the new power tools as follows:

The new power tools will be priced to sell at $120 per unit for the first three years. However, when the competition catches up after three years, the company anticipates that the price will drop to $110 per unit. The sales in units going to be 4200, 7000, 8400, 9100, 8400, 7000, 5500 and 4200. 

The variable costs per unit manufactured are estimated at $60 per unit. Fixed costs are estimated at $75,000 per year.

New fixed assets to produce the new line of power tools include a machine that will cost $800,000 installed, which will have a useful life of 8 years and a salvage value of $50,000 at the end of its life. All fixed assets are depreciated on a straight-line basis.

Management estimates that the project will require additional net working capital of $50,000 at the start of the project, beginning in Year 1. If your recommendation is to accept the project, management anticipates that production (and sales) could begin in 2016. The relevant tax rate for the company is 40%.

Mary noted during the management meeting that there was disagreement regarding assumptions used in the base case. Bob Redford, vice-president of marketing and sales, felt that the estimate of unit sales and the selling price were too conservative and wanted to bump up both by 10%. On the other hand, Chief Financial Officer Harry Ford noted uncertainty in the economy and thought projections for unit sales and the selling price were too optimistic. He wanted to reduce the estimates by 10%. Jake Nicholson, vice-president of operations, agreed with Harry’s concern about the economy and also thought variable and fixed costs should be 5% higher than the base case.

1. Use Excel to prepare “base case” pro forma income statements for the project for the period 2016 to 2023. Calculate free cash flows for each year.
2. Use Excel to prepare “best case” pro forma income statements for the project for the same period, assuming unit sales and the selling price are 10% higher than the base case. Calculate free cash flows for each year.
3. Use Excel to prepare “worst case” pro forma income statements for the project for the same period, assuming unit sales and the selling price are 10% lower than the base case. Also, variable and fixed costs are 8% higher than the base case.

Doing sensitivity analysis should take a matter of seconds if your Excel spreadsheet is well organized. Think about how you can structure your spreadsheet before you start entering your numbers.

4. Mary considered the input from the management team after the meeting. She decided there was a 60% chance that the “base case” would occur, a 20% chance that the “best case” would occur and a 20% chance that the “worst case” would occur. Calculate the expected net income (after tax) and free cash flow for the project.
5. Prepare a brief report to Mary Manic. The report should include a memo of 250 words summarizing your findings, plus the Excel spreadsheets for the base case, best case and worst case scenarios discussed above.