attached is a file that I am needing some assistance with

Managerial Accounting Exel Part - 50 points When completed, place back in drop box. Show all your work! Problem 1 - Make Buy Decision 10 Points DM DL Variable Overhead Fixed Overhead $ $ $ $ Cost to Make Widget 12.00 8.00 5.00 3.00 Total $ 28.00 We need 1,000 Widgets per year. We can buy the Widget for $27 each. Fixed overhead is allocated expense and none would be eliminated if we buy the Widget. A. List relevant costs per Widget for each alternative Make Widget Buy Widget B. Based on your numbers, would you make or buy the Widget? C. What would be the total difference to net income if you buy the 1,000 widgets instead of making them? D. What are some other non-financial factor to consider when buying over making the widget? Problem 2 - Special Order Decision (8 points) Assume the same data as in Problem 1 for the cost to make a Widget. What if we could sell the widgets we make for $50 to other customers. We receive a special order for 1,000 more widgets but that customer wants to just pay $30. It would not affect our current orders or our fixed costs and we have plenty of plant capacity. A. What are the total relative costs associated with the special order? B. Should we accept this special order at the selling price of $30 each? C. By accepting the special order of 1,000 widgets, our total net income would go up or down by how much? Problem 3 - Drop decision (8 points) We sell two products A Sales Revenue Less V.C. C.M. Less Direct F.C. Depreciation Salaries $ Segment Margin B 100 $ 20 $ 80 160 140 20 20 30 10 50 $30 ($40) A. Should product B be dropped if $10 of the salaries can be eliminated? Should Product B be dropped? 2. What if salaries would be completely eliminated due to layoff? Should Product B be dropped? Problem 4 - Constrained Resource (8 points) We make Part A and Part B. After finishing, each goes through the same glossing department. A C.M (per unit) Glossing hours $ B 240 $ 6 150 2 Max Capacity of Glossing is 6,000 hours. A. What is the CM per hour of glossing for each product A B. What is the optimal product mix? All hours should be used for product B as it gives the most CM per hour. C. What would the total dollar amount of CM be for all units? B Problem 5 - ARR (5 points) Equipment costs 100,000 and has a life of 10 years. Expected Cash Revenues are $200,000 per year. Expected Cash expenses are 170,000 per year A. What is depreciation expense per year? B. What is the ARR? Average net income / Investment (excluding depreciation) Problem 6 - NPV Competing Projects (11 points) TABLES are BELOW (or you can use tables in your text) Net Cash flows: Year Project A Equip. Project B Equip. 1 $ 90,000 $ 50,000 2 $ 100,000 $ 80,000 3 $ 60,000 ### 4 $ 50,000 $ 40,000 Life is 4 years and each equipment has an investment of $200,000 Discount rate is 10% Assume no salvage on equipment. A. What is the NPV of each project? Project Equipment A Year Cash flow Discount factor Cash flow Discount factor Total NPV Project Equipment B Year Total NPV B. Which project would you choose and why? C. Is there either project that should not be considered at all? Tables Present Value of $1 Periods 1 2 3 4 5 5% 0.95238 0.90703 0.86384 0.8227 0.78353 10% 0.90909 0.82645 0.75132 0.68301 0.62092 Present Value of an Annuity Periods 1 2 3 4 5 5% 0.95238 1.85941 2.72325 3.54595 4.32948 10% 0.90909 1.73554 2.48385 3.16986 3.79079 Differential cost to make aking them? by how much? Total $ 260 160 100 30 80 ($10) be dropped? be dropped? per year. No Salvage. per year NPV NPV