Kelvin is the store manager for a bicycle retailer store in Ion Orchard. Last year, the sales of the bicycles were stable and are shown in Table 1. To keep sufficient stock to satisfy customer demands, Kelvin places orders to the supplier when stock on hand falls below a certain level. The bicycles are kept at a rented warehouse in Tanjong Katong. It is estimated that the holding cost per unit is 30% of its purchase price of $200. The supplier charges a $100 transportation cost to deliver the bicycles to the warehouse.

Bicycle Sales

Sales (units)

January 100

February 110

March 100

April 80

May 100

June 100

July 120

August 90

September 110

October 100

November 100

December 90

Month

Determine the best order quantity he should be ordering from the supplier explain it with formulas

discuss the reasons for using the method in part (a) to determine

the best order quantity. What are the assumptions for the method?

How much will the total inventory cost change (in percentage) if Kelvin decides to order (X + 1)% more or (X + 1)% less than the best quantity of bicycles? What can you infer based on the findings?

Note: X represents the last digit of your PI number; if your PI number is B1234567, X

= 7.

How often should Kelvin place orders with the supplier?

What should be the reorder level, ROL, if the replenishment lead time is 2 months?

Explain with Formulas where possible and use excel to determine many thanks