Question

Weber Manufacturing has been manufacturing its own locks for its security doors The following is the current cost for manufacturing the locks Direct Materials Direct Labor Manufacturing Overhead - Variable Manufacturing Overhead - Fixed Unit Production $4.00 per unit $5.00 per unit $3.00 per unit $45,000 30,000 Supplier Purchase Price: Avoided Fixed Costs if locks are purchased Opportunity Costs $12.75 per unit $10,000 30 Instructions: (a) Complete the incremental analysis (in total dollars) below for the decision to make or buy the locks. Use cell referencing in all of the blue cell already completed for you. ray Effect on Net Income Make Buy (Increase + or Decrease -) if firm Buys' the locks LLLLLLLLLS 0 Direct Materials Durect Labor Manufacturing Overhead - Variable Manufacturing Overhead - Fixed Opportunity Costs Supplier Purchase Price: Total annual cost 0 0 LORES ol (b) Create an If statement (in cell G30) to show whether Weber Manufacturing should purchase the locks from the supplier based on the Total Annual Cost calculated in cell H27 in part (a). Cell G30 should read either "Make" or "Buy." (Complete the incremental analysis below to show the impact if Weber Manufacturing purchased the locks from the supplier and then could earn the following rental income from the released manufacturing space. Use cell referencing in all of the blue cells; gray cells are already completed for you. Rental Income (Opportunity Cost) $30,000

Answer 1

B12 X fox (a) ДА В DE direct materials direct labor manufacturing overhead-variable manufacturing overhead-fixed unit production $ 4.00 $ 5.00 $ 3.00 $ 45,000 30,000 per unit per unit per unit per unit supplier purchase price $ 12.75 avoidable fixed costs if locks are purchased $10,000 opportunity costs $0 (a) make buy direct materials direct labor manufacturing overhead-variable manufacturing overhead-fixed opportunity costs supplier purchase price total annual cost $ 120,000 $ 150,000 $ 90,000 $ 45,000 $0 $0 $ 405,000 $0 $0 $0 $ 35,000 $0 $ 382,500 $ 417,500 effect on net income if firm buys the locks $ 120,000 $ 150,000 $ 90,000 $ 10,000 SO $ 382,500 $ 12,500 (b) whether company should make or buy? Make rental income (opportunity cost) $ 30,000 make buy total annual cost opportunity costs tota revised cost $ 405,000 $ 30,000 $ 435,000 $ 417,500 $0 $ 417,500 effect on net income if firm buys the locks -$ 12,500 $ 30,000 $ 17,500 (d) whether company should make or buy? Buy |

for formulas and calculations, refer to the image below -

B12 X fox (a) direct materials direct labor manufacturing overhead-variable manufacturing overhead-fixed unit production per unit per unit per unit 45000 30000 per unit supplier purchase price avoidable fixed costs if locks are purchased opportunity costs 12.75 10000 make buy direct materials direct labor manufacturing overhead-variable manufacturing overhead-fixed opportunity costs supplier purchase price total annual cost |=D2 D6 =D3* D6 =D4D6 =D5 effect on net income if firm buys the locks =D13-E13 =D14-E14 =D15-E15 =D16-E16 lo =D5-09 lo =D8*D6 =D18-E18 SUM(E13:E18) -SUM(F13:F18) =SUM(D13:D18) (b) whether company should make or buy? |=IF(F19>0,"Buy", "Make") rental income (opportunity cost) 30000 make total annual cost opportunity costs tota revised cost =D19 =D23 =SUM(D26:D27) buy effect on net income if firm buys the locks =E19 =D26-E26 =D27-E27 | SUM(E26:E27) =D28-E28 (d) whether company should make or buy? =IF(F28>0, "Buy", "Make")

In case you have any query, kindly ask in comments.