Question

Prepare a make or buy analysis. ? X - Sign In FILE Prepare a make or buy analysis - Excel HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calibri -11 TA À B I U - - A - Alignment Number Conditional Format as Cell Formatting Table Styles Font Styles Paste Cells Editing Clipboard A1 V : * fic of its own component parts. A B C D E F G Hн г 1 Alanco, Inc. manufactures a variety of products and is currently maunfacturing all of its own component parts. 2 An outside supplier has offered to sell one of those components to Alanco. To evaluate this offer, the following 3 information has been gathered relating to the cost of producing the component internally: 6 Direct materials Direct labor Variable manufacturing overhead Fixed manufacturing overhead, traceable* Fixed manufacturing overhead, common but allocated Total cost 4.00 6.00 2.00 5.00 8.00 25.00 8 10 Ś 12 Supplier price S 21.00
Can you post the answers as a formula?

Answer 1

T B C D E F G 5 Direct Material 6 Direct Labor 7 Variable Manufacturing Overhead 8 Fixed Manufacturing overhead, traceble 9 Fixed Manufacturing overhead, common 10 Total Costs 11 12 Supplier price 13 14 Units used per year 15 4.00 6.00 2.00 5.00 8.00 25.00 $ $ 21.00 12,000 16 17 Dpereciation of equipment 18 Supervisior Salary 30% 70% 19 Per Unit Differential Cost Make Buy 12,000 Units Make Buy $ 21.00 $ 2,52,000 $ 4.00 6.00 26 Cost of Purchasing 27 Direct Material 28 Direct Labor 29 Variable Manufacturing Overhead 30 Fixed Manufacturing overhead, traceble 31 Fixed Manufacturing overhead, common 32 Total Costs 33 34 48,000 72,000 24,000 42,000 2.00 3.50 0.00 15.50 $ $ 1,86,000 Alanco Should Make the component

40 Note : It is assumed that supervisior Salary can be avoided, therefore calculation has been made accordingly for Fixed Manufacturing overhead, traceble. 41

Formula Bar,

5 Direct Material 6 Direct Labor 7 Variable Manufacturing Over 8 Fixed Manufacturing overhea 9 Fixed Manufacturing overhea 10 Total Costs =SUM(G5:49) 21 12 Supplier price 13 14 Units used per year 12000 15 16 17 Dpereciation of equipment 18 Supervisior Salary 19 0.3 0.7 12,000 Units Per Unit Differential Cost Make Buy Make Buy =F12 =F12*F14 24 25 26 Cost of Purchasing 27 Direct Material 28 Direct Labor 29 Variable Manufacturing Over 30 Fixed Manufacturing overhea 31 Fixed Manufacturing overhea 32 Total Costs 33 34 =G5 =G6 =G7 =G8*F18 =E27*$F$14 =E28*$F$14 =E29*$F$14 =E30*$F$14 =E31*$F$14 =SUM(G27:G31) =SUM(E27:E31) 35 Alanco Should =IF(E32>=F26,"Buy", "Make") the component 37