excel sheet needs the answers and the formulas

profilehwhlp9
MGT-655-RS-T3-InventoryMangement-Studentformulas1.xlsx
Home>Business & Finance homework help>Management homework help> excel sheet needs the answers and the formulas

A-Topic 3 Part 1&2

Engine Assembly Master Schedule
Week 1 2 3 4 5 6 7 8 9 10 11 12
Quantity 15 5 7 10 0 15 20 10 8 2 160
Gear box requirements
Week 1 2 3 4 5 6 7 8 9 10 11 12
Gross Requirements 15 5 7 10 0 15 20 10 0 8 2 16 Since Gear box 1 X Engine Assembly, use Master Engine Assembly Qty
Scheduled Receipts 5 From Problem
Projected Available Balance 2 2 0 0 0 0 0 0 0 0 0 0 Cell B10, Beg Balance of 17- Gross Requirement of 15 Cell C10 Carryover
Net Requirements 0 0 5 10 0 15 20 10 0 8 2 16 Net Req=Gross Req-Projected Available Balance from prior week
Planned Order Receipt 5 10 15 20 10 8 2 16 Same as row above
Planned Order Release 5 10 15 20 10 8 2 16
From E12 for 2 Weeks Lead Time
Input shaft requirements
Week 1 2 3 4 5 6 7 8 9 10 11 12
Gross Requirements 10 20 0 30 40 20 0 16 4 32 0 0 Since Input Shaft is 2 X Gear Box, then each week is 2 X Row 13
Scheduled Receipts 22 From Problem
Projected Available Balance 30 32 32 2 0 0 0 0 0 0 0 0 Cell B22, Beg Balance of 40- Gross requirement of 10 Week 5 and beyond 0 since no scheduled receipts
Net Requirements 0 0 0 0 38 20 0 16 4 32 Net Req=Gross Req-Projected Available Balance from prior week
Planned Order Receipt 38 20 16 4 32 Same as row above
Planned Order Release 38 20 16 4 32 Planned Order Receipt from 3 weeks in future
From F24 for 3 Weeks Lead Time
Part 2
Gear Box
Given Information Number of orders ( count cells with values for planned order release)
Setup per order= $90.00 Set-up Costs=# of Orders X Setup Costs
Inventory Carrying Cost per unit per period $2.00 (8*90)
Inventory (2+2)*Inventory Carrying Cost
Total $0.00
Input Shaft
Given Information
Setup per order= $45.00 Setup Costs=5 orders*45
Inventory Carrying Cost per unit per period $1.00 Inventory=(30+32+32+2)*1
Total $0.00
Total Cost $0.00

A-Topic 3 Part 3

Engine Assembly Master Schedule
Week 1 2 3 4 5 6 7 8 9 10 11 12
Quantity
Lead Time 2
Gear box requirements
Week 1 2 3 4 5 6 7 8 9 10 11 12
Gross Requirements 0 0 0 0 0 0 0 0 0 0 0 0 Since Gear box 1 X Engine Assembly, use Master Engine Assembly Qty
Scheduled Receipts 5 From Problem
Projected Available Balance 17 22 0 0 0 0 0 0 0 0 0 0 Cell B10, Beg Balance of 17- Gross Requirement of 15 Next cell, Gross Req-Scheduled Receipts
Net Requirements Net Req=Gross Req-Projected Available Balance from prior week
Planned Order Receipt Planned order receipt=projected available balance +net requirements
Planned Order Release Stagger Order Releases to reduce Costs
From E12 for 2 Weeks Lead Time
Input shaft requirements
Week 1 2 3 4 5 6 7 8 9 10 11 12
Gross Requirements 30 0 0 0 0 0 0 0 Since Input Shaft is 2 X Gear Box, then each week is 2 X Row 13
Scheduled Receipts 22 From Problem
Projected Available Balance 10 32 32 0 0 0 0 0 0 0 0 0 Cell B22, Beg Balance of 40- Gross requirement of 10 Week 4 and beyond 0 since no scheduled receipts
Net Requirements Net Req=Gross Req-Projected Available Balance from prior week
Planned Order Receipt
Planned Order Release Planned Order Receipt from 3 weeks in future
From F24 for 3 Weeks Lead Time
Part 2
Gear Box
Given Information Number of orders ( count cells with values for planned order release)
Setup per order= $90.00 Set-up Costs=# of Orders X Setup Costs
Inventory Carrying Cost per unit per period $2.00 3*90)
Inventory (88)*Inventory Carrying Cost sum of projected available balance
Total $0.00
Input Shaft
Given Information
Setup per order= $45.00 Setup Costs=2 orders*45
Inventory Carrying Cost per unit per period $1.00 Inventory=(74)*1
Total $0.00
Total Cost $0.00