Monte Carlo Simulation
Tully Tyres sells cheap imported tyres. The manager believes itsprofits are in decline. You have just been hired as an analyst bythe manager of Tully Tyres to investigate the expected profit overthe next 12 months based on current data.
•Monthly demand varies from 100 to 200 tyres – probabilitiesshown in the partial section of the spreadsheet below, but you haveto insert formulas to ge the cumulative probability distributionwhich can be used in Excel with the VLOOKUP command.
•The average selling price per tyre follows a discrete uniformdistribution ranging from $160 to $180 each. This means that it cantake on equally likely integer values between $160 and $180 – moreon this below.
•The average profit margin per tyre after covering variable costsfollows a continuous uniform distribution between 20% and 30% ofthe selling price.
•Fixed costs per month are $2000.
(a)Using Excel set up a model to simulate the next 12 months todetermine the expected average monthly profit for the year. Youneed to have loaded the Analysis Toolpak Add-In to your version ofExcel. You must keep the data separate from the model. The modelshould show only formulas, no numbers whatsoever except for themonth number.
You can use this partial template to guide you:
| Tully Tyres | | | | | | | |
| Data | | | | | | | |
| Probability | Cumulative probability | Demand | | Selling price | $160 | $180 | |
| 0.05 | | 100 | | Monthly fixed cost | $2000 | | |
| 0.1 | | 120 | | Profit margin | 20% | 30% | |
| 0.2 | | 140 | | | | | |
| 0.3 | | 160 | | | | | |
| 0.25 | | 180 | | | | | |
| 0.1 | | 200 | | | | | |
| 1 | | | | | | | |
| | | | | | | |
| Model | | | | | | | |
| | | | | | | |
| Month | Random number1 | Demand | Selling price | Random number 2 | Profit margin | Fixed cost | Profit |
| 1 | 0.23297 | #N/A | $180 | 0.227625 | 0.2 | | |
| | | | | | | |
The first random number (RN 1) is to simulate monthly demandsfor tyres.
•The average selling price follows a discrete uniform distributionand can be determined by the function =RANDBETWEEN(160,180) in thiscase. But of course you will not enter (160,180) but the data cellreferences where they are recorded.
•The second random number (RN 2) is used to help simulate theprofit margin.
•The average profit margin follows a continuous uniformdistribution ranging between 20% and 30% and can be determined bythe formula =0.2+(0.3-0.2)*the second random number (RN 2). Againyou do not enter 0.2 and 0.3 but the data cell references wherethey 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 therandom number is low, say 0, then 0.3-0.2 becomes zero and theprofit margin becomes 0.2.
•Add the 12 monthly profit figures and then find the averagemonthly profit.
Show the data and the model in two printouts: (1) the results,and (2) the formulas. Both printouts must show the grid (ie., rowand 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 the12-month period.
(c)You present your findings to the manager of Ajax Tyres. Hethinks that with market forces he can increase the average sellingprice by $40 (ie from $200 to $220) without losing sales. Howeverhe does suggest that the profit margin would then increase from 22%to 32%.
He has suggested that you examine the effect of these changesand report the results to him. Change the data accordingly in yourmodel to make the changes and paste the output in your Word answerthen write a report to the manager explaining your conclusions withrespect to his suggestions. Also mention any reservations you mighthave about the change in selling prices.
The report must be dated, addressed to the Manager and signedoff by you.
(Word limit: No more than 150 words)
