Financial planning and budgeting is one of the most common business applications of spreadsheets. The figure shown below depicts one such illustration, in which the income and expenses of "Get Rich Quick Enterprises" are projected over a six-year period.
Open theFinancial_Forecast.csvfile and copy the data into a valid Excel workbook. Although this file can be opened using Excel, it is NOT an Excel file...it is still a text file. If you try to enter formulas and save the file (while still a .csv file), upon closing the file, all your formulas and formatting will be lost.When opening text files with Excel, you absolutelyMUSTeither copy the data into a new or existing Excel workbook OR save the .csv file as an actual Excel Workbook.Before beginning, please copy the Financial Forecast data into a new tab of your current Chapter 1 Assignment file(in which you completed the first two problems).Then proceed as follows.
1. Develop formulas for the column headings starting with the second year.(The first column of the forecast will use the current year…you may just manually enter the current year rather than using a formula.) Your formulas should be flexible so if you change the current year, the subsequent year column headings change automatically.
Gross revenue is calculated using the Units Sold & Unit Price. The overhead (fixed costs) consists of the production facility & administrative expenses.
The variable costs are broken down into Variable manufacturing cost (# of units times Unit manufacturing cost) and Variable sales cost (# of units times Unit sales cost). (Assume all units manufactured will be sold, therefore units manufactured & units sold will be the same value.)
Earnings before Taxes are calculated by subtracting the total expenses (Overhead and COGS) from the estimated income (Gross revenue). (Income taxes are based on Earnings.)
Net Earnings are calculated by subtracting the Income taxes from the Earnings before taxes.
2. Develop formulas for the second year, based on the values in the first year and the assumed rates of increase in the worksheet. Use an appropriate combination of relative and absolute references so that these formulas can be copied to the remaining columns in the worksheet. (Ensure that you refer to the assumptions, even if the assumption happens to contain a 0 value...such as when calculating the Unit Sales Cost. Assumptions should be able to be changed and the worksheet will adjust accordingly.)
3. Copy the formulas for the second year to the remaining years of the forecast.