Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please help with the calculations in the blank, yellow cells. Calculations and steps need to be shown. Thank you! begin{tabular}{|c|c|c|c|c|c|c|c|} hline & 2018 & 2019
Please help with the calculations in the blank, yellow cells. Calculations and steps need to be shown. Thank you!
\begin{tabular}{|c|c|c|c|c|c|c|c|} \hline & 2018 & 2019 & 2020 & \multicolumn{2}{|c|}{ Sales Forecast 2021} & Cost per unit & $150 \\ \hline January & 790 & 853 & 929 & January & 1,068 & Inventory Holding Cost per unit & $22.50 \\ \hline February & 800 & 864 & 944 & February & 1,086 & & \\ \hline March & 1,300 & 1,404 & 1,544 & March & 1,776 & Z-score & \\ \hline April & 1,560 & 1,685 & 1,943 & April & 2,234 & Standard Deviation of Demand & 785.75 \\ \hline May & 1,620 & 1,750 & 2,469 & May & 2,839 & Safety Stock & \\ \hline June & 1,970 & 2,128 & 2,584 & June & 2,972 & & \\ \hline July & 2,140 & 2,311 & 2,539 & July & 2,920 & EOQ(Q) & \\ \hline August & 2,230 & 2,408 & 2,541 & August & 2,922 & Maximum Inventory, (Q+SS) & \\ \hline September & 1,560 & 1,685 & 2,426 & September & 2,790 & Average Inventory, (Q/2) & \\ \hline October & 1,280 & 1,382 & 1,573 & October & 1,809 & Number of Orders & \\ \hline November & 1,070 & 1,156 & 1,271 & November & 1,462 & Re-order Point & \\ \hline December & 940 & 1,015 & 976 & December & 1,122 & & \\ \hline \multirow[t]{3}{*}{ Total } & 17,260 & 18,641 & 21,740 & Total & 25,000 & Total Ordering cost & \\ \hline & & & & & & Inventory Carrying Cost & \\ \hline & & & & Data Point & & Safety Stock Carrying Cost & \\ \hline Surf Shops & & 300 & & Mean & 2,083 & & \\ \hline \multicolumn{2}{|l|}{ Service Level } & 96% & & St Dev & 785.75328 & cogs[ & \\ \hline Turnover & & 3 days & & \multicolumn{3}{|c|}{ z score formula = norms.s.inv } & \\ \hline Item & & \multicolumn{2}{|c|}{ Grommet Surf Board } & & & Total Cost of Inventory Plan & \\ \hline Retail price & & $400 & & & & & \\ \hline COGS & & $150 & & & & & \\ \hline Lead time & & 30 days & & & & & \\ \hline \multicolumn{2}{|c|}{ Inventory carrying cost } & 15% & & & & & \\ \hline \multicolumn{2}{|c|}{ Expenditure/order } & $935 & & & & & \\ \hline \end{tabular} Surf Shaper, a surf warehouse in California, serves over 300 surf shops and retailers along the California coast. Surf Shaper places their boards in surf shops on consignment and replaces the consignment stock from their warehouse as boards are sold, typically within three days. Inventory must be carefully managed. If the retail surf shops run out of Surf Shaper boards before the consignment inventory is replaced, sales may be lost as customers choose other vendors. Surf Shaper may also lose their "shelf space" within the retailer's shop if their supply chain proves unreliable. For this reason, Surf Shaper strives to maintain a 96% service level with their retailers. The best-selling board for Surf Shaper is the Grommet, their 8-foot foam wide board, that is suitable for beginners and casual surfers. The Grommet retails for $400, and COGS is $150 per unit. The popularity of the Grommet has been steadily increasing over the past three years. The unit sales report is displayed below. The Grommet is manufactured in Thailand. Orders take 30 days to arrive via sea freight. With the costs of communications, customs, and freight, each order placed incurs an expenditure of $935. Once the boards arrive at the warehouse, the inventory carrying cost is 15% per year/per unit of the COGS. Based on the information provided in this case study, you will create a 2021 inventory plan for Surf Shaper. Using the spreadsheet provided and the formulas you learned and practiced last week: 1. Create a Sales Forecast showing a 15% increase in sales over 2020. 2. Using your 2021 Sales Forecast, determine the amount of Safety Stock Surf Shapers should keep on-hand to meet their service level goal. 3. Utilizing the EOQ method, identify: a. The optimal order quantity (Q) b. The number of orders to be completed c. The Re-Order Point d. Total ordering cost e. Inventory carrying cost f. Safety Stock Carrying cost g. COGS h. Total Cost of the inventory planStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started