I J A B D E H 1 Chapter 1: Applying Excel Name: G#: 2 3 Data 4 Sales $12,000 5 Variable costs: 6 Cost of goods sold $6,000 7 Variable selling $600 8 Variable administrative $400 9 Fixed costs: 10 Fixed selling $2,500 11 Fixed administrative $1,500 12 13 Enter a formula into each of the green cells marked with a ? below. Once complete use the "Copy Formula" button. 14 Exhibit 1-7 15 16 Traditional Format Income Statement 17 Sales ? 18 Cost of goods sold ? 19 Gross margin ? Copy Formulas 20 Selling and administrative expenses: 21 Selling ? 22 Administrative ? ? 23 Net operating income ? 24 25 Contribution Format Income Statement 26 Sales ? 27 Variable expenses: 28 Cost of goods sold ? 29 Variable selling ? 30 Variable administration ? ? 31 Contribution margin ? 32 Fixed expenses: 33 Fixed selling ? 34 Fixed administrative ? ? 35 Net operating income ? 36 37 38 39 40 41 42 43 Formulas Req 1 Ch1 Req 2 Ch1 + A B - F G H Req. 1: Enter $900 for the green variable selling cell in the data section. The Net Operating Income should now be $700 and the Contribution Margin should be $4,700 in red bold text. If not, go back to the formulas page, make corrections, press the copy formulas button, and recheck your answers until they are correct. When correct, answer the questions below. $12,000 $6,000 ?I $400 How much is the gross margin? $2,500 $1,500 Did the Gross Margin change from the previous Formulas page? ? ? ? ? 1 Chapter 1: Applying Excel 2 3 Data 4 Sales 5 Variable costs: 6 Cost of goods sold 7 Variable selling 8 Variable administrative 9 Fixed costs: 10 Fixed selling 11 Fixed administrative 12 13 14 Exhibit 1-7 15 16 Traditional Format Income Statement 17 Sales 18 Cost of goods sold 19 Gross margin 20 Selling and administrative expenses: 21 Selling 22 Administrative 23 Net operating income 24 25 Contribution Format Income Statement 26 Sales 27 Variable expenses: 28 Cost of goods sold 29 Variable selling 30 Variable administration 31 Contribution margin 32 Fixed expenses: 33 Fixed selling 34 Fixed administrative 35 Net operating income 36 37 38 39 40 41 42 43 ? Why or why not? ? ? ? ? ? ? ? ? ? ? ? ? Formulas Req 1 Ch1 Req 2 Ch1 + A B C - K E F G H Req. 2: Enter the data changes from req. 2 on page 45 of the textbook in the green data cells on the left. After the data is entered, answer the questions below. ? ? ? ? What happened to the variable and fixed costs after the 10% increase in Sales? $2,500 $1,500 Did the Contribution Margin change by 10%? Why or why not? ? ? ? ? 1 Chapter 1: Applying Excel 2 3 Data 4 Sales 5 Variable costs: 6 Cost of goods sold 7 Variable selling 8 Variable administrative 9 Fixed costs: 10 Fixed selling 11 Fixed administrative 12 13 14 Exhibit 1-7 15 16 Traditional Format Income Statement 17 Sales 18 Cost of goods sold 19 Gross margin 20 Selling and administrative expenses: 21 Selling 22 Administrative 23 Net operating income 24 25 Contribution Format Income Statement 26 Sales 27 Variable expenses: 28 Cost of goods sold 29 Variable selling 30 Variable administration 31 Contribution margin 32 Fixed expenses: 33 Fixed selling 34 Fixed administrative 35 Net operating income 36 37 38 39 40 41 42 43 ? Did the Net Operating Income increade by 10%? Why or why not? ? ? ? ? ? ? ? ? ? ? ? ? Formulas Reg 1 Ch1 Req 2 Ch1 +
