please use excel 10) You are hired as a consulting engineer to a toy manufacture
ID: 1714258 • Letter: P
Question
please use excel
10) You are hired as a consulting engineer to a toy manufacturer. The toys are made of wood (pine and hardwood) and you need to determine how many toys of different styles should be produced each day to maximize the profit. Fractional toys should be rounded to the nearest integer. The quantity of each toy cannot exceed the maximum sales for the toy. The maximum pine available is 245 board feet per day, the maximum hardwood available is 40 board feet per day, and the maximum production hours available are 90. The production hours required for one toy varies from 0.6 to 1.68 hours. The following table summarizes the requirement to produce one toy Pine Hardwood board feet/Toy board feet/Toy Hours/To Max Sales Selling Price Small Medium 2.7 3.4 3.1 0.25 0.35 0.55 0.9 0.6 0.8 1.04 1.68 50 30 20 15 $45.00 $61.00 $81.00 100.00 Fanc Daily availability and costs are summarized below Max Hours Labor Cost/Hour $ pine/board foot $ hardwood/board foot Avail Pine (bf Avail Hardwood (bf) 90 $15.78 $12.00 $25.00 245 40Explanation / Answer
Assuming all hardwoods and pines available are utilized, the total expense will be equal to sum of Labor cost for 90 hours and Total cost of pine/hardwood available.
For Hardwoods,
Total cost = Labor cost @ 15.78 for 90 hrs + 40 bf @ $ 25 / bf
= 15.78 *90 + 40 *25 = 1420.2 + 1000 = $ 2420.2
For Pine,
Total cost = Labor cost @ $ 15.78 for 90 hrs + 245 bf @ $ 12 / bf
= 15.78 *90 + 245 *12 = 1420.2 + 2940 = $ 4360.2
Total Revenue is equal to the sum of money obtained by selling different types of products.
So, Profit = Total Revenue – Total Expenses
To maximize the profit, we have to maximize the revenue so that all the materials available are utilized and maximum numbers of production hours is also utilized.
From the question, it is clear that of the 4 products, 3 can be manufactured to their individual max. Number, so the limiting factor is the 4th product. Therefore, we have 4 cases for each pine and hardwood to find the maximum profit and number of individuals’ items that have to be manufactured.
HARDWOOD ITEMS
So, for hardwood itmes, 50 small, 30 medium , 20 large and 7 fancy items should be produced with total profit of $ 3979.8 and 86.6 hrs of production.
PINE WOOD ITEMS
So, for pinewood itmes, 25 small, 30 medium , 20 large and 15 fancy items should be produced with total profit of $ 3643.6 and 84.9 hrs of production.
Maximizing profit for Hardwod items CASE 1 Considering Small, Medium and Large are manufactured to their max. sales number S.No Type Total No. Manufactured Total Board ft consumed Total Revenue Hrs consumed ( Maximum Sales) (Board ft/toy * Total No.) (Total No. * Selling price) (Total No. * hrs/toy) 1 Small 50 12.5 2250 30 2 Medium 30 10.5 1830 24 3 Large 20 11 1620 20.8 TOTAL 100 34 5700 74.8 Total Board ft available 40 Board ft available = Total -Consumed 6 Total no. of Fancy items 7 (Board ft available/ board ft/toy) Rounded to integer Total revenue from fancy items 700 Hrs taken = No*hrs/toy 11.8 Total Revenue 6400 Total production hrs 86.6 Profit = Revenue - Expenses 3979.8 CASE 2 Considering Small, Medium and Fancy are manufactured to their max. sales number S.No Type Total No. Manufactured Total Board ft consumed Total Revenue Hrs consumed ( Maximum Sales) (Board ft/toy * Total No.) (Total No. * Selling price) (Total No. * hrs/toy) 1 Small 50 12.5 2250 30 2 Medium 30 10.5 1350 24 3 Fancy 15 13.5 1500 25.2 TOTAL 95 36.5 5100 79.2 Total Board ft available 40 Board ft available = Total -Consumed 3.5 Total no. of Large items 6 (Board ft available/ board ft/toy) Rounded to integer Total revenue from Large items 486 Hrs taken = No*hrs/toy 6.6 Total Revenue 5586 Total production hrs 85.8 Profit = Revenue - Expenses 3165.8 CASE 3 Considering Small, Large and Fancy are manufactured to their max. sales number S.No Type Total No. Manufactured Total Board ft consumed Total Revenue Hrs consumed ( Maximum Sales) (Board ft/toy * Total No.) (Total No. * Selling price) (Total No. * hrs/toy) 1 Small 50 12.5 2250 30 2 Large 20 11 1620 20.8 3 Fancy 15 13.5 1500 25.2 TOTAL 85 37 5370 76 Total Board ft available 40 Board ft available = Total -Consumed 3 Total no. of Medium items 9 (Board ft available/ board ft/toy) Rounded to integer Total revenue from Medium items 523 Hrs taken = No*hrs/toy 6.9 Total Revenue 5893 Total production hrs 82.9 Profit = Revenue - Expenses 3472.7 CASE 4 Considering Medium, Large and Fancy are manufactured to their max. sales number S.No Type Total No. Manufactured Total Board ft consumed Total Revenue Hrs consumed ( Maximum Sales) (Board ft/toy * Total No.) (Total No. * Selling price) (Total No. * hrs/toy) 1 Medium 30 10.5 1350 24 2 Large 20 11 1620 20.8 3 Fancy 15 13.5 1500 25.2 TOTAL 65 35 4470 70 Total Board ft available 40 Board ft available = Total -Consumed 5 Total no. of small items 20 (Board ft available/ board ft/toy) Rounded to integer Total revenue from small items 900 Hrs taken = No*hrs/toy 12.0 Total Revenue 5370 Total production hrs 82.0 Profit = Revenue - Expenses 2949.8Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.