CRITICAL THINKING > Using Excel P25-33 Using Excel to calculate optimum product

Question

CRITICAL THINKING > Using Excel P25-33 Using Excel to calculate optimum product mix Download an Excel template for this problem online in MyAccountinglab or at http //www.pearsonhighered.com Horngren. Magnolia Company produces leather shoes in three models: Medina, Ballard, and Fremont. Currently, Magnolia is manufactur ing 4,000 pairs of Medina, 6,000 pairs of Ballard, and 1,500 pairs of Fremont during the year, for a total contribution margin of $2,242,500. However, some of the resources used in the manufacturing process are underutilized, leading the manager to believe that there could be an alternative product mix for shoes that would increase the total contribution margin. Magnolia can sell all shoes produced The current product mix is: Shoe Ballard 6,000 250 50 200 80% Total Contribution Margin Pairs of shoes Sales price per pair Variable costs per pair Contribution margin per pair Contribution margin ratio Medina 4,000 385 175 210 Fremont 1,500 180 45 135 75% $ 2,242,500

Explanation / Answer

this problem in solved based on theory of constraints.

The product ranking has to be done first to allocate the resources available, here the machine hours is limited and the product mix is based on the ranking.

Step 1-calculatation of contribution per machine hour

Contribution per machine hour

=Contribution per unit/time required per unit;

medina=210/.65hr=

Now based on the baove calculation, the resources has to be allocated to maximise the contribution.

Cutting time is limited and hence the product mix is based on the time available in cutting i.e.1800 hours.

as fremont cotribution per machine hour is least it is assumed not to produce and instead allocate the time to Medina.

Time required for Fremont in cutting section =1500 units*.20=300 hours.

Therefore, for the time available from fremont, Medina can produce from 300 hours additional units or pairs of 300/.15=2000 pairs

6000*.15=900

6000*.25=1500

6000*.25=1500

Ballard

based above product mix, revised contribution will be

To complete this problem in excel using sumproduct, use the following formula

use the table machin hours allocation table and allocate the resources as given above product mix.

using sumproduct =sumproduct((machine hours allocation)*select(b19:d19))

the result will be f19=1800;f20=2100;f21=2100

.

Product Medina Ballard Fremont Contribution per unit 210 200 135 time required 0.65 0.35 0.40

Contribution per machine hour

=Contribution per unit/time required per unit;

medina=210/.65hr=

227.43 116.6 89.10 Ranking I II III

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---