Question

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?

Answer 1

Using the above formulaes in Excel, the answers are as follows: