Excel Applications in Decision Making (Problems taken from Management Accountin

Surender Singh Edition 2024)

PRICING DECISIONS

Problem 1(Example 1 Page 6.9). SR Ltd. produces and sells a product. Due to severe competition, the company
proposes to reduce the selling price. Following is the details of its current year’s operations:
Sales (30,000 units × 100) Rs. 30,00,000
Variable costs Rs. 18,00,000
Fixed costs Rs. 7,00,000
Total cost Rs. 25,00,000
Profit Rs. 5,00,000
(i) Find out the increase in number of units to be sold if selling price is reduced by (a) 5% and (b) 10% while
maintaining the existing level of profit.
(ii) If company decides to sell 48,000 units in the next year to out-place the competitors, find out theprice it should
charge to continue to earn the existing level of profit.

Solution:
Assumption: It is assumed that fixed cost will remain same during the next year also.
Statement of Contribution and Profit

(i) Existing When selling price is reduced

by
5% 10%
No. of units sold 30,000 ? ?

Per unit (Rs.) Total (Rs.) Per unit (Rs.) Per unit (Rs.)

Sales 100 3000000 95 90

Less: Variable cost 60 1800000 60 60

Contribution 40 1200000 35 30
Less: Fixed cost 700000
Profit 500000

(a) When selling price is reduced by 5%

Required Sales (units) (FC+DP)/Cp.u 34285.714286
or say 34286
Increase in units 4,286
(b) When selling price is reduced by 10%
Required Sales (units)= (FC+DP)/Cp.u 40000
Increase in units = 10,000
(ii) Required contribution = . Sales (units) × Cp.u
Cp.u. = Required contribution/ Sales (units 25
Selling Price(Rs.) = Cp.u.+VCp.u 85
Management Accounting Book by Prof.

Alternate Solution By Using Goal Seek

Proposal to reduce SP
ompetition, the company Current by 5 percent by 10 percent
ations: Selling price 100 95 90
Variable Cost 60 60 60
Contribution 40 35 30
Units 30000 34285.7142857143 40000
5% and (b) 10% while Total Contribution 1200000 1200000 1200000
Fixed Cost 700000 700000 700000
ors, find out theprice it should Profit 500000 500000 500000
Increase in Units 4285.71428571428 10000
al Seek
Proposed

85
60
25
48000
1200000
700000
500000
 Problem2 : (Example 2 Page 6.10) A toy manufacturer earns an average net profit of 30 per unit in a selling price of 150
by producing and selling 60,000 units at 60% of its capacity. The composition of his cost of sales is as follows:
Rs.
Direct material 40
Direct wages 10
Works overheads 50 (50% Fixed)
Sales overheads 20 (25% Variable)
During the current year, he intends to produce the same number but anticipates that :
(i) The fixed charges will go up by 10%.
(ii) Rates of labour will increase by 20%.
(iii) Rates of direct materials will increase by 5%.
(iv) Selling price cannot be increased.
Under these circumstances he obtains an order for a further 20% of his capacity. What minimum price will you
recommend for accepting the order so as to ensure the manufacturer earn an overall profit of Rs. 17,40,000

Solution: Statement of Contribution and Profit (without Special Offer)

Year Last Year Current Year

Capacity 60% 60%

No. of Units 60000 60000
Selling Price 150 150
Variable Cost p.u.:
Direct Materials 40 42
Direct labour 10 12
Works overheads 25 25
Sales overhead 5 5
Variable Cost p.u. 80 84
Contribution p.u. 70 66
Total Contribution 4200000 3960000
Fixed Cost:
Works overheads 1500000 1650000
Sales overhead 900000 990000
Total Fixed Overhead 2400000 2640000
Profit 1800000 1320000

Calculation of additional contribution / profit to be earned from special offer :

Desired Profit 1740000

Less: Estimated Profit 1320000
Additional Profit to be earned from special offer 420000
Additional Units 20000
Additional Cp.u. = Additional contribution/ Sales 21
Recommended Selling Price(Rs.) = Cp.u.+VCp.u 105
 Alternate Solution by Goal Seek
Year Last Year Current Year
Capacity 60% 60% 20%
No. of Units 60000 60000 20000
Selling Price 150 150 105 Goal Seek
Direct Materials 40 42 42
Direct labour 10 12 12
Works overheads 25 25 25
Sales overhead 5 5 5
Variable Cost p.u. 80 84 84
Contribution p.u. 70 66 21
Total Contribution 4200000 3960000 420000
Fixed Cost:
Works overheads 1500000 1650000 0
Sales overhead 900000 990000 0
Total Fixed Overhead 2400000 2640000 0
Profit 1800000 1320000 420000
Desired Profit 1740000
Remaining Profit 420000
 P3. (Illustration 5. Page 6.38) (Pricing Decision) A manufacturing company has an installed capacity of 1,20,000 units per
annum. The cost structure of the product is given below:
Variable cost (per unit):
Material : Rs. 8
Labour (subject to a minimum of 56,000 per month) : Rs.8
Overheads : Rs. 3
Fixed overheads : Rs.1,68,750 per annum.
Semi-variable overheads : Rs.48,000 per annum at 60% capacity, which
increase by 6,000 per annum for every 10% increase in the capacity utilisation or any part thereof for the year as a
whole.
The capacity utilisation for next year is estimated at 60% for first 2 months; 75% for next 6 months and 80% for the
remaining part of the year. If the company is planning to have a profit of 25% on the selling price, then calculate the
selling price per unit for the year under consideration.
Assume that there is no opening or closing stock.

Solution: Statement of Cost of Production and Profit

Month Ist 2 months Next 6 months Last 4 months For Whole Year
Capacity 60% 75% 80% 74.17%
Production/Sales (units) 12000 45000 32000 89000
Material 96000 360000 256000 712000
Labour 112000 360000 256000 728000
Variable Overheads 36000 135000 96000 267000
Total Variable Cost 244000 855000 608000 1707000
Semi-Variable Cost 60000
Fixed Cost 168750
Total Cost 1935750
Add: Profit (1/3 of cost) 645250
Sales 2581000
Selling Price 29
 Problem 4. (Illustration 6. Page 6.39) Modern Sports Ltd. has for the past several years produced boxing
gloves which are sold at Rs.28 per pair. Higher costs in recent years have made management to consider
about the adequacy of this selling price.
The labour rate was increased from Rs.1.75 per hour to Rs. 2.25 per hour and the cost of leather has gone
up from Rs.1.10 to Rs.2.15 per square foot during the last five years. Fixed expenses have increased 25%
from the level of Rs.18,000 five years ago. Over the same period variable overhead has increased 30%, or
Rs.3 per pair of gloves. Each glove requires 1.5 sq. ft. of leather and one hour of direct labour.
Calculate the selling price that the company has to charge under the new cost structure to break-even at the
same number of units as five years ago.

Solution: Statement of Marginal Cost (Per Pair of Boxing Glove)

Particulars Five Years Ago (Rs.) Now (Rs.)

Selling Price (Sp.u.) 28 ?

Direct Material 3.3 6.45
Direct Labour 3.5 4.5
Variable Overheads 10 13
Variable Cost per pair 16.8 23.95
Contribution (Cp.u.) 11.2
P/V Ratio Cp.u./Sp.u. 40%
Fixed Cost (FC) 18000 22500
BEP(units) FC/Cp.u. 1607.14285714286
Or Say 1607 1607

Calculation of Current Selling Price to break even at 1607 units

Contribution (Cp.u.) FC/BEP(units) 14.00124456

Or Say 14
Recommended SP Cp.u.+VCp.u. 37.95
 produced boxing
ement to consider By Using Goal Seek
of leather has gone Present New
ave increased 25% Selling Price (Sp.u.) 28 37.95
increased 30%, or Direct Material 3.3 6.45
labour.
e to break-even at the Direct Labour 3.5 4.5
Variable Overheads 10 13
Variable Cost per pair 16.8 23.95
Contribution (Cp.u.) 11.2 14
Units 1607.1428571 1607.1428571
Total Contribution 18000 22500
Fixed Cost 18000 22500
Profit 0 0
P/v ratio 40 36.890645586
 Alternate Solution
P5. (Example 10. Page 6.27)ABC Ltd. is working at 75% of its capacity. The following are the details
of its operations: Particulars
Output (units) 15,000
Sales value Rs. 30,00,000 Sales(units)
Material cost Rs. 6,00,000 Selling Price(Rs.)
Wages Rs. 4,50,000 Variable Cost:
Variable expenses Rs. 3,00,000 Material cost
Semi-variable expenses Rs. 1,50,000
Fixed costs Rs. 7,50,000 Wages
The company has received an export order for 2,000 units at a much lower price of Rs. 120 per unit. Variable exps
The semi-variable expenses will increase byRs. 10,000 for additional production and sales. The Semi-variable exps.
export order will require a special packing cost of Rs. 10,000 to be incurred for its completion. (Variable)
Management wants to reject the offer because the export price is much below the domestic price.
You are requested by the management to give your advice. Total Variable Cost
Contribution
Fixed Cost:
Fixed Cost
Semi-variable exps.(Fixed)

Special Packing Cost

Solution Statement of Marginal Cost, Contribution and Profit Total Fixed Cost
Profit
Particu;lars Domestic Sale Export Order Total
Sales(units) 15000 2000 17000
Selling Price(Rs.) 200 120
Sales(Rs.) 3000000 240000 3240000
Variable Cost: Cost p.u
Material cost 40 600000 80000 680000
Wages 30 450000 60000 510000
Variable exps 20 300000 40000 340000
Semi-variable exps.(Variable) 5 75000 10000 85000
Total Variable Cost 95 1425000 190000 1615000
Contribution 1575000 50000 1625000
Fixed Cost:
Fixed Cost 750000 0 750000
Semi-variable exps.(Fixed) 75000 0 75000
Special Packing Cost 0 10000 10000
Total Fixed Cost 825000 10000 835000
Profit 750000 40000 790000

Export order Should be accepted as it increases overall contribution by Rs.50000 and Decision Yes
Profit by Rs. 40000.
 Domestic Sale Export Order
Per Unit Total Per Unit Total
15000 2000
200 3000000 120

40 600000 40
30 450000 30
20 300000 20

5 75000 5
95 1425000 95
105 1575000 25 50000

750000

75000
0 10000
825000 10000
750000 40000
 Alternate Solution based on SOLVER when
P6. (Illustration 17 Modified. Page 6.49) (Key Factor) JAL Ltd. can produce three different products from
the same raw material using the same production facilities. The relevant details are as follows: MAXIMUM DEMAND
Particulars X Y Z
Maximum market demand (units) 6,000 4,000 3,000 Selling price (SP)
Selling price per unit (Rs. ) 250 200 400 Raw Material %
Raw material as % of sales value 80% 60% 75% Raw Material per unit (Rs.)
Labour cost per unit ( Rs.) 24 40 32 Raw Material per unit (kg) @ Rs20
Overhead Rate is Rs.10 per hour of which 60% is fixed. Raw material available is @ Rs.20 per kg and
labour hours @ Rs.16 per hour. Labour per unit (Rs.)
Required: Find out the Optimum Product Mix and determine the Profit at the selected product Mix if a) Labour Hours per unit@ Rs 16
Raw material is limted to 100000 kg, b) If Labour hours are limited to 18,400 hours. Variable Overheads @ Rs 4 (10X0.4
Total Variable Cost
Contribution per unit
Units
Total Contribution
Solution: Statement of Contribution Fixed Ovrheads Cost
Product X Y Z Profit
Expected Demand (units) 6000 4000 3000
Raw material cost as % of Sales
80% 60% 75%
Value
Rs. Rs. Rs. When Raw materal is Key Factor
Selling price (SP) 250 200 400
Variable cost per unit:
Direct material @Rs.20 p. kg. 200 120 300
Direct labour @ Rs 16 per hour 24 40 32
Variable overheads 6 10 8
Total variable cost per unit 230 170 340
Contribution per unit 20 30 60
Material required p.u. (Kg.) 10 6 15
Contribution per Kg. of material 2 5 4
Ranking (Contribution per Kg. of
material ) III I II
Labour hours required per unit
(Labour cost / Rs.16) 1.5 2.5 2
Contribution per labour hour 13.33333333333 12 30
Ranking (based on contribution per II III I
labour hour)
Fixed Cost 36000 24000 18000 78000
(i) When raw material is key factor : Total raw material Available = 1,00,000
kg. Optimum Product Mix will be

Ranking of products( as per Units Material Total material

Contribution per Kg. of material ) required p.u. consumption
(Kg.) (Kg.)

Y 4000 6 24000
Z 3000 15 45000
X 3100 10 31000
Total 100000

Statement of Contribution and Profit

Product Units Cp.u (Rs.) Total(Rs.)
Y 4000 30 120000
Z 3000 60 180000
X 3100 20 62000
Total Contribution 362000
Less; Fixed Cost 78000
Profit 284000
(ii) When labour time is key factor Labour hours Available = 18,400; Optimum
Product Mix will be:

Ranking of products( as per Units Labour hours Total labour

Contribution per labour hour ) required p.u. hours
Z 3000 2 consumed6000
X 6000 1.5 9000
Y 1360 2.5 3400
Total 18400

Statement of Contribution and Profit

Product Units Cp.u (Rs.) Total(Rs.)
Z 3000 60 180000
X 6000 20 120000
Y 1360 30 40800
Total Contribution 340800
Less; Fixed Cost 78000
Profit 262800
on based on SOLVER when Raw Material is Key Factor
X Y Z
6000 4000 3000
250 200 400
0.8 0.6 0.75
200 120 300
10 6 15
24 40 32
1.5 2.5 2
6 10 8
230 170 340
20 30 60
3100 4000 3000 362000
62000 120000 180000
36000 24000 18000 78000
284000

10X+6Y+15Z<=100000
X Y Z LHV RHV
1 0 0 3100 <= 6000
0 1 0 4000 <= 4000
0 0 1 3000 <= 3000
10 6 15 100000 <= 100000
 Alternate Solution based on SOLVER when Labour hour is Key Factor
X Y Z
MAXIMUM DEMAND 6000 4000 3000
Selling price (SP) 250 200 400
Raw Material % 0.8 0.6 0.75
Raw Material per unit (Rs.) 200 120 300
Raw Material per unit (kg) @ Rs20 10 6 15
Labour per unit (Rs.) 24 40 32
Labour Hours per unit@ Rs 16 1.5 2.5 2
Variable Overheads @ Rs 4 (10X0.4) 6 10 8
Total Variable Cost 230 170 340
Contribution per unit 20 30 60
Units 6000 1360 3000 340800
Total Contribution 120000 40800 180000
Fixed Ovrheads Cost 36000 24000 18000
Profit 262800

When Labour hour is Key Factor 1.5X+2.5Y+2Z<=18400

X Y Z LHV RHV
1 0 0 6000 <= 6000
0 1 0 1360 <= 4000
0 0 1 3000 <= 3000
1.5 2.5 2 18400 <= 18400
 P7. (Illustration 34. Page6.69) (Export Order) SM Ltd. has just received an export order for its product which
will require use of 50% of the factory’s total capacity, which is estimated at 5,00,000 units.The condition of
export order is that it has to be accepted in full only The factory is currently operating at 60% level to meet
the demand of domestic market where sale price per unit is Rs. 6. The export offer is at 4.70 per unit, which
is less than the total cost of current production per unit as follows:
Direct Material Rs.2.00: Direct Labour Rs.1.50; Variable Expenses Rs.0.50; Fixed overhead
Rs. 1.00; Total cost Rs.5.00
The company has the following options:
(i) Accept the export order and cut back domestic sales as necessary.
(ii) Remove the capacity constraint by installing necessary balancing equipment and also by workingovertime
to meet both domestic and export demand. This decision will increase fixed overhead by Rs.20,000 and
additional cost for overtime work will be for Rs. 35,000.
You are required to prepare a statement of costs & profits under each of above two options and advise the
management suitably.

Solution (i) Statement of Contribution and Profit under Option(i) (Rs.)

Sales 2675000
Less: Variable Cost 2000000
Contribution 675000
Less: Fixed Cost 300000
Profit 375000
Statement of Contribution and Profit under Option(ii) (Rs.)
Sales 2975000
Less: Variable Cost 2235000
Contribution 740000
Less: Fixed Cost 320000
Profit 420000
 P8 (Illustration 35. Page 6.70) (Export Order ) X Ltd. annually manufactures 10,000 units of a product at a cost of Rs.
4 per unit and sells these in the home market at a selling price ofRs. 4.25 per unit. In the next year, there is fall in
demand in home market which can absorb 10,000 units only at price of Rs.3.72 per unit. The total cost of
10,000 units is as follows: Materials Rs.15,000
Wages Rs. 11,000
Fixed overheads Rs. 8,000
Variable overheads Rs. 6,000
The foreign market is explored and it is found that this market can consume 20,000 units of the product at a price of
Rs. 3.55 per unit. (Assume that the company has sufficient plant capacity to produce additional output). It is also
discovered that fixed overheads will increase by 10% for additional output above initial output of 10,000 units. Is it
worthwhile to try to capture the foreign market? Give reasons

Solution Comparative Statement of Profitability

Existing Proposed
Domestic Export Total
Sales (units) 10000 10000 20000 30000
Rs. Rs. Rs. Rs.
Selling Price 4.25 3.72 3.55
Sales 42500 37200 71000 108200
Variable Cost:
Material Cost 15000 15000 30000 45000
Labour Cost 11000 11000 22000 33000
Variable Overheads 6000 6000 12000 18000
Total Variable Cost 32000 32000 64000 96000
Contribution 10500 5200 7000 12200
Less: Fixed Cost 8000 8000 800 8800
Profir 2500 -2800 6200 3400

Recommendation: Yes, it is worthwhile to try to enter the foreign market as it will give a net
profit of Rs.6,200. Next year, this will help the firm to cover a loss of Rs. 2,800 from domestic
market and earn an overall net profit of Rs.3,400 (Rs. 6,200 – Rs. 2,800).
Recommendation: Yes, it is worthwhile to try to enter the foreign market as it will give a net
profit of Rs.6,200. Next year, this will help the firm to cover a loss of Rs. 2,800 from domestic
market and earn an overall net profit of Rs.3,400 (Rs. 6,200 – Rs. 2,800).
 P9 .(Illustration 38. Page (Export Order Pricing using Differential Costing Approach) A company is at present working at 90
percent of its capacity and producing 13,500 units per annum. It operates a flexible budgetary control system. The following
figures are obtained from its budget:
90%(Rs.) 100% (Rs.)
Sales 15,00,000 16,00,000
Fixed expenses 3,00,500 3,00,6,00
Semi-fixed expenses 97,500 1,00,500
Variable expenses 1,45,000 1,49,500
Units made 13,500 15,000
Labour and material cost per unit are constant under present conditions. Profit margin is 10 percent.
(a) You are required to determine the differential cost of producing 1,500 units by increasing capacity to100 percent.
(b) What would you recommend for an export price for these 1,500 units taking into account the overseasprices are much
lower than indigenous prices?

Solution: Working: Computation of Material and Labour Cost using information at 90% Capacity
Rs.
Sales 1500000
Less Profit 150000
Total Cost 1350000
Less: All cost other than material & Labour
Fixed Expenses 300500
Semi-fixed expenses 97500
Variable expenses 145000 543000
Material & Labour 807000

Statement Showing Differential Cost of 1,500 Units

Material & Labour 89666.67
Fixed Expenses 100
Semi-fixed expenses 3000
Variable expenses 4500
Differential Cost 97266.67
Minimum Export Price 64.84444
Alternate Solution:
Segregation of Semi Fixed Expenses
90% 100% per unit
Units 13500 15000
Semi Fix 97500 100500
Variable component 2 #NAME?
Fixed Component 70500 #NAME?

Segregation of Variable Expenses

90% 100% per unit
Units 13500 15000
Semi Fix 145000 149500
Variable component 3
Fixed Component 104500

Segregation of Fixed Expenses

90% 100% per unit
Units 13500 15000
Semi Fix 300500 300600
Variable component 0.066667
Fixed Component 299600

Material and Labour Cost per unit 59.77778

Total Variable Cost per unit 64.84444

Minimum Price to be quoted
 P10. (Illustration 46.Page 6.82) (Differential Costing) A company has a capacity of producing 50,000 units
of a product in a month. The sales department reports that the following schedule of selling prices is
possible:
Volume of sales (% capacity) Selling price per unit
50% 2.00
60% 1.90
70% 1.85
80% 1.80
90% 1.70
100% 1.60
Total fixed cost at 100% capacity is Rs.20,000 per month and variable cost per unit is Rs.1 per unit.
Prepare a statement showing total and differential costs.

Solution: Statement of Incremental Revenue (IR), Differential Cost (DC) and Incremental Profit (IP)

Capacity(%) Output(units) SP(Rs.) Sales(Rs.) IR (Rs.) VC(Rs.) FC(Rs.) TC(Rs.) DC(Rs.) IP(Rs.) profit
50% 25000 2 50000 25000 20000 45000 5000
60% 30000 1.9 57000 7000 30000 20000 50000 5000 2000 7000
70% 35000 1.85 64750 7750 35000 20000 55000 5000 2750 9750
80% 40000 1.8 72000 7250 40000 20000 60000 5000 2250 12000
90% 45000 1.7 76500 4500 45000 20000 65000 5000 -500 11500
100% 50000 1.6 80000 3500 50000 20000 70000 5000 -1500 10000
Total Capacity 50000
Capacity 100%
Units 50000
Selling Price 1.6
Less: VC 1
Contribution 0.6
Total Contribution 30000
Less: FC 20000
Profit 10000
P/V Ratio #REF!
BEP in Units #REF!
BE Sales #REF!
M/S #REF!
Scenario Summary
Current Values: 50 percent 60 percent 70 percent 80 percent 90 percent 100 percent
Changing Cells:
Capacity 100% 50% 60% 70% 80% 90% 100%
Selling_Price 1.6 2 1.9 1.85 1.8 1.7 1.6
Result Cells:
Total_Capacity 50000 50000 50000 50000 50000 50000 50000
Capacity 100% 50% 60% 70% 80% 90% 100%
Units 50000 25000 30000 35000 40000 45000 50000
Selling_Price 1.6 2 1.9 1.85 1.8 1.7 1.6
Less_VC 1 1 1 1 1 1 1
Contribution 0.6 1 0.9 0.85 0.8 0.7 0.6
Total_Contribution 30000 25000 27000 29750 32000 31500 30000
Less_FC 20000 20000 20000 20000 20000 20000 20000
Profit 10000 5000 7000 9750 12000 11500 10000
PV_Ratio 0.375 0.5 0.4736842105 0.4594594595 0.4444444444 0.4117647059 0.375
BEP 33333.333333 20000 22222.222222 23529.411765 25000 28571.428571 33333.333333
BE_Sales 53333.333333 40000 42222.222222 43529.411765 45000 48571.428571 53333.333333
Margin_of_Safety 26666.666667 10000 14777.777778 21220.588235 27000 27928.571429 26666.666667
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in grey.
Simple Differential

Selling Price 22 SP ranges between 15 to 50

Less: VC 8.8 VC is 40percent of SP

Contribution 13.2

Units 14998 Sales may range between 10000 to 30000 units

Total Contribution 197973.6

Less: FC 150000

Profit 47973.6