AC341 ? Excel Project

Simulation ? Excel Project #2

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	.24
4000	.43
5000	.18

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, 9 cents per gallon demanded and not provided. If he orders too large a quantity, he will have to pay 5 cents per gallon shipped back due to lack of storage. For each gallon sold he makes 25 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?

DELIVERABLES:

1. Complete the above 5 requirements.
2. In your simulation spreadsheet answer the following questions: (a) How much would you charge for this job AND why? Show your calculations within your Excel file. (b) Should your client invest in the new tank? Why? (place both of these answers under your calculation of NPV?#5 above).

AC341 - Excel Project Simulation - Excel Project #2 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 1000 2000 3000 4000 5000 Probability .05 .10 .24 .43 .18 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, 9 cents per gallon demanded and not provided. If he orders too large a quantity, he will have to pay 5 cents per gallon shipped back due to lack of storage. For each gallon sold he makes 25 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? DELIVERABLES: 1. Complete the above 5 requirements. 2. In your simulation spreadsheet answer the following questions: (a) How much would you charge for this job AND why? Show your calculations within your Excel file. (b) Should your client invest in the new tank? Why? (place both of these answers under your calculation of NPV...#5 above). Title: Whatever you want (Centered over the entire spreadsheet) Quantity (in gallons): Random Number Week # Inventory at Beginning of Simulated Week Demand Ib = Ie+order quantity D Sold Inventory at End of Week Unsatisfied Demand Shipped Back S Ie=Ib-S U=D-Ib B Weekly Profit Average Weekly Profit 1 2 3 4 5 6 7 8 9 10 Total Average Per Week ----- --- Probability Table Weekly Demand (Gal) Probability Cumulative Probability Representative Numbers (range) 2000 3000 4000 5000 0.12 0.23 0.48 0.17 0.12 0.35 0.83 1.00 01-12 13-35 36-83 84-00 Marin's 12 Month Trend (to be used for regression) Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec units sold 2,100 3,000 3,700 3,800 4,200 5,000 4,800 4,500 3,800 3,500 3,200 2,700 total cost 19,800 27,000 32,600 33,400 36,600 43,000 41,400 39,000 33,400 31,000 28,600 24,600 Example of "nested-Ifs" with hardcoding...which is a no-no ;-) : Grade Pts. 89 30 15 60 75 95 GRADE B F F D C A Monica McElhaney: Look at the hard-coded information and then compare with the formulas below. Example of "nested-ifs" with a more flexible design (no hard-coded information with the use of absoluting [F4 function key] and th Grade Pts. 89 30 15 60 75 95 GRADE B F F D C A A B C D F 90 80 70 60 Monica McElhaney: Notice the hard-coded information has been replaced with formulas (you could also use VLOOKUP or HLOOKUP if you would like...not required though). HINTs:...1. In your project, demand is based on random probability given the fact demand is based on the weather. 2. You are trying to determine "order quantity" based on the weather...what quantity provides the highest profitability. 3. In the example above, the random number would be comparable to the "grade pt." column and the demand would be the "grade" column. 4. ...and you can not sell more gasoline that what you have in inventory AND you can not sell more than demanded. nd the use of a table)