QUESTION : Monte Carlo Simulation

This is a work integrated assessment item. The tasks are similar to what would be carried out in the workplace.

Guide to marks: 20 marks 12 for a, 2 for b, 6 for c

Tully Tyres sells cheap imported tyres. The manager believes its profits are in decline. You have just been hired as an analyst by the manager of Tully Tyres to investigate the expected profit over the next 12 months based on current data.

Monthly demand varies from 100 to 200 tyres probabilities shown in the partial section of the spreadsheet below. The average selling price per tyre follows a discrete uniform distribution ranging from $60 to $80 each. This means that it can take on equally likely integer values between $60 and $80 more on this below. The average profit margin per tyre after covering variable costs follows a continuous uniform distribution between 20% and 30% of the selling price. Fixed costs per month are $1500.

(a) Using Excel set up a model to simulate the next 12 months to determine the expected average monthly profit for the year. You need to have loaded the Analysis Toolpak Add-In to your version of Excel. You must keep the data separate from the model. The model should show only formulas, no numbers whatsoever.

You can use this template to guide you:

Tully Tyres

DATA

Prob	Cumulative prob	Demand		Selling	Price	$60	$80
0.05		100		Monthly	Fixed cost	$1,500
0.10		120		Profit	Margin	20%	30%
0.20		140
0.30		160
0.25		180
0.10		200
1.00
MODEL
			Selling		Profit	Fixed
Month	RN 1	Demand	Price	RN 2	Margin	Costs	Profit

The first random number (RN 1) is to simulate monthly demands for tyres. The average selling price follows a discrete uniform distribution and can be determined by the function =RANDBETWEEN(60,80) in this case. But of course you will not enter (60,80) but the data cell references where they are recorded. The second random number (RN 2) is used to help simulate the profit margin. The average profit margin follows a continuous uniform distribution ranging between 20% and 30% and can be determined by the formula =0.2+(0.3-0.2)*the second random number (RN 2). Again you do not enter 0.2 and 0.3 but the data cell references where they are located. Note that if the random number is high, say 1, then 0.3-0.2 becomes 1 and when added to 0.2 it becomes 0.3. If the random number is low, say 0, then 0.3-0.2 becomes zero and the profit margin becomes 0.2. Add the 12 monthly profit figures and then find the average monthly profit.

Show the data and the model in two printouts: (1) the results, and (2) the formulas. Both printouts must show the grid (ie., row and column numbers) and be copied from Excel and pasted into Word. See Spreadsheet Advice in Interact Resources for guidance.

(b) Provide the average monthly profit to Tully Tyres over the 12-month period..

(c) You present your findings to the manager of Tully Tyres. He thinks that with market forces he can increase the average selling price by $20 (ie range from $80 to $100) without losing sales. However he does suggest that the profit margin would then increase to range from 22% to 32%.

He has suggested that you examine the effect of these changes and report the results to him. Change the data accordingly in your model to make the changes and paste the output in your Word answer

Then write a report to the manager explaining your conclusions with respect to his suggestions. Also mention any reservations you might have about the change in selling prices.

The report must be dated, addressed to the Manager and signed off by you. (Word limit: No more than 150 words)