QUESTION 5 CVP Analysis Guide to marks: 20 marks - 8 for (a), 6 for (b), 6 for (c) We start with a simple use of Excel in CVP analysis. Copy this model into Excel: A B 1 Known parameters 2 Selling price per unit 10 3 Fixed cost 1000 4 Variable cost per unit 5 5 6 Variables 7 Number of units X 1000 8 9 Results 10 Total revenue =B2*B7 11 Fixed cost =B3 12 Total variable costs =B4*B7 13 Total costs =B11+B12 14 Profit =B10-B13 And the answer is: A B 1 Known Parameters 2 Selling price perunit 10 3 Fixed cost 1000 4 Variable cost per unit 5 5 6 Variables 7 Number of units X 1000 8 9 Results 10 Total revenue 10000 11 Fixed cost 1000 12 Total variable costs 5000 13 Total costs 6000 14 Profit 4000 If we want to calculate the BEP we can use Goal Seek (Data/What if analysis/Goal Seek). ______________________ Goal seek ? X Set cell $B$14 To value 0 By changing cell $B7 OK Cancel ________________________ and this produces zero in cell B14 and the breakeven units in cell B7 (ie 200). (a) A manufacturer can make product A. The following data are available: Selling price per unit $15, Variable cost per unit $7, Fixed cost $2,400. Modify the model shown above and invoking Goal Seek, determine the number of units required to break even. (b) Use the modified model again to determine the number of units required to earn a profit before tax of $1,600. (c) Now a second product is added, B: Selling price per unit $20, Variable cost per unit $10, Fixed cost $3,600 He decides to manufacture both A and B this year in the ratio of 2 of A to 1of B. Assuming total fixed costs are the sum of the fixed costs allocated to each product, how many of each product must be sold to earn a profit of $20,000?