Tully Tyres sells cheap imported tyres. The manager believes its profits are in
ID: 3069570 • Letter: T
Question
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, but you have to insert formulas to ge the cumulative probability distribution which can be used in Excel with the VLOOKUP command.
•The average selling price per tyre follows a discrete uniform distribution ranging from $160 to $180 each. This means that it can take on equally likely integer values between $160 and $180 – 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 $2000.
(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 except for the month number.
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(160,180) in this case. But of course you will not enter (160,180) 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 Ajax Tyres over the 12-month period.
(c)You present your findings to the manager of Ajax Tyres. He thinks that with market forces he can increase the average selling price by $40 (ie from $200 to $220) without losing sales. However he does suggest that the profit margin would then increase 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)
Explanation / Answer
a)Model sheet and Formula Sheet
Model
Month
RN1
Demand
Selling price
RN2
Profit margin
Fixed cost
Profit
Cost price
profit per piece
1
0.94557728
200
171
0.45306216
0.24530622
2000
6736.87521
137.3156
33.68438
2
0.86504377
180
163
0.86540213
0.28654021
2000
6534.64988
126.6964
36.30361
3
0.75401736
180
167
0.31171716
0.23117172
2000
5644.23443
135.6431
31.35686
4
0.84764454
180
161
0.04897888
0.20489789
2000
4928.1693
133.6213
27.37872
5
0.01798387
100
179
0.74707744
0.27470774
2000
3857.56551
140.4243
38.57566
6
0.11064554
120
160
0.55468882
0.25546888
2000
3906.90889
127.4424
32.55757
7
0.65084945
180
176
0.09396328
0.20939633
2000
5485.11312
145.5271
30.47285
8
0.82800777
180
162
0.24019748
0.22401975
2000
5336.85494
132.3508
29.64919
9
0.51640536
160
167
0.38647226
0.23864723
2000
5148.07908
134.8245
32.17549
10
0.08969567
120
175
0.27855721
0.22785572
2000
3897.01335
142.5249
32.47511
11
0.9398299
200
165
0.94256129
0.29425613
2000
7502.72843
127.4864
37.51364
12
0.13051547
120
169
0.50122161
0.25012216
2000
4057.5854
135.1868
33.81321
Average profit =
5252.98146
Formula
Month
RN1
Demand
Selling price
RN2
Profit margin
Fixed cost
Profit
Cost price
profit per piece
1
=RAND()
=VLOOKUP(B14,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E14
2000
=J14*C14
=D14*100/(100+(F14*100))
=D14-I14
2
=RAND()
=VLOOKUP(B15,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E15
2000
=J15*C15
=D15*100/(100+(F15*100))
=D15-I15
3
=RAND()
=VLOOKUP(B16,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E16
2000
=J16*C16
=D16*100/(100+(F16*100))
=D16-I16
4
=RAND()
=VLOOKUP(B17,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E17
2000
=J17*C17
=D17*100/(100+(F17*100))
=D17-I17
5
=RAND()
=VLOOKUP(B18,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E18
2000
=J18*C18
=D18*100/(100+(F18*100))
=D18-I18
6
=RAND()
=VLOOKUP(B19,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E19
2000
=J19*C19
=D19*100/(100+(F19*100))
=D19-I19
7
=RAND()
=VLOOKUP(B20,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E20
2000
=J20*C20
=D20*100/(100+(F20*100))
=D20-I20
8
=RAND()
=VLOOKUP(B21,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E21
2000
=J21*C21
=D21*100/(100+(F21*100))
=D21-I21
9
=RAND()
=VLOOKUP(B22,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E22
2000
=J22*C22
=D22*100/(100+(F22*100))
=D22-I22
10
=RAND()
=VLOOKUP(B23,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E23
2000
=J23*C23
=D23*100/(100+(F23*100))
=D23-I23
11
=RAND()
=VLOOKUP(B24,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E24
2000
=J24*C24
=D24*100/(100+(F24*100))
=D24-I24
12
=RAND()
=VLOOKUP(B25,$L$4:$M$9,2,TRUE)
=RANDBETWEEN(160,180)
=RAND()
=0.2+(0.3-0.2)*E25
2000
=J25*C25
=D25*100/(100+(F25*100))
=D25-I25
Average profit =
=AVERAGE(H14:H25)
b)Average monthly profit over 12-month period is 5252.9816
Model
Month
RN1
Demand
Selling price
RN2
Profit margin
Fixed cost
Profit
Cost price
profit per piece
1
0.94557728
200
171
0.45306216
0.24530622
2000
6736.87521
137.3156
33.68438
2
0.86504377
180
163
0.86540213
0.28654021
2000
6534.64988
126.6964
36.30361
3
0.75401736
180
167
0.31171716
0.23117172
2000
5644.23443
135.6431
31.35686
4
0.84764454
180
161
0.04897888
0.20489789
2000
4928.1693
133.6213
27.37872
5
0.01798387
100
179
0.74707744
0.27470774
2000
3857.56551
140.4243
38.57566
6
0.11064554
120
160
0.55468882
0.25546888
2000
3906.90889
127.4424
32.55757
7
0.65084945
180
176
0.09396328
0.20939633
2000
5485.11312
145.5271
30.47285
8
0.82800777
180
162
0.24019748
0.22401975
2000
5336.85494
132.3508
29.64919
9
0.51640536
160
167
0.38647226
0.23864723
2000
5148.07908
134.8245
32.17549
10
0.08969567
120
175
0.27855721
0.22785572
2000
3897.01335
142.5249
32.47511
11
0.9398299
200
165
0.94256129
0.29425613
2000
7502.72843
127.4864
37.51364
12
0.13051547
120
169
0.50122161
0.25012216
2000
4057.5854
135.1868
33.81321
Average profit =
5252.98146
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.