HE SU Homework 1 - Excel (3) X At g # Arial " 10 Ai = , ab 1911 V V ["47- Ou Ou 8 DX y B I I U A abca v y % ) V .00 20 1418 E ##O tv H25 fx A B D E F G H 1 J L M N Exercise 1 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) 2016 10000 5000 2015 7500 4000 1000 500 750 500 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" 4 75 50 1 2 Sales 3 Cost of Goods Sold Gross Profit 5 Gross Profit Margin 6 SG&A 7 Depreciation and Amortization Operating Income 9 Net Interest Expense 10 Pretax Income 11 Tax (35%) 12 Net Income 3 4 5 6 Sales 7 Cost of Goods Sold 8 Gross Profit 9 Gross Profit Margin 10 ce 10 SG&A 11 Depreciation and Amor 12 Operating Income 13 Net Interest Expense 14 Pretax Income 15 Tax (35%) 16 Net Income 17 18 To 19 20 20 21 21 22 23 20 24 27 25 20 20 26 27 27 20 28 20 29 30 31 32 33 so 34 34 35 36 37 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 D 2014 5,000 3,000 2,000 40% 500 500 1,000 75 925 324 601 8 50 2,200 770 1,430 $ 13 Exercise 1 Exercise 2 + + 124% HES. Homework 1 - Excel (3) At #2 Arial 10 "AI ab 490 4 Z 5 B A 3 A# 18 LE v % QUE ** et ## Wat A6 fc Sales A B D E F G H ITILI ULLIUITS. 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 2016 10000 5000 2015 7500 4000 A 1 1000 500 750 500 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" 4 75 5 6 Sales 7 Cost of Goods Sold 8 Gross Profit 9 Gross Profit Margin 10 SG&A 11 Depreciation and Amort 12 Operating Income 13 Net Interest Expense 14 Pretax Income 15 Tax (35%) 16 Net Income 17 18 19 20 21 22 23 24 25 26 27 50 2 Sales 3 Cost of Goods Sold Gross Profit 5 Gross Profit Margin 6 SG&A 7 Depreciation and Amortizat 8 Operating Income 9 Net Interest Expense 10 Pretax Income 11 Tax (35%) 12 Net Income 12 Exercise 1 Exercise2 + + 170% HE SU Homework 1 - Excel (3) ) X At g Arial " 10 Ai , = V V WE AYO ou 3 DX y #606 B I U ar Av ab y % ) 1418 #t 20 DISSE tv ## ###O A1 4 x fx A B D E F G H J K 1 1 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 - D9). 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 3 Apple 4 5 6 7 8 22 69 341 -312 14 0.55553 Monday 235 778 33.55 19000 40 30 Max: Min: Average: 9 10 11 12 13 14 15 16 17 18 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: 19 20 21 22 23 24 25 2017 2018 2019 2016 10,000 Sales: Exercise 1 Exercise 2 + + 132% HE SU Homework 1 - Excel (3) At 9 # Arial * 10 ~ AI = ab WE 5 LO #606 B I U a. Av ab y C% 1418 DX Net ## ###O A1 x fx D E F G H | 2. In cell D10, Show the "count" of all the non-blank, non-number cells above it (D5 - D9). 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 235 778 33.55 Monday 30 19000 40 B TULI VULVITU 1. In cell B10, add the numbers above (B5-B9) using the correct formula, round answer to nearest 3 whole number 4 5 69 6 341 7 -312 8 14 9 0.55553 10 11 12 13 14 15 16 17 18 5. In Cells C21 and D21, reference the Sales figures for 2014 and 2015, respectively, from Exercise 1 Max: Min: Average: 6. Put formulas into the green cells below to forecast the next three years of Sales, assuming an 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 around the column and row) for your *fixed* cell, and a "relative reference" (no $ signs) for your For those new to this, try doing the formula first, referencing cells you need to, not worrying abo 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 2014 2015 19 20 21 Sales: Exercise 1 Growth Rate: Exercise2 + + 150% HE SU Homework 1 - Excel (3) X At g # Arial " 10 Ai = , ab 1911 V V ["47- Ou Ou 8 DX y B I I U A abca v y % ) V .00 20 1418 E ##O tv H25 fx A B D E F G H 1 J L M N Exercise 1 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) 2016 10000 5000 2015 7500 4000 1000 500 750 500 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" 4 75 50 1 2 Sales 3 Cost of Goods Sold Gross Profit 5 Gross Profit Margin 6 SG&A 7 Depreciation and Amortization Operating Income 9 Net Interest Expense 10 Pretax Income 11 Tax (35%) 12 Net Income 3 4 5 6 Sales 7 Cost of Goods Sold 8 Gross Profit 9 Gross Profit Margin 10 ce 10 SG&A 11 Depreciation and Amor 12 Operating Income 13 Net Interest Expense 14 Pretax Income 15 Tax (35%) 16 Net Income 17 18 To 19 20 20 21 21 22 23 20 24 27 25 20 20 26 27 27 20 28 20 29 30 31 32 33 so 34 34 35 36 37 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 D 2014 5,000 3,000 2,000 40% 500 500 1,000 75 925 324 601 8 50 2,200 770 1,430 $ 13 Exercise 1 Exercise 2 + + 124% HES. Homework 1 - Excel (3) At #2 Arial 10 "AI ab 490 4 Z 5 B A 3 A# 18 LE v % QUE ** et ## Wat A6 fc Sales A B D E F G H ITILI ULLIUITS. 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 2016 10000 5000 2015 7500 4000 A 1 1000 500 750 500 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" 4 75 5 6 Sales 7 Cost of Goods Sold 8 Gross Profit 9 Gross Profit Margin 10 SG&A 11 Depreciation and Amort 12 Operating Income 13 Net Interest Expense 14 Pretax Income 15 Tax (35%) 16 Net Income 17 18 19 20 21 22 23 24 25 26 27 50 2 Sales 3 Cost of Goods Sold Gross Profit 5 Gross Profit Margin 6 SG&A 7 Depreciation and Amortizat 8 Operating Income 9 Net Interest Expense 10 Pretax Income 11 Tax (35%) 12 Net Income 12 Exercise 1 Exercise2 + + 170% HE SU Homework 1 - Excel (3) ) X At g Arial " 10 Ai , = V V WE AYO ou 3 DX y #606 B I U ar Av ab y % ) 1418 #t 20 DISSE tv ## ###O A1 4 x fx A B D E F G H J K 1 1 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 - D9). 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 3 Apple 4 5 6 7 8 22 69 341 -312 14 0.55553 Monday 235 778 33.55 19000 40 30 Max: Min: Average: 9 10 11 12 13 14 15 16 17 18 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: 19 20 21 22 23 24 25 2017 2018 2019 2016 10,000 Sales: Exercise 1 Exercise 2 + + 132% HE SU Homework 1 - Excel (3) At 9 # Arial * 10 ~ AI = ab WE 5 LO #606 B I U a. Av ab y C% 1418 DX Net ## ###O A1 x fx D E F G H | 2. In cell D10, Show the "count" of all the non-blank, non-number cells above it (D5 - D9). 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 235 778 33.55 Monday 30 19000 40 B TULI VULVITU 1. In cell B10, add the numbers above (B5-B9) using the correct formula, round answer to nearest 3 whole number 4 5 69 6 341 7 -312 8 14 9 0.55553 10 11 12 13 14 15 16 17 18 5. In Cells C21 and D21, reference the Sales figures for 2014 and 2015, respectively, from Exercise 1 Max: Min: Average: 6. Put formulas into the green cells below to forecast the next three years of Sales, assuming an 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 around the column and row) for your *fixed* cell, and a "relative reference" (no $ signs) for your For those new to this, try doing the formula first, referencing cells you need to, not worrying abo 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 2014 2015 19 20 21 Sales: Exercise 1 Growth Rate: Exercise2 + + 150%