H5 fx HE E F G H J L M N 1 2 Instructions: 1. Add correct formulas to the blank cells (referencing other cells where necessary) 2. Reformat all cells in order to make the Income Statement to the left look exactly like the picture to the right (bold, indents, text colors, column widths, italicize, $ signs - which can be modified using the number format of the cell) 3 4 5 6 7 2014 Formula Instructions (what goes into cells on left) 5000 3000 Equals "Sales - Cost of Goods Sold" Equals "Gross Profit / Sales" 500 500 Equals "Gross Profit - SUM of ("SG&A" and "Depreciation and Amortization")" 75 Equals "Operating Income - Net Interest Expense" Equals "35% times Pretax Income" Equals "Pretax Income - Tax" 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 1 2 Sales 3 Cost of Goods Sold 4 Gross Profit 5 Gross Profit Margin 6 SG&A 7 Depreciation and Amortization 8 Operating Income 9 Net Interest Expense 10 Pretax Income 11 Tax (35%) 12 Net Income B 2016 $ 10,000 $ 5,000 5,000 50% 1,000 500 3,500 75 3,425 1,199 $ 2,226 $ 2015 7,500 $ 4,000 3,500 47% 750 500 2,250 50 2,200 770 1,430 $ D 2014 5,000 3,000 2,000 40% 500 500 1,000 75 925 324 601 12 Exercise1 Exercise2 + D19 A x fx B D E F G H J K L M N o Q 2 2 Instructions 1. In cell B10, add the numbers above (B5 - B9) using the correct formula, round answer to nearest whole number 2. In cell D10, Show the "count" of all the non- blank, non-number cells above it (D5 - 09). 3. Fill in Cells G10 - G12 with the answer to the instruction to its left. Only use a formla to answer, referencing the entire range. 4. In Cell 110 Write today's date using a formula. If you've forgotten how to do this, use the Excel help menu to find the formula Apple 22 Monday 69 341 -312 14 0.55553 103 30 235 778 33.55 19000 40 19000 33.55 4017.31 2 3 4 5 5 6 7 9 10 11 12 13 14 15 16 17 18 14-09-2020 Max: Min: Average: 5. In Cells C21 and D21, reference the Sales figures for 2014 and 2015, respectively, from Exercise 1 6. Put formulas into the green cells below to forecast the next three years of Sales, assuming an 8% growth per year off of the previous year. a. You should reference cell H21 for your growth rate b. Remember that you can do this by adding "1" to your growth rate before multiplying c. You should get the correct formula into cell 124 first, using an appropriate "absolute reference" (with $ signs around the column and row) for your fixed cell, and a "relative reference" (no $ signs) for your other argument. For those new to this, try doing the formula first, referencing cells you need to, not worrying about the $ signs, and then put them in afterwards. d. You should be able to copy the formula from 124 into J24 and K24, and then double-check that it makes sense. 2014 2015 Sales: Growth Rate: 2017 2018 2019 2016 10,000 Sales: 19 20 21 22 2 23 24 25 26 27 28 29 BO 31 R2 Exercise 1 Exercise 2 +