Parameters

Selling Price 249

Admin and
1,000,000
Advertising Cost
Direct Labor cost
45
per unit
Parts cost per unit
90
of demand

Model Equation
Profit (249-c1-c2)*Demand-FC

Simulation Trial Direct Labour Cost Parts Cost Per unit

1 45 81.5606826269547
2 45 97.0546410235154
3 44 95.8572830683563
4 47 82.2690154982388
5 46 98.8191154108134
6 45 92.4662272273115
7 46 96.353498396251
8 44 92.29505872557
9 45 90.5745850369672
10 44 92.8512357998399
11 45 85.257505314852
12 45 85.5604742622374
13 45 90.9018541174711
14 45 80.4433555156862
15 44 82.4229813900812
16 46 92.208513832158
17 45 94.5958754208848
18 46 81.8335999853685
19 45 90.6973317023221
20 46 86.4179443056666
21 44 83.1204868700121
22 45 81.1387907633854
23 45 92.5925576746548
24 43 85.7219454472886
25 47 99.9395391698903
26 45 90.8153234351418
27 44 98.4110583834929
28 45 84.3383876311066
29 43 96.7048008677574
30 46 86.6954014360716
31 46 86.0257071317994
32 44 83.6007088673934
33 45 80.5504797229988
34 43 99.2454190917622
35 45 84.3691244230844
36 46 98.77210558529
37 45 80.5674199179364
38 45 95.3749962351224
39 44 97.8659955625897
40 45 92.0300155833541
41 44 99.7527570860275
42 44 91.8744609560967
43 47 95.3027688749807
44 46 90.007545042032
45 47 90.3281953728503
46 43 84.0226735581997
47 45 95.1131834076885
48 44 99.4822417052757
49 46 83.9079353946877
50 47 93.7907756128154
51 44 88.8887098534715
52 43 94.1012533194501
53 45 86.757598749505
54 45 84.5537606551605
55 47 88.7718453173743
56 45 80.5397782568306
57 45 86.9548644943253
58 46 99.6691243924862
59 45 98.2811986626044
60 44 88.3668755151504
61 45 81.8056476305441
62 47 84.1882403916649
63 47 96.7150919482176
64 47 87.0766413973222
65 44 91.479762661141
66 47 85.5308670145189
67 44 83.5488049628857
68 45 86.7594335969126
69 45 93.4851864680827
70 46 93.4436302431876
71 46 93.3920727915032
72 45 82.9539145001169
73 46 96.6323956695064
74 46 83.6287038191756
75 47 87.4251429174988
76 47 88.4482252213563
77 45 87.433573116194
78 45 94.3342988805674
79 43 99.199405115987
80 43 88.1409411383127
81 45 99.2692934088947
82 45 84.2260651168465
83 44 99.7493232815164
84 45 89.0214997249467
85 43 92.0108996289322
86 44 99.2559799778961
87 46 90.6251876574344
88 45 92.7686131099014
89 44 85.1244564335982
90 45 89.2743076426176
91 45 86.5427194620167
92 46 90.2214587801872
93 44 85.3505843687108
94 45 84.6641422851894
95 44 99.8025711254274
96 45 82.4411977214691
97 45 87.1358649883594
98 46 87.6561759775466
99 46 83.8821375885015
100 43 92.9632916720646
GIVEN

Variable

710,000 Base Case

-1,000,000 Worst Case
2,780,000 Best Case

Demand Profit
18945.7045236666 1,319,699.13
14236.923846558 522,572.93
9171.7431165828 1,028.96
32920.0001120999 2,941,544.02
7559.84145660047 -212,409.03
8289.69170028634 -75,419.41
14913.9942036719 590,525.31
15288.3574906752 723,073.43
15460.8892545985 753,657.78
14365.3296585762 611,053.97
15509.4217006902 841,627.42
17484.3820219728 1,070,841.91
18119.8201820988 1,049,318.07
14923.6769398411 843,919.45
12392.470503394 519,032.09
20011.3976484501 1,217,092.49
9567.75764577451 46,752.15
17085.6175813964 1,070,202.77
16444.0806291668 863,158.21
17479.9239035496 1,037,845.46
11789.5164682626 436,900.53
9797.7455050579 203,762.86
19042.0255920788 1,121,423.37
20214.0068617422 1,431,301.42
10339.553652076 55,259.61
11621.6122393103 315,388.42
18696.3812646233 992,827.49
15398.5105997844 842,610.61
9207.72792131779 6,360.46
19283.6962873176 1,242,782.56
11340.1388065822 326,504.72
13141.1972623645 595,332.03
15193.3630396781 875,613.38
11158.2296051441 191,192.13
11343.0906052865 356,983.86
10999.970607636 146,503.78
14779.0314041785 824,213.98
15946.4902433863 732,187.56
26620.3299668617 1,851,942.55
16817.3186303535 883,034.90
12283.8709641056 292,843.55
10812.635188928 223,185.18
13476.5166536811 437,907.01
21757.0522580395 1,458,382.75
15338.0930142899 712,832.53
12349.7227836483 506,386.17
20430.1023342572 1,224,568.81
10315.5533609576 88,474.07
13326.1757487037 587,041.78
9762.87858506053 56,433.52
7791.15683221283 -95,358.73
17063.2999406562 909,361.88
9568.3989346292 121,822.07
12086.4694616615 443,683.32
16456.088747131 863,292.56
17072.3519593341 1,107,756.36
13515.9326908461 581,974.17
13145.1286510331 358,297.65
20657.6571890663 1,183,902.76
9036.62398582235 53,969.69
22399.2850592698 1,737,066.13
16716.9365316082 969,451.71
12286.5078981232 293,583.85
11600.5211324164 333,170.85
14667.4712102625 665,054.81
9540.30086935066 111,150.57
17514.1836166267 1,127,118.53
14204.4591445348 665,338.84
12298.1699313208 359,129.96
22762.390553021 1,493,764.88
15451.9128492935 693,652.14
9194.03582203742 112,902.05
12564.7997037373 336,487.64
14463.8613387161 726,569.88
 14779.271997126 693,332.98
6414.29800773132 -271,645.08
14905.589280326 737,491.28
21042.2492787211 1,307,613.02
14859.8885932017 587,044.94
13553.6684000349 597,422.60
19314.7693278267 1,022,849.44
17547.8108991814 1,101,770.36
11664.0630117313 227,650.53
17735.9894840842 1,039,257.47
19485.9706072363 1,221,188.26
14954.9644291993 581,398.06
12542.882402472 409,504.06
7420.78945137353 -174,575.30
15912.4469649899 907,513.23
21265.7320590425 1,439,725.83
28402.0459644023 2,336,027.08
21466.3568498506 1,420,944.41
13961.431287538 670,477.09
10938.8008123773 305,391.18
19055.7885052586 1,004,619.96
12289.4226234923 493,887.49
12903.0457205959 507,903.28
13971.9079934054 611,573.30
10627.7798974541 265,958.42
7453.95005545693 -157,430.02
 Only to be created (such a table) for a discrete
Direct Labour Cost
distribution

Lower Bound Upper Bound Cost Per Unit

0 0.1 43
0.1 0.3 44
0.3 0.7 45
0.7 0.9 46
0.9 1 47

Demand

x=N(15000,4500) [Link](RAND(),MEAN,SD)
Mean 15000
SD 4500

Parts Cost per unit c2

Parts Cost per unit

X=a+U*(b-a)
c2-uni(80,100) 80+rand()20
a 80
b 100
 Column1

Mean 648898.102865588
Standard Error 55072.7354471669
Median 571947.694246623
Mode #N/A
Standard Deviation 550727.354471669
Sample Variance 303300618963.364
Kurtosis 0.398001264005388
Skewness 0.522248034506622
Range 2786589.24045299
Minimum -484537.441354395
Maximum 2302051.79909859
Sum 64889810.2865588
Count 100
Probability (Data will be given in the
question)

0.1
0.2
0.4
0.2
0.1
 a=0.7 a(Yt+1) + (1-a)Ypredict of t
Week Sales (Y) Y Predict Error Squared Error
(1000's of Gallons)
1 17 17 0 0
2 21 19.8 1.2 1.44
3 19 19.24 -0.239999999999998 0.0576
4 23 21.872 1.128 1.272384
5 18 19.1616 -1.1616 1.34931456
6 16 16.94848 -0.94848 0.8996143104
7 20 19.08454 0.915455999999999 0.8380596879
8 18 18.32536 -0.325363199999998 0.1058612119
9 22 20.89761 1.10239104 1.2152660051
10 20 20.26928 -0.269282688000001 0.0725131661
11 115 86.58078 28.4192151936 807.65179222
12 22 41.37424 -19.37423544192 375.36099896

MSE 99.18861701

RMSE 9.9593482221

Accuracy

m=1.1
Linear Trend Method y=mx+C
c=20.4

Year (x) Sales Y Predict Error Squared Error

1 21.6 21.5 0.100000000000001 0.01
2 22.9 22.6 0.300000000000001 0.09
3 25.5 23.7 1.8 3.24
4 21.9 24.8 -2.9 8.41
5 23.9 25.9 -2 4
6 27.5 27 0.5 0.25
7 31.5 28.1 3.4 11.56
8 29.7 29.2 0.5 0.25
9 28.6 30.3 -1.7 2.89
10 31.4 31.4 0 0

MSE 3.07

RMSE 1.7521415468

Accuracy 94.931856963
Percentage ErrorAbsolute Percentage Error

0 0
5.7142857142857 5.71428571428573
-1.263157894737 1.26315789473683
4.904347826087 4.90434782608697
-6.453333333333 6.45333333333333
-5.928 5.928
4.57728 4.57728
-1.807573333333 1.80757333333332
5.0108683636364 5.01086836363637
-1.34641344 1.34641344
24.712361037913 24.712361037913
-88.06470655418 88.0647065541818

MAPE 12.4818606247923

87.518139375208

Sales
35
Percentage ErrorAbsolute Percentage Error
0.462962962963 0.46296296296297 30 f(x) = 1.1 x + 20.4
1.3100436681223 1.31004366812227
7.0588235294118 7.05882352941177 25

-13.24200913242 13.2420091324201
20
-8.36820083682 8.36820083682009
1.8181818181818 1.81818181818182 15
10.793650793651 10.7936507936508
1.6835016835017 1.68350168350168 10
-5.944055944056 5.94405594405593
0 0 5

0
1 2 3 4 5 6
MAPE 5.06814303691274
 Sales

4 5 6 7 8 9 10
0.2 0.8 Month Sales
1 20 18
2 21 20.4
3 15
4 14
5 13
6 16
7 17
8 18
9 20
10 20
11 21
12 23
Gross Profit per Unit 50
Holding Cost 15
Shortage Cost Per Unit 30

Replenishment Level 100

Demand (Normal Distribution)

Mean 100 INT([Link](RAND(),MEAN,SD))
SD 20

Month Demand Sales

1 128 100
2 106 100
3 103 100
4 99 99
5 67 67
6 136 100
7 95 95
8 95 95
9 85 85
10 86 86
11 81 81
12 124 100
13 104 100
14 104 100
15 85 85
16 138 100
17 129 100
18 109 100
19 97 97
20 100 100
21 78 78
22 76 76
23 123 100
24 117 100

2465 2244
91.0344827586207
 Given

Adding the "INT" so we get the whole number as values

Gross Profit Holding CostShortage Cost

5000 0 840
5000 0 180
5000 0 90
4950 15 0
3350 495 0
5000 0 1080
4750 75 0
4750 75 0
4250 225 0
4300 210 0
4050 285 0
5000 0 720
5000 0 120
5000 0 120
4250 225 0
5000 0 1140
5000 0 870
5000 0 270
4850 45 0
5000 0 0
3900 330 0
3800 360 0
5000 0 690
5000 0 510
Net Profit
4160
4820
4910
4935
2855
3920
4675
4675
4025
4090
3765
4280
4880
4880
4025
3860
4130
4730
4805 Column1
5000
3570 Mean 4394.7916667
3440 Standard Error 129.49822308
4310 Median 4447.5
4490 Mode 4970
Standard Deviation 634.40913826
Sample Variance 402474.95471
Kurtosis 9.4009564429
Skewness -2.5783200013
Range 3055
Minimum 1945
Maximum 5000
Sum 105475
Count 24
 Cost 5500
Sales Price 7000
Shortage Cost Per Unit 200

Replenishment Level 25

Demand (Discrete Distribution)

Lower Bound Upper Bound Demand
0 0.2 10
0.2 0.45 20
0.45 0.85 30
0.85 1 40

Month Demand Sales

1 20 20
2 30 25
3 10 10
4 20 20
5 40 25
6 30 25
7 40 25
8 20 20
9 10 10
10 30 25
11 20 20
12 40 25
13 40 25
14 30 25
15 40 25
16 20 20
17 20 20
18 10 10
19 20 20
20 20 20
21 10 10
22 30 25
23 40 25
24 10 10
25 20 20
26 40 25
27 30 25
28 30 25
29 20 20
30 40 25
31 20 20
32 30 25

830 670
Probability (Data will be given in the question)
0.2
0.25
0.4
0.15

Gross Profit Shortage CostNet Profit

30000 0 30000
37500 1000 36500
15000 0 15000
30000 0 30000
37500 3000 34500
37500 1000 36500
37500 3000 34500
30000 0 30000
15000 0 15000
37500 1000 36500
30000 0 30000
37500 3000 34500
37500 3000 34500
37500 1000 36500
37500 3000 34500
30000 0 30000
30000 0 30000
15000 0 15000
30000 0 30000
30000 0 30000
15000 0 15000
37500 1000 36500
37500 3000 34500
15000 0 15000
30000 0 30000
37500 3000 34500
37500 1000 36500
37500 1000 36500
30000 0 30000
37500 3000 34500
30000 0 30000
37500 1000 36500

81%
 Column1

Mean 29,718.75
Standard Error 1,584.95
Median 34,500.00
Mode 36,500.00
Standard Deviation 8,965.83
Sample Variance 80,386,088.71
Kurtosis -0.84
Skewness -0.98
Range 21,500.00
Minimum 15,000.00
Maximum 36,500.00
Sum 951,000.00
Count 32.00
Week Sales Y Predict Error Squared ErrorPercent error
(1000's of Gallons)
1 17
2 21
3 19
4 23 19 4 16 17.39130
5 18 21 -3 9 -16.666666667
6 16 20 -4 16 -25
7 20 19 1 1 5
8 18 18 0 0 0
9 22 18 4 16 18.181818182
10 20 20 0 0 0
11 115 20 95 9025 82.608695652
12 22 52.33333 -30.33333 920.11111111 -137.87878788

MSE 1111.4567901
MAPE
RMSE 33.338518115

Accuracy 66.363636364
Absolute Percentage Erro 3 bin Week Sales
(1000's of Gallons)
1 17
2 21
3 19
17.3913043478261 4 23
16.6666666666667 5 18
25 6 16
5 7 20
0 8 18
18.1818181818182 9 22
0 10 20
82.6086956521739 11 115
137.878787878788 12 22

33.6363636363636
Y Predict Error Squared ErrorPercent ErrorAbsolute Percentage Error 5 bin

19.6 -3.6 12.96 -22.5 22.5

19.4 0.6 0.36 3 3.00000000000001
19.2 -1.2 1.44 -6.666666667 6.66666666666666
19 3 9 13.63636364 13.6363636363636
18.8 1.2 1.44 6 6
19.2 95.8 9177.64 83.30434783 83.304347826087
39 -17 289 -77.27272727 77.2727272727273

MSE 1355.9771429
MAPE 30.3400150574064
RMSE 36.82359492

Accuracy 69.659984943
 Weights 0.15 0.2 0.65
Week Sales Weighted Y Predict Error Squared ErrorPercent Error
(1000's of Gallons)
1 17
2 21
3 19
4 23 19.1 3.9 15.21 16.95652174
5 18 21.9 -3.9 15.21 -21.66666667
6 16 19.15 -3.15 9.9225 -19.6875
7 20 17.45 2.55 6.5025 12.75
8 18 18.9 -0.9 0.81 -5
9 22 18.1 3.9 15.21 17.72727273
10 20 20.9 -0.9 0.81 -4.5
11 115 20.1 94.9 9006.01 82.52173913
12 22 82.05 -60.05 3606.0025 -272.9545455

MSE 1408.4097222
MAPE
RMSE 37.528785248

Accuracy 49.581750476
 Weights
Absolute Percentage Error Week

1
2
3
16.9565217391304 4
21.6666666666667 5
19.6875 6
12.75 7
4.99999999999999 8
17.7272727272727 9
4.49999999999999 10
82.5217391304348 11
272.954545454545 12

50.4182495242278
 0.1 0.2 0.3 0.4
Sales Y Predict Error Squared ErrorPercentage Error
(1000's of Gallons)
17
21
19
23
18 20.8 -2.8 7.84 -15.55555555556
16 20 -4 16 -25
20 18.3 1.7 2.89 8.5
18 18.7 -0.7 0.49 -3.888888888889
22 18.2 3.8 14.44 17.27272727273
20 19.8 0.2 0.04 1
115 20.2 94.8 8987.04 82.4347826087
22 58.2 -36.2 1310.44 -164.5454545455

MSE 1292.3975
MAPE
RMSE 35.949930459

Accuracy 60.22532389108
Absolute Percentage Error

15.5555555555556
25
8.5
3.88888888888889
17.2727272727273
0.999999999999996
82.4347826086956
164.545454545455

39.7746761089152