Please use Excel

Design engineering has created a new item that is to be reviewed by procurement as to whether it should be made or bought. Engineering, sourcing, and planning have determined the following values to assist in the cost (US$) analysis: General Data Estimated annual usage = 25,000 units Inventory carrying cost = 22 % Purchase ordering costs = 145.50 Direct overhead % = 50 % Indirect overhead % = 50 % Depreciation rate = 6,500 (absorbed 25,000 units) Design rate = 25,000 (absorbed 25,000 units) To determine the best option for the new item, the planners are required to calculate the cost first for the outsourcing option and then for the insourcing option. The source with the lowest cost will provide a significant criterion for the sourcing option. Outsourcing Option The planner began by determining the outsourcing option. After reviewing several suppliers, one was selected as the probable supplier. The planner received the following cost information to assist in the outsource cost estimate. Shipping and handling = .18 Purchase price = 14.25 Setup charge= .005 General and Administration = .0963 Based on the information, assist the planner solve for the following purchasing costs: 1. Unit cost: 2. EOQ lot size: 3. Unit inventory carrying cost: 4. Total unit cost: 5. Total annual cost: Insourcing Option After discussing the new item cost elements with design engineering, the planner complied the following internal production costs. Direct costs (per unit): Materials = .68 Labor = .60 Setup = .051 Overhead = 0.30 Indirect Costs (unit) Labor = .10 General and Administration = 12.5 Based on the information, assist the planner solve for the following production costs: 1. Unit cost: 2. EOQ lot size: 3. Unit inventory carrying cost: 4. Total unit cost: 5. Total annual cost: Cost Summary Assist the planner to summarize the costs associated with both options. Solve for the following costs: 1. Cost savings per unit: 2. Total annual cost saving: Sourcing Option Selection 1. Based on the cost estimates, which source offers the better choice? 2. What other factors does the planner need to review before making the final decision? 2 3 General Data 4 Annual Usage 5 Inventory Carrying Cost 6 Purchase Ordering Cost 8 Insourcing Option/unit 9 Direct Costs 10 Materials 11 Labor 12 Setup 13 Overhead 14 15 Total 16 Total Unit Cost 17 Total Cost/Annual 19 Outsourcing Option/unit 20 Purchase Price 21 Shipping & Handling 22 Setup Charge 23 General & Admin 288852 24 26 27 29 Cost Decision 30 31 Total Total Unit Cost Total Cost/Annual Savings/Unit Total Annual Cost Savings 32 33 34 35 $ 6969696 $ $ $ $ $ $ $ $ 25,000 Direct Overhead % 22% Indirect Overhead % 145.50 Indirect Costs 0.68 Indirect Labor 0.60 General & Admin 0.0510 Depreciation 0.30 Design Overhead Inventory Costs 14.25 EOQ Lot Size 0.18 Annual Material 0.005 Annual Inv Carrying 0.0963 Unit Inv Carrying Cost Total 50% Depreciation Rate 50% Design Rate Inventory Costs 0.1 EOQ Lot Size 12.5 Annual Material Cost Annual Inv Carrying Cost Unit Inv Carrying Cost $ 6,500.00 $ 25,000.00