Old MathJax webview

Please complete questions 2 & 4

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 - night-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 (Year 1-Year 4) 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 & Allowances Cost of Goods Sold = Direct Materials + Direct Labor + Factory Overhead d Gross Profit = Net Sales - Cost of Goods Sold General & Administrative Expense = General & Administrative Expense Rate x Net Sales f Profit before Tax = Gross Profit - General & Administrative Expense g Taxes = Tax Rate x Profit before Tax h Profit after Tax = Profit before Tax -Taxes Note: Please include an =IF statement in Taxes calculation to preclude paying taxes when the company has a net loss - companies do not pay taxes when they have net loss in any period. e 3 PDF page 1: Print the Pro Forma Statement (Cells A11F26) 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.) - Print into a single page. 4. Change the assumption figures to the following values a Gross Sales Growth 4% b Direct Materials Growth 8% Direct Labor Growth d Factory Overhead Growth e General & Admin Rate 17% f Tax Rate 35% 6% 6% 5. PDF page 2: Print the revised four-year (Year 1-Year 4) Pro Forma Income Statement (Cells A11 F26) with the changed assumptions specified in (4) above Again, print the whole spreadsheet into one page PDF with good format as specified. 6 PDF page 3: Print cell formulas for Year 2 (ie, Cells C 15.026). Print only the specified area into yet another single page PDF. Make sure that you do not hardwire in any part in your formulas (There will be serious point deduction if you hardwired in your spreadsheet We will explain in class ) The Greenleaf Company 1 NEW 2 3 Assumptions 4 Gross Sales Growth 5 Direct Materials Growth 6 Direct Labor Growth 7 Factory Overhead Growth 8 General & Admin Exp. Rate 9 Tax Rate 2.00% 5.00% 7.00% 5.00% 18.00% 30.00% 10 11 The Greenleaf Company Pro Forma Income Statement 12 VARU 13 14 Year 1 Year 2 Year 3 Year 4 Total 3985 15 Gross Sales 16 Less: Returns & Allowances 17 Net Sales 702.95 859.17 1288.75 18 Direct Materials 19 Direct Labor 20 Factory Overhead 21 Cost of Goods Sold 22 Gross Profit 23 General & Admin Exp. 24. Profit before Tax 25 Taxes 26 Profit after Tax 27