Homework answers / question archive / This week you will be starting to put together a pro forma financial model

This week you will be starting to put together a pro forma financial model

Accounting

Share With

This week you will be starting to put together a pro forma financial model. You will be getting some additional practice with forecasting. Pay particular attention to how your cells are linked together through formulas and functions, as this is the essence of a model. It may be helpful to review the historical part of the financial statements to be sure you understand how each of the total lines were calculated. Your pro forma model must be calculated in a manner consistent with the historical financial statements presented.

Each item is worth the amount of points stated, for a total of 26 points. Always use formulas with cell references when possible. All answers should go in the relevant blue-shaded cells.

In the Financials worksheet:

1. (2 points) Forecast "COGS excluding D&A" using the constant percent of sales assumption in column G.
2. (2 points) Calculate "Depreciation & Amortization Expense" in row 8.
3. (2 points) Calculate "Cost of Goods Sold (COGS) Incl. D&A" in row 6.
4. (2 points) Calculate "Gross Income" in row 11.
5. (2 points) Calculate "EBIT (Operating Income)" in row 13.
6. (3 points) Complete the "Intangible Assets" Control Account.
7. (1 point) Forecast "Intangible Assets" in the balance sheet.
8. (2 points) Calculate "Total Assets."
9. (3 points) Complete the "Long-Term Debt" Control Account.
10. (1 point) Forecast "Long-Term Debt" in the balance sheet.
11. (2 points) Forecast "Interest Expense" using the interest rate in column G. Base your calculation on the average of the beginning and ending balances of "Long-Term Debt" for each year.
12. (2 points) Calculate the "Balance Sheet Check" in the same way as the online course materials to see if the balance sheet balances. (It's ok if it doesn't balance.)
13. (2 points) Answer the question in cell A70 using the drop-down menu in cell B70.