Financial Forecast

Financial planning and budgeting is one of the most common business applications of spreadsheets. Attached Excel sheet depicts one such illustration, in which the income and the expenses of Get Rich Quick Enterprises are projected over a six-year period. You need to follow a logical approach to develop the worksheet using these instructions.
A. Develop the formulas for the first year 2017 based on the ASSUMPTIONS (Initial Conditions) provided at the bottom of the spreadsheet “MAN18-HW#8-Excel Sheet without formulas.”
a. The projected income for the first year (2017) of the forecast (cell B7) is $308,000 based on 77,000 units sold at a price of $4.00 per unit (provided under the Assumptions in cells B24 and B25 respectively).
b. The overhead (fixed Costs) consists of the production facility at $55,000 and administrative expenses of $23,500 (provided under the Assumptions in cells B26 and B27 respectively).
c. The variable costs for the same year are broken down for manufacturing as $77,000 (77000 units at $1.00 per unit) and for sales as $15,400 (77,000 units at $.20 per unit).
d. Subtracting the total costs (production + administration + variable manufacturing + variable sales) from the gross revenue should yield a net income before taxes of $137,100.
e. The income tax is computed using the tax rate in B29.
f. Income tax is then subtracted from Earnings before Taxes to compute net earnings of $97,341 in the first year.

B. Develop the formulas for the second year (2018), based on the values in year 1 (2017) and the assumed rates of annual increase under the Assumptions in cells D24, D25, D26, D27, and D28. Use an appropriate combination of relative and absolute addresses so that these formulas can be copied to the remaining columns in the worksheet.
C. Copy the formulas for year 2018 (in column C) to the remaining years of the forecast (column D through G).
D. Format the completed worksheet. You do not have to match the formatting exactly, but you are to display dollar amounts with the currency symbol and appropriate decimal places.
E. And, do improve the looks of the spreadsheet according to your preference.
F. Make sure to mention Homework #8 and your name in the Subject Line.

Leave a Reply