Exercise 1

Pro Forma Income Statement

Retrieve Exercise01.xls from class website. Do the following:

1. Format the spreadsheet as follows:

a. All headings - boldfaced and centered.b. All numbers - right-justified with two decimal places. (In general, numbers are right-justified.)c. All texts - left-justified. (In general, texts are left-justified.)d. Please do not add $ signs unless it is specified in the exercise.2.Complete the four-year (2017-2020) Pro Forma Income Statement using the following relationships, assuming that Gross Sales, Direct Materials, Direct Labor, and Factory Overhead will all increase based on their respective growth rates:

a. Returns & Allowances rate is always 2% of Gross Sales.b. Net Sales = Gross Sales - Returns & Allowancesc. Cost of Goods Sold = Direct Materials + Direct Labor + Factory Overheadd. Gross Profit = Net Sales - Cost of Goods Solde. General & Administrative Expense = General & Administrative Expense Rate x Net Salesf. Profit before Tax = Gross Profit - General & Administrative Expenseg. Taxes = Tax Rate x Profit before Taxh. Profit after Tax = Profit before Tax - Taxes

3. Print the Pro Forma Statement (Cells A11:F26) with spreadsheet frame. Make sure that your printout will fit into one page, centered, and properly labeled. Please DO NOT print any extra areas that do not contain any text or number. (Unless otherwise specified, please always print with grids.)

4. Change the assumption figures to the following values:a. Gross Sales Growth:4%b. Direct Materials Growth:8%c. Direct Labor Growth:6%d. Factory Overhead Growth:6%e. General & Admin. Rate: 17%f. Tax Rate:35%