Excel Applications in Cost-Volume-Profit Analysis
Problem 1. From following information, calculate the amount of profit using marginal cost
technique if output level is 100000 units
Selling price Rs. 10 per unit
Variable cost Rs. 5 per unit
Fixed Cost Rs. 3,00,000
output 100000
PURTICULAR p.u. amount
SELLING PRICE 10 1000000
LESS: VARIABLE COST 5 500000
CONTRIBUTION 5 500000
LESS: FIXED COST 300000
PROFIT 200000
g marginal cost
t
Excel Applications in Cost-Volume-Profit Analysis
Problem 2. From following information, calculate BEP in units and rupees
Selling price Rs. 20 per unit
Variable cost Rs. 15 per unit
Fixed Cost Rs. 1,00,000
Selling price p.u 20
Variable cost p.u 15
Contribution 5
Fixed cost 100000
PV Ratio 25%
BEP(UNIT) 20000
BEP(SALE) 400000
ysis
and rupees
r unit
r unit
,000
Excel Applications in Cost-Volume-Profit Analysis
Problem 3. ABC Ltd. provides you the following forecast for the next budget period.
Selling price Rs. 50 per unit
Variable cost Rs. 30 per unit
Fixed Cost Rs. 1,80,000
You are required to calculate:
(i) Profit Volume Ratio. (ii) Break-Even Point in units and value
Selling price 50
Variable cost 30
Fixed Cost 180000
CONTRIBUTION 20
PV RATIO 40%
BEP(UNITS) 9000
BEP(SALE/VALUE) 450000
rofit Analysis
for the next budget period.
Rs. 50 per unit
Rs. 30 per unit
Rs. 1,80,000
:
Point in units and value
Problem 4: From following information, calculate:
(i) P/V ratio
(ii) BEP
(iii) If selling price is reduced to Rs 80. calculate new BEP
Total Sales 500000
Selling price p.u 100
Variable cost p.u 60
Fixed Cost 120000
TOTAL SALES 500000
SELLING PRICE p.u. 100
VARIABLE COST p.u. 60
FIXED COST 120000
CONTRIBUTION 40
PV RATIO 40%
BEP(UNIT) 3000
BEP(VALUE) 300000
NEW SELLING PRICE 80
NEW CONTRIBUTION 20
NEW PV RATIO 25%
NEW BEP(UNIT) 6000
NEW BEP(VALUE) 480000
Problem 5: Sales 200000
Profit 20000
Variable cost 60%
You are required to calculate:
(i) P/V Ratio
(ii) Fixed Cost
(iii) Sales Volume to earn profit of Rs 50000
SALE 200000
VARIABLE COST 120000
CONRTIBUTION 80000
PROFIT 20000
FIXED COST 60000
DESIRE PROFIT 50000
PV RATIO 40%
NEW SALE 275000
Problem 6. The following cost information relating to a product is supplied by a cost accountant.
Sales (20,000 units @ Rs. 40 per unit) Rs. 8,00,000
Variable Cost per unit @ Rs. 28 per unit) Rs.5,60,000
Fixed Cost Rs. 1,80,000
Profit Rs. 60,000
You are required to calculate:
(i) Variable cost ratio
(ii) P/V ratio
(iii) Break-even Sales
(iv) Sales to earn a profit of Rs.1,20,000
(v) Sales to earn a profit of 10% of Sales
(vi) Profit at a Sales level of Rs.12,00,000
(vii) Profit at a Sales level of 36,000 units
SALES 800000
VARIABLE COST 560000
CONRIBUTION 240000
CONRIBUTION per unit 12
FIXED COST 180000
PROFIT 60000
1 VARIABLE COST RATIO 0.7
2 PV RATIO 30%
3 BREAK EVEN SALES 600000
4 Sales to earn a profit of Rs.1,20,000 1000000
5 Sales to earn a profit of 10% of Sales 900000
10% OF SELLING PRICE 4
6 Profit at a Sales level of Rs.12,00,000 180000
7 Profit at a Sales level of 36,000 units--
NEW SALE 1440000
NEW VARIABLE COST 1008000
NEW CONTRIBUTION 432000
Profit at a Sales level of 36,000 units is 252000
accountant.
Problem 7: XYZ provides you the following estimated information relating to next year of its operations:
Sales 50,000 units
Selling price Rs. 20 per unit
Variable cost (Out of pocket costs) Rs.12 per unit
Fixed cost per annum Rs. 1,20,000
Calculate the following :
(i) Required sales to break even;
(ii) Required sales to earn a profit of Rs.1,00,000 (iii) Required sales to earn a profit of Rs.4 per unit;
(iv) Required sales to earn a profit of 15% on sales.
SALES IN UNITS 50000
SELLING PRICE 20
SALES 1000000
VARIABLE COST 600000
CONTRIBUTION 400000
CONTRIBUTION PER UNIT 8
FIXED COST 120000
PV RATIO 40%
1 sales to break even 300000
2 Required sales to earn a profit of Rs.1,00,000 550000
3 Required sales to earn a profit of Rs.4 per unit 600000
4 Required sales to earn a profit of 15% on sales--- 480000
15% on sales 3
ts operations:
units
er unit
r unit
,000
per unit;
P8 Product X has variable costs of Rs 2 per unit, and selling price of Rs 6 per unit. The fixed costs are Rs 1,000 per year
(a) What is the breakeven point (in units)?
(b) What is the breakeven revenue?
(C) How many units need to be sold to achieve a target profit of 30000 per year?
VARIABLE COST 2
SELLING COST 6
FIXED COST 1000
CONTRIBUTION 4
1 breakeven point (in units) 250
2 Revenue at break even point 1500
3 Sales for target profit 30000 7750
s are Rs 1,000 per year
P9: From the following particulars, you are required to find out:
(a) Contribution
(b) Break-even point in units
(c) Profit
(d) volume of sales to earn profit of Rs. 6,000
Total Fixed cost 4500
Total Variable cost 7500
Total Sales 15000
Units Sold 5000
SELLING PRICE
A CONTRIBUTION
CONTRIBUTION PER UNIT
B Break-even point in units
C PROFIT
D Sales to earn profit of 6000 IN UNITS
Sales to earn profit of 6000 in monetary VALUE
3
7500
1.5
9000
3000
7000
21000
P10 Sales Price - Rs. 20 unit
Variable manufacturing cost - Rs. 11 per unit
Variable selling cost - Rs. 3 per unit.
Fixed factory overheads - Rs. 5,40,000 per year.
Fixed selling costs - Rs. 2,52,000 per unit.
Calculate:
(a) BEP Volume and Value.
(b) Sales required to earn a profit of Rs. 60,000.
(c) Sales required to earn a profit of 10% of sales.
PV RATIO
SALES PRICE 20
TOTAL VARIABLE COST 14
TOTAL FIXED COST 792000
CONTRIBUTION PER UNIT 6
10% OF SELLING PRICE 2
A BEP IN UNITS 132000
BEP IN VALUE 2640000
B Sales required to earn a profit of Rs. 60,000 142000
C Sales required to earn a profit of 10% of sales in units 198000
Sales required to earn a profit of 10% of sales in value 3960000
