Question

Inventory Management

Your company keeps fleets of vehicles at a number of sites around the country. At each site the vehicles can be classified into two types: light and heavy. A heavy vehicle costs more per day, but it can do any task that a light vehicle can do. You would like to determine what mix of vehicles your company should have at each site. If you do not have enough vehicles of the appropriate size to meet the demand on a given day, you must rent the vehicles. Some cost data were collected on the cost of various vehicle types:

Vehicle Type             Daily fixed cost          Daily variable cost (if used)

Owned Light               $32                              $40

Owned Heavy                         $44                              $54

Rented Light               0                                  $175

Rented Heavy             0                                 $225

At a particular site, your company collected demand data for the number of vehicles required on each of seven days:

Day     Lights             Heavies

1          6                      0

2          3                      2

3          5                      4

4          8                      3

5          2                      1

6          4                      4

7          1                      2

Based on just the above data, what is your recommendation for the number of vehicles to own of each type at this site? What is your weekly cost?

Please shoe and explain using solver

Answer 1

Optimal number of vehicles to be used is determined by spreadsheet model as follows:

EXCEL FORMULAS:

Weekly cost = $ 6,374