Question

Tom Terrific leases rafts from a supplier and rents them to customers who use them to float down the Illinois river.   Each day Tom leases 30 rafts from his supplier, at a cost of $15 per raft. He rents them to his customers for $30 per day. Rental demand follows a normal distribution, with a mean of 30 rafts and a standard deviation of 6 rafts. (Once generated, make all demands integers in your model using INT function – see podcast.)

• Create a simulation model to calculate daily profit.

• Simulate 25 days of operation

• Calculate the average daily profit

Answer 1

Cost/raft	15	$
Rental/raft	30	$
Demand follows normal distribution
Mean	30
SD	6
All demands to be integer

Further, we can use norm.inv function in excel to generate demand following normal distribution with mean 30 and standard deviation as 6

the formula is norm.inv (probability, mean, sd); here for probability we have give a random number between 0 and 1 which can further be generated using rand() function

so to create a random demand no we use =norm.inv(rand(), 30, 6)

We have to copy the formula in 25 rows to give 25 days of random demand. Please ensure to copy and paste the 25 nos from formulas to values since the nos will keep changing on pressing enter in excel everytime

Next, use the int function in excel to convert all nos obtained above to integers

Now we use the following formula to calculate the profit

profit = revenue - costs = 30x - 15y

where x is the revenue generated based on demand that day and y is the no of rafts leased each day which is 30 every day

So proft = 30x - 15*30 = 30x - 4500. we can put this formula in another column in excel and calculate the profit for each value of random integer demand no generated above

Below is the table that I generated in excel. Please note that you will get a different table as the demand generated are random nos

Day	Demand	Integer	Profit
1	36.98	36	630
2	34.27	34	570
3	20.54	20	150
4	29.46	29	420
5	33.81	33	540
6	41.71	41	780
7	30.15	30	450
8	33.52	33	540
9	40.97	40	750
10	30.07	30	450
11	20.51	20	150
12	23.53	23	240
13	21.83	21	180
14	18.20	18	90
15	33.84	33	540
16	31.35	31	480
17	17.60	17	60
18	38.93	38	690
19	27.58	27	360
20	38.99	38	690
21	25.08	25	300
22	42.45	42	810
23	36.65	36	630
24	30.37	30	450
25	32.69	32	510

From above, average daily profit = $458