a) Without Marketing Campaign
Month Visits Visits with purchases Revenue ($) Expenses ($) Profit/Loss ($)
1 10000 100 $3,368.52 $2,021.11 $1,347.41
2 10500 105 $3,536.94 $2,122.16 $1,414.78
3 11025 110 $3,705.37 $2,223.22 $1,482.15
4 11576 116 $3,907.48 $2,344.49 $1,562.99
5 12155 122 $4,109.59 $2,465.75 $1,643.84
6 12763 128 $4,311.70 $2,587.02 $1,724.68
7 13401 134 $4,513.81 $2,708.29 $1,805.52
8 14071 141 $4,749.61 $2,849.76 $1,899.84
9 14775 148 $4,985.40 $2,991.24 $1,994.16
10 15514 155 $5,221.20 $3,132.72 $2,088.48
11 16290 163 $5,490.68 $3,294.41 $2,196.27
12 17105 171 $5,760.16 $3,456.10 $2,304.06
13 17960 180 $6,063.33 $3,638.00 $2,425.33
14 18858 189 $6,366.49 $3,819.90 $2,546.60
15 19801 198 $6,669.66 $4,001.80 $2,667.86
16 20791 208 $7,006.51 $4,203.91 $2,802.60
17 21831 218 $7,343.36 $4,406.02 $2,937.35
18 22923 229 $7,713.90 $4,628.34 $3,085.56
19 24069 241 $8,118.12 $4,870.87 $3,247.25
20 25272 253 $8,522.34 $5,113.41 $3,408.94
21 26536 265 $8,926.57 $5,355.94 $3,570.63
22 27863 279 $9,398.16 $5,638.90 $3,759.26
23 29256 293 $9,869.75 $5,921.85 $3,947.90
24 30719 307 $10,341.34 $6,204.81 $4,136.54
Total $150,000.00 $90,000.00 $60,000.00
Average $6,250.00 $3,750.00 $2,500.00
b) Yes I will. As ABC is focused on boosting sales, although they incur losses in their first 7 months of operation; starting fr
the first month where the marketing campaign is implemented, ABC will see increases in visits with purchases and revenu
than without the marketing campaign. As such, although their initial start-up cost of $6000 is only enough to cover their
for the first 3 months (Figure 1.2) and they will have to take up a loan from the bank from the fourth month onwards, AB
should implement this marketing campaign as it will help them acheive their goal of boosting sales. This loan can easily b
off as ABC is able to turn a much higher profit in the later months of operation.
c) $33.69. Using Goal Seek, setting cell $D$27 (Total revenue without marketing campaign) to $150000 by changing cell $
(Average sale per visit with purchase), the value of $33.69 is derived.
d)
Is profit with
Profit/Loss ($) marketing
Profit/Loss ($)
Month without better than
with marketing
marketing without
marketing
1 $1,347.41 -$2,000.00 Not better
2 $1,414.78 -$1,760.00 Not better
3 $1,482.15 -$1,500.00 Not better
4 $1,562.99 -$1,220.00 Not better
5 $1,643.84 -$920.00 Not better No. of months that profit with marketing be
6 $1,724.68 -$590.00 Not better 12
7 $1,805.52 -$240.00 Not better Percentage of months that profit with mark
8 $1,899.84 $140.00 Not better 50%
9 $1,994.16 $550.00 Not better
10 $2,088.48 $1,000.00 Not better
11 $2,196.27 $1,480.00 Not better
12 $2,304.06 $1,990.00 Not better
13 $2,425.33 $2,550.00 Better e)
14 $2,546.60 $3,160.00 Better
15 $2,667.86 $3,810.00 Better
Profit/Loss with
16 $2,802.60 $4,520.00 Better
17 $2,937.35 $5,280.00 Better $14,000.00
18 $3,085.56 $6,100.00 Better $12,000.00
19 $3,247.25 $6,990.00 Better $10,000.00
f(x) = 608.404347826087
20 $3,408.94 $7,950.00 Better $8,000.00
Profit/Loss
21 $3,570.63 $8,980.00 Better $6,000.00
22 $3,759.26 $10,100.00 Better
$4,000.00
23 $3,947.90 $11,310.00 Better
$2,000.00
24 $4,136.54 $12,610.00 Better
$0.00
1 6
-$2,000.00
-$4,000.00
Profit/Loss ($)
without marketing
Equation of line for profit with marketing:
y = 608.4x - 4259.6
y-intercept:
-4259.64
Slope:
608.40
"With marketing campaign" profit for month
$ 44,413.00
With Marketing Campaign
Visits Visits with purchases Revenue ($) Expenses ($) Profit/Loss ($)
10000 300 $7,500.00 $9,500.00 -$2,000.00
10800 324 $8,100.00 $9,860.00 -$1,760.00
11664 350 $8,750.00 $10,250.00 -$1,500.00
12597 378 $9,450.00 $10,670.00 -$1,220.00
13605 408 $10,200.00 $11,120.00 -$920.00
14693 441 $11,025.00 $11,615.00 -$590.00
15868 476 $11,900.00 $12,140.00 -$240.00
17137 514 $12,850.00 $12,710.00 $140.00
18508 555 $13,875.00 $13,325.00 $550.00
19989 600 $15,000.00 $14,000.00 $1,000.00
21588 648 $16,200.00 $14,720.00 $1,480.00
23315 699 $17,475.00 $15,485.00 $1,990.00
25180 755 $18,875.00 $16,325.00 $2,550.00
27194 816 $20,400.00 $17,240.00 $3,160.00
29370 881 $22,025.00 $18,215.00 $3,810.00
31720 952 $23,800.00 $19,280.00 $4,520.00
34258 1028 $25,700.00 $20,420.00 $5,280.00
36999 1110 $27,750.00 $21,650.00 $6,100.00
39959 1199 $29,975.00 $22,985.00 $6,990.00
43156 1295 $32,375.00 $24,425.00 $7,950.00
46608 1398 $34,950.00 $25,970.00 $8,980.00
50337 1510 $37,750.00 $27,650.00 $10,100.00
54364 1631 $40,775.00 $29,465.00 $11,310.00
58713 1761 $44,025.00 $31,415.00 $12,610.00
Total $500,725.00 $420,435.00 $80,290.00
Average $20,863.54 $17,518.13 $3,345.42
Table 1.2
rst 7 months of operation; starting from
s in visits with purchases and revenue How many months initial start-up cost lasts
$6000 is only enough to cover their losses Initial start-up cost Losses first 3 mths Losses first 4 mths
from the fourth month onwards, ABC 6000 -$5,260.00 -$6,480.00
boosting sales. This loan can easily be paid
paign) to $150000 by changing cell $P$8
months that profit with marketing better than without marketing:
age of months that profit with marketing better than without marketing:
Profit/Loss with marketing/without marketing
$14,000.00
$12,000.00
$10,000.00
f(x) = 608.404347826087 x − 4259.63768115942
$8,000.00
$6,000.00
$4,000.00
$2,000.00
$0.00
1 6 11 16 21
-$2,000.00
-$4,000.00
No. of months
Profit/Loss ($) Profit/Loss ($) Linear (Profit/Loss ($)
without marketing with marketing with marketing)
n of line for profit with marketing:
marketing campaign" profit for month 80, so when x=80
Is having marketing campaign better? (Y/N)
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Y
Table 1.1
Input parameters for without marketing and with marketing
With marketing Without marketing
Initial visitors 10000 10000
Growth rate 5% 8%
Buy conversion rate 1% 3%
Monthly marketing expenses $0 $5,000
Average sale ($) per visit with $33.69 $25
COGS (inclusive of other expenses) 60% of revenue 60% of revenue
Initial start-up cost ($) $6,000 $6,000
a) Code Description Unit Cost ($) Selling Price ($)
D01 Disinfectant spray $ 10.00 $ 14.90
H01 Hand Sanitizer $ 5.00 $ 8.90
M01 Mask (50 pcs) box $ 15.00 $ 28.90
T01 Tissue box $ 2.50 $ 4.50
Shipping charge <=$60 $ 60.00 $ 1.99
Decision model for sale
Code Description Unit Selling Price ($)
D01 Disinfectant spray $ 14.90
H01 Hand Sanitizer $ 8.90
M01 Mask (50 pcs) box $ 28.90
T01 Tissue box $ 4.50
Objective Max 17190
f) When D01=2 and H01=2,
Total charge inclusive of shipping ($)= $46.59
When D01=1 and M01=5, T02=2,
Total charge inclusive of shipping ($)= $153.40
g) Code Description Unit Cost ($)
M01 Mask (50 pcs) box $ 15.00
Profit $ 1,390.00
Profit margin 93%
i) PMT ($3,726.52)
Type 0
Rate 0.57333333333333
NPER 24
PV 6500
FV -20000
PMT ($3,726.52)
Quantity to buy Total Cost Profit per sale
100 $ 1,000.00 $ 4.90
200 $ 1,000.00 $ 3.90
800 $ 12,000.00 $ 13.90
2400 $ 6,000.00 $ 2.00
3500 $ 20,000.00
Quantity Total
$ -
$ -
100 $ 2,890.00
$ -
Total before discount $ 2,890.00
Discount ($) 15
Total charge after discount ($) $ 2,875.00
Shipping charge $ -
Total charge inclusive of shipping ($) $ 2,875.00
Selling Price ($) No.
$ 28.90 100
Remaning amount 13500
Month Amount paid Amount left
1 $ 500.00 $ 13,000.00
2 $ 500.00 $ 19,953.33
3 $ 500.00 $ 30,893.24
4 $ 500.00 $ 48,105.37
5 $ 500.00 $ 75,185.78
6 $ 500.00 $ 117,792.30
7 $ 500.00 $ 184,826.55
8 $ 500.00 $ 290,293.78
9 $ 500.00 $ 456,228.87
10 $ 500.00 $ 717,300.10
11 $ 500.00 $ 1,128,052.15
12 $ 500.00 $ 1,774,302.05
13 $ 500.00 $ 2,791,068.56
14 $ 500.00 $ 4,390,781.20
15 $ 500.00 $ 6,907,662.42
16 $ 500.00 $ 10,867,555.54
17 $ 500.00 $ 17,097,787.38
18 $ 500.00 $ 26,900,018.81
19 $ 500.00 $ 42,322,196.26
20 $ 500.00 $ 66,586,422.12
21 $ 500.00 $ 104,762,137.47
22 $ 500.00 $ 164,825,262.95
23 $ 500.00 $ 259,324,580.37
24 $ 500.00 $ 408,003,506.45
lump sum amount $ 408,003,506.45
total interest paid $ 407,983,506.45
Amount ($) Discount ($)
1 0
25 3
51 6
81 10
121 15
only one discount can be applied
Constraints
Quantity >100 per product
Quantity of Tissue:Mask =3:1
Quantity of Hand sanitizer:Spray =2:1
Total quantity <=5000
Maximise Profit
Initial start-up cost 20000
