Excel template below:

 

Jody wishes to use the forecast just developed to help make an Aggregate Production Plan that will then become a Master Production Schedule and drive a Materials Requirements Plan. Jody knows that weekly demand for the gift packs is split evenly between the two samplers (i.c., 50% of demand is Grunge and 50% Deep South). In addition, the following weekly breakdown, in percentage of quarterly forecast amount, has been developed. Help Jody develop an Master Production Schedule for quarter 1 (i.e., figure out how much they will need to produce in which weeks (1-12). Master Production Schedule as % of Sales Forecast Item NW Grunge Sampler Deep South Hooch Sampler 1 0% 0% Item Northwest Grunge Sampler Deep South Hooch Sampler Walla Walla Mud Mt. Hood Dredge Tacoma Terror Tuscaloosa Tornado Meridian. Mothball Augusta Augsbarge 2 5% Labels Boxes 0% 3 15% 20% 0% On Hand 9 15 250 301 350 15 Given the following bill of materials, master production schedule, and inventory master file, generate MRP matrices for each item and identify orders that are past due. If any orders are past due give a recommendation on what course of action to take. If past due, Jody can subcontract the samplers themselves but it costs 4 times the unit cost of each sampler (e.g., subcontract 1 unit NW Grunge = 4*$6). Lower level items can be expedited if need be but the cost is twice as much as the unit cost of the item (e.g., expedite 1 unit Walla Walla Mud=2*$1). Item Master File 250 4 5% 5 257 611 2 Lead Time 1 1 2 1 1 5 10% 2 1 10% 2 2 Weeks in Quarter 6 5% Text 0% 20% 25% Lot Size 1 1 10 75 40 I 7 8 10% 25% 20 40 100 91 $6.00 Unit Manufacturing /Purchasing Cost $6.00 $1.00 $1.00 $0.50 $0.50 9 10% $0.50 $0.50 $0.01 $0.10 10 10% 5% 15% 5% Scheduled Receipts 0 0 0 0 0 0 0 11 0% 0 0 0 12 5% 0% Finance has asked for a preliminary report on how much capital will be needed to purchase the units needed to complete these orders. Using the MRP matrices and the notes provided, calculate the capital costs per period and total for the quarter associated with the schedule you have constructed. There are no capital costs for finished samplers. Inventory holding costs are approximately the same for each lower level item and run about $0.50 per unit per period. There are no inventory costs for the final samplers themselves. Jody also needs to estimate the costs incurred from holding inventory over the quarter.