1 Purpose of Chapter 21 Excel Activity 2 To create an Excel report from scratch. 3 4 5 Using formulas, cell referencing, sizing columns, and formatting. Practice using the following chapter 21 calculations in Excel Contribution margin 6 Break-even analysis 7 8 Example: Product A Selling price per unit $75 Variable expense per unit $45 Fixed expenses $210,000 Targeted net income $126,000 Contribution Margin per unit Selling price per unit $75 Variable expense per unit 45 $60 Contribution Margin per unit $30 Variable expense per unit $35 Fixed expenses $123,000 Contribution ratio 40% Targeted net income $55,000 Break-even Units Sales 7,000 $525,000 $8 Variable expense per unit $5 Targeted net income Units $257,000 Sales 11,200 $840,000 Targeted net income $90,000 Conribution Margin Income statement Units Break-even Target 7,000 9 Targeted net income Instructions 10 I. Using the picture to the right as an example create a report for Product B 1 Compute the contribution margin. Product Information Product B Selling price per unit 11 12 13 2 Compute the contribution ratio. 14 3 Compute break even in units. 15 4 Compute break even in sales. 16 5 Compute targeted net income sales. 17 6 Compute a targeted net income in units. 18 19 20 21 7 Prepare a prepare contribution margin Income statement for break-even. 8 Prepare a prepare contribution margin Income statement for targeted net income. 22 II. Copy your worksheet Product C Selling price per unit Fixed expenses 23 1 Copy Product B worksheet to a new sheet 24 2 Rename the new sheet 25 3 Change the assumptions to Product C information 26 If you prepare the formulas for product B correctly you will only need to change 27 the data for product C. 28 Excel Instructions You can copy the product B information to Product B sheet Replicating the example is not required, create it how you determine it should look. For all number except product information, use cell referencing and formulas Format the report properly, so it is easy for your boss to read. (me) 29 30 31 32 33 34 35 When completed, copy Product B worksheet 36 37 38 39 40 Rename the worksheet from Product B (2) to Product C Change the product information to Product C data If your formulas were done correctly, you will not have to change the new spreadsheet. The new data will recalculate your reporting. Sales Variable expenses Contribution margin Fixed expenses Net operating income 11,200 $525,000 $840,000 315,000 504,000 210,000 336,000 210,000 210,000 $0 $126,000