SCM $453-$ HOMEWORK #$5$

NAME: $q,$

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:

$\backslash$table$[[$WEEKLY DEMAND FORECAST $($in units$),],[$Shade$,1,2,3,4,5,6,7,8,9,10],[\backslash$table$[[$Blonde$],[8$A$]],1500,1450,1550,1650,2000,1900,1800,1650,1550,1450],[\backslash$table$[[$Brown$],[6$B$]],1110,1140,1150,1160,1160,1180,1190,1210,1160,1140],[\backslash$table$[[$Black$],[2$C$]],1300,1350,1375,1450,1500,1450,1375,1350,1300,1300]]$

It takes one week to produce the coloring, with a production run lot size of $250$ units for Blonde $8A,50$ units for Brown $6$B$,$ and $100$ for Black $2$C$.$ Safety stock requirements are $50,10,$ and $25$ respectively.

There are currently $1600$ Blonde $8$A 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\mathrm{.}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.