Please fill out the excel tables using the information provided.

A well-known, beauty products manufacturer produces and sells a line of premium hair coloring in twenty-two shades of brown, blonde, red and black. They all require the same basic chemicals however. The purchase plan for the three most popular shades of coloring must be determined for the next ten weeks. Marketing forecasts are given as follows: WEEKLY DEMAND FORECAST (in units) Shade 1 2 3 4 5 6 7 8 10 1500 1450 1550 1650 2000 1900 1800 1650 1550 1450 Blonde 8A 1110 1140 1150 1160 1160 1180 1190 1210 1160 1140 Brown 6B 1300 1350 1375 1450 1500 1450 1375 1350 1300 1300 Black 2C It takes one week to produce the coloring, with a production run lot size of 250 units for Blonde 8A, 50 units for Brown 6B, and 100 for Black 2C. Safety stock requirements are 50, 10, and 25 respectively. There are currently 1600 Blonde 8A units on hand. Additionally, there are currently 1225 Brown 6B units and 1325 Black 2C units on hand. According to the production planners, all other ingredients for the coloring are readily available and should cause no delay in producing the finished product, with the exception of the common oxidizing agent, Volume 20 Hydrogen Peroxide and the coated aluminum tubes used for packaging. Each unit requires 4 ounces of Hydrogen Peroxide. How many gallons of Volume 20 Hydrogen Peroxide do you need each week, knowing that one ounce equals 0.0078125 gallons (round up to the nearest whole gallon)? This product is purchased in 55 gallon drums. With 178 gallons remaining in stock, and a one week lead time, complete an MRP table for Hydrogen Peroxide as well. Safety stock requirement is 25 gallons. Finally, all three shades use the same coated aluminum needed for the packaging tubes, which comes in 500 foot coils. The aluminum coils are already cut to the exact width needed for making the tubes. Each unit requires 6 inches of coated aluminum, so how many feet of aluminum do you need each week? When do we need to order this, given a one week lead time, 2475 feet currently in stock, and no safety stock requirement? Complete an MRP table for coated aluminum to answer this question. Complete the five MRP tables using the template on WesternOnline to answer these questions and submit via Dropbox. I encourage you to use formulas in your spreadsheet to simplify the process for yourselves. In case you aren't familiar with them, use the CEILING function for accommodating the lot size, as well as the ROUNDUP function for the materials, in addition to the basic mathematical formulas. NOW Shade: Blonde 8A Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 1 1500 0 2 1450 0 3 1550 0 4 1650 0 5 2000 0 6 1900 0 7 1800 0 8 1650 D 9 1550 0 10 1450 0 LOT SIZE LEAD TIME S.S. NOW 1 1100 0 2 1400) 0 SO 160 15410 Shade: Brown 6B Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 5 1600 1600) 6 18181 0 0 7 10K 0 21.10 0 9 1600) D 10 1400 0 LOT SIZE LEAD TIME S.S. Pag 6 3000 2 3500 0 7 3750 Shade: Black 2C Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 1500) 5 5000 0 1500 0 8 3500 0 9 3000 0 10 3000 0 0 LOT SIZE LEAD TIME S.S. UNITS ORDERED PER WEEK 4 5 6 1 2 3 7 8 9 10 Blonde SA Brown 6B Black 2 TOTAL Planned Orders UNITS ORDERED PER WEEK 10 Blonde SA Brown GB Black 2 TOTAL CONVERTED TO OUNCES OF HYDROGEN PEROXIDE PER WEEK CONVERTED TO GALLONS OF HYDROGEN PEROXIDE PER WEEK actual rounded NOW 1 9 10 Hydrogen peroxide (in gal) Projected Demand Scheduled Receipts Quantity On Hand Planned Orders Page 2 LOT SIZE LEAD TIME SS Page 4 UNITS CONVERTED TO FEET OF COATED ALUMINUM PER WEEK 5 NOW 1 10 Coated Aluminum in feet) Projected Demand Scheduled Receipts Quantity On Hand Planned Orders LOT SIZE LEAD TIME S.S A well-known, beauty products manufacturer produces and sells a line of premium hair coloring in twenty-two shades of brown, blonde, red and black. They all require the same basic chemicals however. The purchase plan for the three most popular shades of coloring must be determined for the next ten weeks. Marketing forecasts are given as follows: WEEKLY DEMAND FORECAST (in units) Shade 1 2 3 4 5 6 7 8 10 1500 1450 1550 1650 2000 1900 1800 1650 1550 1450 Blonde 8A 1110 1140 1150 1160 1160 1180 1190 1210 1160 1140 Brown 6B 1300 1350 1375 1450 1500 1450 1375 1350 1300 1300 Black 2C It takes one week to produce the coloring, with a production run lot size of 250 units for Blonde 8A, 50 units for Brown 6B, and 100 for Black 2C. Safety stock requirements are 50, 10, and 25 respectively. There are currently 1600 Blonde 8A units on hand. Additionally, there are currently 1225 Brown 6B units and 1325 Black 2C units on hand. According to the production planners, all other ingredients for the coloring are readily available and should cause no delay in producing the finished product, with the exception of the common oxidizing agent, Volume 20 Hydrogen Peroxide and the coated aluminum tubes used for packaging. Each unit requires 4 ounces of Hydrogen Peroxide. How many gallons of Volume 20 Hydrogen Peroxide do you need each week, knowing that one ounce equals 0.0078125 gallons (round up to the nearest whole gallon)? This product is purchased in 55 gallon drums. With 178 gallons remaining in stock, and a one week lead time, complete an MRP table for Hydrogen Peroxide as well. Safety stock requirement is 25 gallons. Finally, all three shades use the same coated aluminum needed for the packaging tubes, which comes in 500 foot coils. The aluminum coils are already cut to the exact width needed for making the tubes. Each unit requires 6 inches of coated aluminum, so how many feet of aluminum do you need each week? When do we need to order this, given a one week lead time, 2475 feet currently in stock, and no safety stock requirement? Complete an MRP table for coated aluminum to answer this question. Complete the five MRP tables using the template on WesternOnline to answer these questions and submit via Dropbox. I encourage you to use formulas in your spreadsheet to simplify the process for yourselves. In case you aren't familiar with them, use the CEILING function for accommodating the lot size, as well as the ROUNDUP function for the materials, in addition to the basic mathematical formulas. NOW Shade: Blonde 8A Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 1 1500 0 2 1450 0 3 1550 0 4 1650 0 5 2000 0 6 1900 0 7 1800 0 8 1650 D 9 1550 0 10 1450 0 LOT SIZE LEAD TIME S.S. NOW 1 1100 0 2 1400) 0 SO 160 15410 Shade: Brown 6B Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 5 1600 1600) 6 18181 0 0 7 10K 0 21.10 0 9 1600) D 10 1400 0 LOT SIZE LEAD TIME S.S. Pag 6 3000 2 3500 0 7 3750 Shade: Black 2C Projected Demand Scheduled Receipts Quantity On Hand Planned Orders 1500) 5 5000 0 1500 0 8 3500 0 9 3000 0 10 3000 0 0 LOT SIZE LEAD TIME S.S. UNITS ORDERED PER WEEK 4 5 6 1 2 3 7 8 9 10 Blonde SA Brown 6B Black 2 TOTAL Planned Orders UNITS ORDERED PER WEEK 10 Blonde SA Brown GB Black 2 TOTAL CONVERTED TO OUNCES OF HYDROGEN PEROXIDE PER WEEK CONVERTED TO GALLONS OF HYDROGEN PEROXIDE PER WEEK actual rounded NOW 1 9 10 Hydrogen peroxide (in gal) Projected Demand Scheduled Receipts Quantity On Hand Planned Orders Page 2 LOT SIZE LEAD TIME SS Page 4 UNITS CONVERTED TO FEET OF COATED ALUMINUM PER WEEK 5 NOW 1 10 Coated Aluminum in feet) Projected Demand Scheduled Receipts Quantity On Hand Planned Orders LOT SIZE LEAD TIME S.S