Question

How can I solve this in Excel? Please show how to solve it in excel step by step.

1) Speedy Wheels is a wholesale distributor of bicycles for the western United States. Its Inventory Manager, Ricky Sapolo, is currently reviewing the inventory policy for one popular model — a small, one-speed girl's bicycle that is selling at the rate of 250 per month. The administrative cost for placing an order for this model from the manufacturer is $200 and the purchase price is $70 per bicycle. The annual cost of the capital tied up in inventory is 20 percent of the value of these bicycles. The additional cost of storing the bicycles — including leasing warehouse space, insurance, taxes, and so on — is $6 per bicycle per year.

a. Use the basic EOQ model to determine the optimal order quantity and the total variable inventory cost per year.

b. Speedy Wheel's customers (retail outlets) generally do not object to short delays in having their orders filled. Therefore, management has agreed to a new policy of having small planned shortages occasionally to reduce the variable inventory cost. After consultations with management, Ricky estimates that the annual shortage cost (including lost future business) would be $30 times the average number of bicycles short throughout the year. Use the EOQ model with planned shortages to determine the new optimal inventory policy.

c. Construct a table with table with the same rows and columns as in Table 18.1 to compare the results from parts a and b.

Answer 1

Problem was modelled in Excel and solution was prepared. Snapshots are shown below for calculations and formulae:

A. EOQ = 293 bicycles.

Total Variable Inventory Costs per year = $2,049.39

B. Cannot be solved since we know the neither the Lead Time nor the standard deviation of the mean sales (250 bicycles/month).

However, Excel model has been prepared for you, as shown, so that you can input those values and then proceed ahead as you like. Co & Cu are used for calculating Service Level (SL) and then Re-Order Level (RoL) can be calculated as shown with formulas for calculate the Re-Order Point.

C. Table 18.1 is not given, so don't know what exactly to prepare.

Hope you can reproduce your excel using this snapshot and answer provided. If not, please post another question with all the details which are not present here and I will solve it further for you.

PS: While posting another question, don't forget to provide all the relevant data otherwise the situation will remain the same and complete solution won't be able to be provided.