Hello, I'm looking for help in using cell referencing and formulas in excel to work throught the problem below. If you could screenshot or provide a workbook to the formulas & cell references used to solve, it would be greatly appreciated. I've included an excel link to the question with "hints", if it makes it easier. Thank You. Excel Workbook Link: https://1drv.ms/x/s!AoRYCwyoAxDMpRGiebfdBQQ81Swc

AC341 Excel Project

Simulation Excel Project #2

IT Experts is a consulting company employing CPAs and IT professionals. They have received a consulting engagement from Lakeside Service Station to design a system (simulation) that would help them predict gasoline demand, order quantity, and profits. Lakeside is a service station that sells gasoline to boat owners and is located in a remote location on a large lake. The demand for gasoline depends on weather conditions and fluctuates according to the following distribution.

Weekly Demand	Probability
1000	.06
2000	.09
3000	.25
4000	.40
5000	.20

Shipments arrive once a week. Since Lakeside is located in a remote place, it must order and accept a fixed quantity of gasoline every week for a period of 10 weeks. Joe, the owner, faces the following problem: If he orders too small a quantity, he will lose, in terms of lost business and goodwill, 12 cents per gallon demanded and not provided. If he orders too large a quantity, he will have to pay 10 cents per gallon shipped back due to lack of storage. For each gallon sold he makes $2.05 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. The tanks 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 with 1200 gallons in his beginning inventory. Therefore, the first week, he will have 1200 gallons plus his weekly order.

EOQ cannot 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 order each quantity for approximately 10 weeks, then compare the results. However, a simulation can give an answer in a few minutes - this is referred to as a Monte Carlo simulation because sales are dependent on the chance of good weather, and this type of system 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 in this simulation are not real, they are only on paper.

(This project is modified with permission from Turban, AIS/Database text).

THE ENGAGEMENT:

(1) 60 pts. Lakeside 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 Lakesides staff can modify the simulation based on new information. Include comments (written) within the cells and do not hardcode numbers within formulas.

(2) 12 pts. 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. This is a data visualization of demand compared to units and the variability of weekly profit.

(3) 8 pts. Also, they are looking at purchasing a new business, see sheet labeled Marins 12 Month Trend under Projects. The companys 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 situationhowever, I want you to explore using regression within Excel). Include as a separate worksheet (tab) within your file.

(4) 8 pts. 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 themits purpose.

(5) 8 pts. The last thing they are requesting is a NPV analysis of a new tankif 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? Also, see #3 below.

What Needs Done/Show:

A) In your simulation complete Rand Number, Begin Inv, Demand, and Sales columns.

B) Complete the regression analysis.

C) Complete the PV and IRR analysis.

D) In your simulation spreadsheet also 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).

Lakesides 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