IT Experts has received a consulting engagement from Lakeshore
Service Station to design a system (simulation) that would help
them predict gasoline demand, order quantity, and profits.
Lakeshore’s is a service station that sells gasoline to boat
owners. The demand for gasoline depends on weather conditions
and fluctuates according to the following distribution.
Weekly Demand
Probability
1000
.05
2000
.10
3000
.23
4000
.42
5000
.20
Shipments arrive once a week. Since Lakeshore is located
in a remote place, it must order and accept a fixed quantity of
gasoline every week. Joe, the owner, faces the following
problem: If he orders too small a quantity, he will lose, in
terms of lost business and goodwill, 10 cents per gallon demanded
and not provided. If he orders too large a quantity, he will
have to pay 6 cents per gallon shipped back due to lack of storage.
For each gallon sold he makes 35 cents profit. At the
present time, Joe receives 3,500 gallons at the beginning of each
week before he opens for business. He feels that he should
receive more, maybe 3,600 or even 3,800 gallons (with a new
tank). The tank’s current capacity is 4,000 gallons.
The problem is to find the best order quantity (you
must answer this question at a minimum). Assume, Joe
starts the first week off with 200 gallons in beginning
inventory.
EOQ can not be used due to the unpredictability of the
weather. This problem can be solved by trial and error over
time. That is, the service station can actually order each
quantity for approximately 10 weeks, then compare the
results. However, a simulation can give an answer in a few
minutes (this is a Monte Carlo simulation and requires random
numbers). Furthermore, the results of the simulation will be
much more accurate, since years of operations can be simulated
rather than only 10 weeks. Also, the losses are not real,
they are only on paper. (modified from Turban, MIS/Database
texts).
THE ENGAGEMENT:
(1) Lakeshore wants IT Experts to design a simulation that would
help them predict demand. This simulation should be flexible
enough so if conditions change (for example, probabilities or if
their tank size would increase) that Lakeshore’s staff can modify
the simulation based on new information. Include comments
(written) within the cells and do not hardcode numbers within
formulas.
(2) They would also, like to see a graphical representation
(vertical bar graph) of the simulated demand vs. the units sold AND
a line graph of the weekly profit.
(3) Also they are looking at purchasing a new business,
see sheet labeled Marin’s 12 Month Trend under Course
Documents. The company’s total costs and units of sales over
the last 12 months are provided and they want you to run a
regression analysis so they can better understand the costs
(numbers do not correlate with the above situation…however, I want
you to explore regression within Excel…post questions in the
discussion board). Include as a separate worksheet (tab)
within your file.
(4) They require a training manual (documentation) in MSWord
(1-2 pages in length). The manual (instructions) you would
provide your client should describe how to use the file, modify the
file, and interpret the data. Also, in your first paragraph
you should tell your client what this system, which you just
designed, will do for them…its purpose.
(5) The last thing they are requesting is a NPV analysis
of a new tank…if a new tank will cost $20,000 and increase net
cashflow by $6,000 in the first year, and $5,200 for the next 4
years (years 2-5), should they invest in the tank if their discount
rate is 10%? 15%? What is the internal rate of
return?
Marin’s 12 Month Trend (to be used for
regression)
Month
units sold
total cost
Jan
2,100
19,800
Feb
3,000
27,000
Mar
3,700
32,600
Apr
3,800
33,400
May
4,200
36,600
Jun
5,000
43,000
Jul
4,800
41,400
Aug
4,500
39,000
Sep
3,800
33,400
Oct
3,500
31,000
Nov
3,200
28,600
Dec
2,700
24,600