Please fill out the mix aggregate planning spreadsheet using the given information from figures 1&2.

Sneakernet is an internet-based company that produces three types of shoes: Casual, Athletic and Running. These shoes are sold directly to consumers and to retail stores. Your task is to plan out their production needs in the given time periods using aggregate planning, master production scheduling and material requirements planning. The aggregate plan will combine all three types of shoe for each quarter. A master production plan for each shoe needs to be created and material requirements planning needs to be done for the casual shoe. This project is worth 200 points, not including the bonus. Aggregate Planning A chase and level plan must be created for the project. The planning horizon is for two years, starting one year out. Each period in the aggregate plans represent one quarter. Aggregate by product for each quarter. The marketing department is planning to attend a series of fairs and conventions during periods 5 and 6, resulting in a projected spike in forecasted demand. 0 O Chase Plan Rules: The forecast demand must be met each quarter Overtime and subcontracting is authorized No backorders are permitted Level Plan Rules: o Regular production will be calculated and rounded up to the nearest whole number using the level production equation (do not just hide decimal places) o No overtime or subcontracting may be used A mixed plan is an optional assignment for 20 bonus points. To score the bonus points, the plan must meet the conditions for the Mix Aggregate Plan listed below. o 0 Mix Aggregate Plan Rules: Plan must be free of errors The cost of the plan must beat the cost provided (target cost to be provided on Canvas) At the end of the last period, all backlog must be cleared Employment level cannot ever go above the maximum number of workers as stated in the Aggregate Planning spreadsheet Master Production Schedule The production department in Sneakernet wants to plan out the next eight weeks for each line of shoe. They have received customer orders and have constructed forecasts to anticipate the demand by shoe line. Construct a master production schedule for each shoe line. Batch sizes for each shoe line will be given in the spreadsheet. Quarter Casual Athletic Running Total 1 8585 8810 8750 26145 2 9510 8875 8825 27210 Aggregate Demand 3 4 5 9905 12085 14480 9250 12615 16850 9355 11990 16900 28510 36690 48230 6 17195 17670 16150 51015 7 12225 14255 13525 40005 8 10505 12165 13175 35845 Description Maximum regular hours per worker per quarter Maximum overtime hours per worker per quarter Maximum number of workers Starting workforce Hiring cost per worker Firing cost per worker Regular time wage Overtime wage Subcontract Cost Back order cost Holding cost per pair of shoes per quarter Ending Inventory Periodo Value 450 100 85 55 1600 1000 20 30 su 55 55 25 23 3 30001 Component Box Unlaced Shoes Tissue Laces laces Shoe Body Assembly Rubber Sole Tongue Week Forecast Orders Casual Shoe Forecast and Customer Orders 2 2 3 4 5 6 673 747 913 999 654 715 555 284 88 45 1 612 600 7 859 Insole 8 952 Lining Semi-Finished Goods and Raw Material Inventory Records Unit of Measure Current Inventory Order Policy Lead Time Scheduled Receipts EACH 2585 FOQ = 2500 2 Weeks None PAIR 300 L4L 1 Week 1124 in Week 1 SHEET 1773 FOQ = 2000 1 1 Week None EACH LA 513 FOQ - 5000 1 Week Week None None PAIR 409 L4L 1 Week 1235 in Week 2 PAIR 0 L4L 2 Weeks 576 in Week 2 EACH CAN 184 L4L 404 14 -ween 1 Week None FAIN PAIR 49 2193 FOQ = 1000 2 Weeks None EACH 0 CACH L4L 2 Weeks 1870 in Week 2 PAIR 194 L4L 1 Week EACH FOQ = 20,000 2 Weeks 40,000 in Week 1 FACH EACH 1418 L4L, Min - 3000 1 Week None EACH 2157 FOQ - 5000 3 Weeks 2000 in Week 1 PAIR 1156 L4L 1 Week 1914 in Week 1 PAIR 1172 L4L 1 Week 1914 in Week 1 EACH 1191 L4L 1 Week 1914 in Week 1 1 EACH 8613 FOQ-5000 3 Weeks None INCH 12250 FOQ = 20000 1 Week 0 0 None 17840 Week Athletic Shoe Forecast and Customer Orders 2 3 3 4 5 6 1220 605 607 1011 1019 1154 641 175 118 1 1160 1184 Forecast lorders 7 833 82 8 8 1083 0 Shoe Body Kit Eyelet Padding Label Outer Body Kit Inner Body Kit Heal Stripe Logo Stitching 330 Aggregate Planning 7 8 None Master Production Schedule Week Forecast Orders 1 768 702 Running Shoe Forecast and Customer Orders 2 2 3 4 5 6 649 898 424 285 451 629 484 306 123 79 498 464 0 0 Materials Requirement Planning Current Inventory Order Policy Lead Time Finished Goods Inventory Records Casual Athletic 2258 2546 FOQ = 2000 FOQ = 2000 = 1 Week 2 Weeks Running 1557 FOQ = 1500 2 Weeks MIX AGGREGATE PLAN 1 2 3 4 5 6 7 8 Period Forecast Production Regular Time Overtime Subcontract Total Production Number of Workers Inventory Beginning Ending Average Backlog Labor Costs Regular Overtime Subcontract Hire Fire Total Labor Cost Inventory Costs Holding Cost Back Order Total Production Cost Total Cost