Need Help With This Assignment?

Let Our Team of Professional Writers Write a PLAGIARISM-FREE Paper for You!

SUBMIT YOUR ASSIGNMENT

Math/Economics/Statistics - Excel Based

Inventory Planning

Inventory Planning

2018 2019 2020 Sales Forecast 2021 Deviation Cost per unit 150
January 790 853 929 January            1,068    1,014.98 Inventory Holding Cost per unit 22.5
February 800 864 944 February            1,086       997.73
March 1,300 1,404 1,544 March            1,776       307.27 Z-score 0.65
April 1,560 1,685 1,943 April            2,234       151.12 Standard Deviation of Demand 2411.62
May 1,620 1,750 2,469 May            2,839       756.02 Safety Stock 1567
June 1,970 2,128 2,584 June            2,972       888.27
July 2,140 2,311 2,539 July            2,920       836.52 EOQ (Q*)              1,441
August 2,230 2,408 2,541 August            2,922       838.82 Maximum Inventory, (Q* + SS)              3,008
September 1,560 1,685 2,426 September            2,790       706.57 Average Inventory, (Q*/2)            720.72
October 1,280 1,382 1,573 October            1,809       274.38 Number of Orders 17
November 1,070 1,156 1,271 November            1,462       621.50 Re-order Point              2,922
December 940 1,015 976 December            1,122       960.93
Total 17,260 18,641 21,740 Total         25,000    8,354.11 Total Ordering Cost $16,216
Inventory Carrying Cost $16,216
Safety Stock Carrying Cost $35,250
COGS $150
Total Cost of Inventory Plan $67,683

ORDER A PLAGIARISM-FREE PAPER HERE

We’ll write everything from scratch

Question 

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 panels 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 boards. If its supply chain proves unreliable, Surf Shaper may lose its “shelf space” within the retailer’s shop. For this reason, Surf Shaper strives to maintain a 96% service level with its retailers.

Inventory Planning

Inventory Planning

The best-selling board for Surf Shaper is the Grommet, their 8-foot foam wide board suitable for beginning 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 for the past three years 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 shipment, each order placed charges $935. Once the boards arrive at the warehouse, the inventory carrying amount of the COGS is 15% per year/unit.

Instructions

Based on the information in this case study, you will create a 2021 inventory plan for Surf Shaper. Use the following spreadsheet, as well as the formulas you learned and practiced last week, to answer the prompts below:

Surf Shaper Case Study

Surf Shaper Case Study – Spreadsheet with 2021 Forecast

  1. Create a Sales Forecast showing a 15% increase in sales over 2020.
  2. Use the 2021 Sales Forecast to determine how much Safety Stock Surf Shaper should keep on hand to meet their service level goal.
  3. Use the EOQ method to identify:
    • The optimal order quantity (Q*)
    • The number of orders to be completed
    • The Re-Order Point
    • Total ordering cost
    • Inventory carrying cost
    • Safety Stock Carrying cost
    • COGS
    • Total cost of the inventory plan