A B D E F G H 1 Click on the CVP tab and enter your...

Transcribed Image Text:

A B D E F G H 1 Click on the "CVP" tab and enter your name in Cell C1. If Cell C1 is left blank, you will not be able to see your score. Each yellow cell requires a formula. 2 3 The formula must only contain cell addresses. 4 Each correct formula will begin with =, +, or -. 5 6 The basic mathematical operators are +, -, * and/ 7 Addition: To add the values in cells A1 and B4, the formula is =A1+B4 8 J K L 9 Subtraction: To subtract the value in cell B4 from the value in cell A1, the formula is =A1-B4 Multiplication: To multiply the values in cells A1 and B4, the formula is =A1*B4 10 Division: To divide the value in cell A1 by the value in cell B4, the formula is =A1/B4 11 12 Using absolute addresses 14 13 Once a formula is created, you will often want to copy the formula to other columns and rows. It's important to understand that excel always uses "relative addresses" unless you indicate otherwise by creating an "absolute address". For example, if you are calculating the cost of purchasing 2 boxes of cereal at a cost of $3.95 per box, the formula in cell B20 is =B18*B19. Although we read cell B20 as "Multiply 2 boxes by $3.95", Excel is actually calculating the value in cell B20 as "Multiply the value in the cell in the same column (column B) 2 rows above (row 19) by the value in the cell in the same column 1 row above (row 19). 15 16 17 18 Boxes of cereal 2 6 11 15 19 Price per box 20 Total cost $3.95 $7.90 $23.70 $43.45 $59.25 21 22 This works just fine to calculate the cost of 2 boxes, but if we copy the formula in cell B20 to C20, Excel will assume relative references. The formula 23 in cell C20 will become =C18*C19. Since Cell C19 is blank, the formula will return a value of $0 (which is an excellent deal for 6 boxes of cereal, but it 24 isn't accurate!) Adding a $ sign before the column or row reference locks the formula to a specific location. 25 26 27 If we modify the formula in cell B20 to =B18*$B$19 before we copy the formula, Excel will not change the row or the column of the second variable. 28 When the formula is copied to columns C through E, the formulas become: 29 30 31 Cell C20 =C18*$B$19 Cell D20 =D18*$B$19 32 33 Cell E20 =E18*$B$19 34 Sometimes, you will want the column to stay fixed, but allow the row to remain relative when it's copied. If so, add a dollar sign before the column, 35 but not the row (i.e.$B19) If you want the row to stay fixed, but allow the column to remain relative when it's copied, add a dollar sign before the row, 36 but not the column (i.e. B$19). Tapping the F4 button while entering the formula (or in the formula bar) toggles among the 3 absolute options 37 (Absolute Column and Absolute Row $B$20, Absolute Column and Relative Row $B20, Relative Column and Absolute Row B$20) A 1 B Name: C D E F G Name Your score: 0% 2 Name Company reports the contribution margin income statement for 2023 below. Using this information, compute Name Company's 1) break-even point in units, 2) break-even point in sales dollars, 3) unit sales to achieve target income, and 4) dollar sales to 3 achieve target income. 4 5 6 Sales 7 Variable costs 8 9 Contribution margin Fixed costs 10 Income Units $ per unit 3,000 $330 Total $990,000 3,000 231 693,000 297,000 207,900 $89,100 11 12 13 14 15 16 1) Compute the break-even point in units 2) Compute the break-even point in sales dollars 17 18 19 Target income $148,500 20 Sales (units) to achieve targeted income of $148,500 21 Sales (dollars) to achieve targeted income of $148,500 22 23 24 25 26 37 A B D E F G H 1 Click on the "CVP" tab and enter your name in Cell C1. If Cell C1 is left blank, you will not be able to see your score. Each yellow cell requires a formula. 2 3 The formula must only contain cell addresses. 4 Each correct formula will begin with =, +, or -. 5 6 The basic mathematical operators are +, -, * and/ 7 Addition: To add the values in cells A1 and B4, the formula is =A1+B4 8 J K L 9 Subtraction: To subtract the value in cell B4 from the value in cell A1, the formula is =A1-B4 Multiplication: To multiply the values in cells A1 and B4, the formula is =A1*B4 10 Division: To divide the value in cell A1 by the value in cell B4, the formula is =A1/B4 11 12 Using absolute addresses 14 13 Once a formula is created, you will often want to copy the formula to other columns and rows. It's important to understand that excel always uses "relative addresses" unless you indicate otherwise by creating an "absolute address". For example, if you are calculating the cost of purchasing 2 boxes of cereal at a cost of $3.95 per box, the formula in cell B20 is =B18*B19. Although we read cell B20 as "Multiply 2 boxes by $3.95", Excel is actually calculating the value in cell B20 as "Multiply the value in the cell in the same column (column B) 2 rows above (row 19) by the value in the cell in the same column 1 row above (row 19). 15 16 17 18 Boxes of cereal 2 6 11 15 19 Price per box 20 Total cost $3.95 $7.90 $23.70 $43.45 $59.25 21 22 This works just fine to calculate the cost of 2 boxes, but if we copy the formula in cell B20 to C20, Excel will assume relative references. The formula 23 in cell C20 will become =C18*C19. Since Cell C19 is blank, the formula will return a value of $0 (which is an excellent deal for 6 boxes of cereal, but it 24 isn't accurate!) Adding a $ sign before the column or row reference locks the formula to a specific location. 25 26 27 If we modify the formula in cell B20 to =B18*$B$19 before we copy the formula, Excel will not change the row or the column of the second variable. 28 When the formula is copied to columns C through E, the formulas become: 29 30 31 Cell C20 =C18*$B$19 Cell D20 =D18*$B$19 32 33 Cell E20 =E18*$B$19 34 Sometimes, you will want the column to stay fixed, but allow the row to remain relative when it's copied. If so, add a dollar sign before the column, 35 but not the row (i.e.$B19) If you want the row to stay fixed, but allow the column to remain relative when it's copied, add a dollar sign before the row, 36 but not the column (i.e. B$19). Tapping the F4 button while entering the formula (or in the formula bar) toggles among the 3 absolute options 37 (Absolute Column and Absolute Row $B$20, Absolute Column and Relative Row $B20, Relative Column and Absolute Row B$20) A 1 B Name: C D E F G Name Your score: 0% 2 Name Company reports the contribution margin income statement for 2023 below. Using this information, compute Name Company's 1) break-even point in units, 2) break-even point in sales dollars, 3) unit sales to achieve target income, and 4) dollar sales to 3 achieve target income. 4 5 6 Sales 7 Variable costs 8 9 Contribution margin Fixed costs 10 Income Units $ per unit 3,000 $330 Total $990,000 3,000 231 693,000 297,000 207,900 $89,100 11 12 13 14 15 16 1) Compute the break-even point in units 2) Compute the break-even point in sales dollars 17 18 19 Target income $148,500 20 Sales (units) to achieve targeted income of $148,500 21 Sales (dollars) to achieve targeted income of $148,500 22 23 24 25 26 37