Create a 4 sheet excel spreadsheet. With direct costs and operational costs of my businees. i.e an onlince grocery delivery platform.
the 2nd attachment is the question and the 1st and 3rd are examples of the fformat I would need it in. More information will be shared once we have discussed.
>Assumptions
n Assumptions
DOWN APPROACH. IN CASE YOUR BUSINESS IDEA IS BOTTOM-UP, PLEASE MAKE ADJUSTMENTS
Assumptions – New Location in Boston Common
and we have two people for selling and two people for preparing the orders. Each costumer buys based on a gaussian dirstribution a mix of 2 products (actually 2,5, but we assume a worse case scenario). This leads to a maximal possible amount of sold products. In reality 0% of workload during a day and selling the maximum possible amount are not possiple, therefore we assume that in the beginning we will sell low volumes that grow to at the end of the first year. In the second year we assume our sold volume will sharply increase to 6 . Our products , , and coffee are sold during the whole year, and hot we only serve in the cold months lasting from october to march. As especially ice cream is affected by seasonal trends we add this effects to our calculation.
min / – 3 min full year e
full year 00
2 00
s
can’t be only based on the competitors’ prices, because they are operating in other countries with lower food costs. Therefore we asked ourselves what we were willing to pay for these products. Considering the direct costs as well, the price structure for all products is shown in the table. As the price for the tredelnik is higher than the stroopwaffles’s price, we assume that we will sell more stroopwaffles.
Price – 3.50 2.50 4.00 – 3.00 – 5.00 rates:
-> 3
-> 4
-> 5
we will have a sharp growth in sold volumes, because the maximum scalability is reached very fast in a shed.
2% Assumptions
(yearly)
.00
https://www.shopkeep.com/blog/how-much-does-it-cost-to-start-a-food-truck#step-1
.00
5. year of our revenues for advertising and promotion in the first year. In the advertising expenses are website maintainance, google listing, facebook/Instagram advertising and public advertising included. In the following years we will reduce these expenses due to our gained popularity.
0 0 0 0 0 .00
(yearly)
7 part time workers. Each of them will be trained in sale and product preparation. We will pay them 13$/h. As we open a second shop we will have the same personal structure over there.
.00
(yearly)
00$ monthly rent fees, also for the second location. Local rent prices are around 2500$. As we rent only a spot, we subtracted 500$.
.00
.00
$ 600.00 we calvulated an average rate excluding the costs mentioned before.
costs 1. month
.00
$ 500.00 .00
Year 2 Year 3 Year 4 Revenue
22.0% 22.0% 22.0% 22.0% $ 24,000 $ 24,000 $ 48,000 $ 48,000 $ 327,600 $ 327,600 $ 960 $ 960 $ 1,500 $ 2,800 $ 2,800 November-March full year 9h= 540 min 2 sales people and 2 preparing people Potential costumers per day: 360 Each costumer buys two products 2 March Mai November Total 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 30% 40% 45% 50% 51% 52% 1. year
6,480 8,640 9,720 10,800 10,800 11,232 Maximum amount 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 21,600 259,200 60% 60% 60% 60% 61% 12,960 12,960 12,960 12,960 13,176 40% 45% 20% 5% 5% 32% 30% 35% 32% 25% 25% 30% 30% 28% 25% 30% 28% 20% 25% 25% 15% 15% 15% 15% 15% 15% 15% 15% 20% 20% 10% 10% 10% – – – – – – 10% 10% 10% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% January February March April Mai June July August September Oktober November December Total 324 562 2,074 3,370 1,814 1,944 2,268 2,808 2,808 972 1,296 1,458 1,620 2,246 648 756 1,123 2,592 659 3,370 3,240 3,953 2,592 3,294 1,944 1,944 1,944 1,944 2,074 2,635 1,231 1,296 1,318 Seasonal Product Sales in % volume
Ice cream January February March April Mai June July August September Oktober November December 0.05 0.05 0.1 0.2 0.25 0.35 0.4 0.45 0.38 0.2 0.05 0.05 Stroopwaffle January February March April Mai June July August September Oktober November December 0.32 0.32 0.3 0.35 0.32 0.27 0.25 0.22 0.26 0.25 0.3 0.3 Tredelnik January February March April Mai June July August September Oktober November December 0.28000000000000003 0.28000000000000003 0.25 0.3 0.28000000000000003 0.23 0.2 0.18 0.21 0.19 0.25 0.25 Coffe January February March April Mai June July August September Oktober November December 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.15 0.16 0.2 0.2 Hot chocolate January February March April Mai June July August September Oktober November December 0.1 0.1 0.1 0 0 0 0 0 0 0.1 0.1 0.1 Apple Cider January February March April Mai June July August September Oktober November December 0.1 0.1 0.1 0 0 0 0 0 0 0.1 0.1 0.1 Revenue 4% Price value – $ 3.50 $ 2.50 $ 4.00 – $ 3.00 – $ 5.00 Year 1 Year 2 Year 3 Year 4 Year 5 $ 373,559 $ 505,185 $ 898,952 $ 1,046,339 $ 1,077,370 Revenue Stroopwaffle Tredelnik Coffee Hot chocolate Apple cider 703076923076924 0.43550000000000005 1.2 000000000000007 00000000000001 0.7 GOGS
Ice cream Stroopwaffle Tredelnik Coffee Hot chocolate Apple cider 3.5 2.5 4 3 3.5 5 for 1 basin
costs per unit total s
10 2 $0.46 2 1 $1.42 1 $11.39 0.5 costs per unit Pfaller: Julia Pfaller: costs per unit total cake
$0.05 $1.20 $0.05 total $0.14 $0.05 3 total Costs Year 1 Year 2 Year 3 Year 4 Year 5 $ 82,131 $ 111,289 $ 197,872 $ 230,502 $ 237,338 $ 1,000 $ 1,000 $ 1,200 $ 1,200 $ 400 $ 400 $ 600 $ 600 $ 600 10% 5% 4% 4% 3% $ 37,356 $ 25,259 $ 35,958 $ 41,854 $ 32,321 $ 163,800 per year Break even January February March April Mai June July August September Oktober November December January February March April June July August September November December $ 1,134 $ 1,966 $ 5,184 $ 7,560 $ 8,424 $ 7,258 $ 7,560 $ 7,776 $ 11,232 $ 2,916 $ 3,888 $ 4,374 $ 4,860 $ 6,739 $ 2,268 $ 2,646 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ 3,931 $ 3,240 $ 3,780 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ 5,616 $ 22,000 $ 37,908 $ 185 $ 320 $ 903 $ 1,467 $ 2,177 $ 2,268 $ 2,333 $ 3,370 $ 807 $ 1,076 $ 1,210 $ 1,345 $ 1,865 $ 395 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ 685 $ 454 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ 786 $ 4,921 $ 8,493 $ 1,230 $ 2,123 $ 4,921 $ 8,493 $ 15,000 $ 17,500 Break even analysis 1. year
Revenue 0 January February March April May June July August September October November December 0 21999.599999999999 21999.599999999999 25704 27864 28036.800000000003 31784.400000000001 31833 35586 35316 37619.64 37908 37908 Costs 0 January February March April May June July August September October November December 17500 19930.034912820513 22390.411158974359 22739.25822051282 23523.143820512822 23921.248020512823 24164.972658974359 24366.925805128205 24620.861012820511 25026.393743589746 25221.240905128205 25653.052320512819 25962.908266666665 Difference (17.500) 2.070 (391)
0 January February March April May June July August September October November December 17500 2069.565087179486 390.8111589743603 2964.7417794871 799 4340.8561794871785 4115.5519794871798 7619.4273410256428 7466.074194871795 10965.138987179489 10289.606256410254 12398.399094871795 12254.947679487181 11945.091733333335 Price .00
$ 4,000.00 $ 17,891 = $ 4,000 $ 4,000 Robert Julia Courtney Luke
Robert Julia Courtney Luke Our ask 7000 4000 4000 4000 52000
2
PROJECT NAME
Mai
PLEASE, USE THIS EXCEL FILE AS EXAMPLE TO PREPARE YOUR OWN
THE EXAMPLE IS ON A TOP
–
Names of Team Members/Promoters
General Assumptions
Currency:
USD
Excel Model:
Inputs in blue
Calculations in black
Approach:
Bottom up approach
BP Period:
We used 5 years to ensure a sufficient forecast period with a second location in the third year
Revenue
Year
3
We plan to open a location in Boston Common in the third year. We assume that based on the characteristics of the new location we can achieve a similar cost structure as in our first shed.
Bottom up approach
We assume that our shop is open 9h a day/ 7 days per week. For serving one costumer we need 3
min
1
0
51%
2%
ice
cream
Stroop
waffle
Tredelnik
apple cider
chocolate
Product
Offer
Opening hours:
9h
=
540
Ice cream
full year
2 sales people and 2 preparing people
Apple cider
Hot chocolate
November
March
Time to serve 1 costumer:
Sweet food
Potential costumers per day:
360
Coffe
Potential costumers per month assuming full workload:
10
8
Each costumer buys two products
Maximum possible amount of products sold per month
2
16
Price
We price the standard products coffee, ice cream, hot chocolate and apple cider in comparison to our local competitors. The pricing for our differentiated products Tredelnik and
Stroopwaffle
Competitor
Price value
Final price
Ice cream
3.50
Stroopwaffle
2.00
2.50
Tredelnik 3.50
4.00
Coffe
3.00
Hot chocolate 3.50 – 3.50
Apple cider
5.00
Growth
Year 2
Year 3
Year 4
We assume that for the 3.,4. and
5. year
4%
3%
Growth in sold volumes
Costs
Direct cost
For calculating the direct costs of our products we assume the average supermarket prices in America. For easier calculation we don’t have direct personal costs.
Utilities
We assume that the utilities like electricity, water and heating are 2$/sqm a month. Our operating space is 256 sqm.
$ 480
Insurance
We assume that our insurance costs will be 600$ a year. We compared the prices with a food truck insurance
$ 600
1. year
2. year
3. year
4. year
Promotional and advertising costs
per year
We plan to give our costumers a discount card. If they buy 10 times something in our shed the 11. food order will be for free. We plan also several promotions during the year for special occasions. We will spend
10%
Permit from Boston
For the permission to operate in the central area we have costs of 500$ per year.
$ 500
Wages
We assumed that we would have in
total
$ 163,800
Our Salary
We assumed that we pay ourself no salary, because it is our dream and our idea which should not affected our project in the first 5 years.
Rent
We will own the shed but rent a central spot from a company, therefore we assume
20
$ 24,000
1.-2. year
3.-5. year
Logistic
We will work with retailers to get the ingredients for our products delivered. Therefore we assume yearly delivery costs of 400$. As our second shop is in a different location the delivery costs increase to 600$ in the third year.
$ 400
Further assumptions
Break even
We assumed that we have to pay some marketing costs, the rent and the permission from Boston upfront before starting to operate. For the
SGA
Marketing
$ 15,000
Permission
Rent
$ 2,000
P&L
PROJECT NAME
Profit and Loss
Year 1
Year 5
Total
$ 373,559
$ 505,185
$ 898,952
$ 1,046,339
$ 1,077,370
COGS
$ 82,131
22.0%
$ 111,289
$ 197,872
$ 230,502
$ 237,338
SGA
Rent
$ 48,000
Marketing
$ 37,356
$ 25,259
$ 35,958
$ 41,854
$ 32,321
Wages $ 163,800 $ 163,800
$ 327,600
Utilities $ 480 $ 480
$ 960
Other costs
$ 1,500
$ 2,800
Total SGA
$ 227,136
60.8%
$ 215,039
42.6%
$ 415,318
46.2%
$ 421,214
40.3%
$ 411,681
38.2%
Total Expenses
-309,267
-326,328
-613,190
-651,715
-649,019
EBITDA
$ 64,292
17.2%
$ 178,857
35.4%
$ 285,762
31.8%
$ 394,624
37.7%
$ 428,351
39.8%
add_calculations_revenue
Sales stream
Product Offer
Ice cream full year
Apple cider/ Hot chocolate
Sweet food full year
Coffee
Calculation for sold volume
Opening hours:
11 a.m. – 8 p.m.
Time to serve 1 costumer: 3 min Potential costumers per month assuming full workload:
10,800
Maximum possible amount of products sold per month
21,600
Sold volume 1. year
January
February
April
June
July
August
September
Oktober
December
Maximum amount
259,200
Percentage sold
30%
3
5%
40%
45%
50%
52%
Volume
6,480
7,560
8,640
9,720
11,016
11,232
112,320
Sold volume 2. year
Percentage sold 52%
55%
57%
58%
59%
60%
61%
11,232
11,880
12,312
12,528
12,744
12,960
13,176
151,848
Sales structure in % of volume (every year):
January February March April Mai June July August September Oktober November December
Ice cream 5% 5% 10%
20%
25%
35%
38%
Stroopwaffle
32%
27%
22%
26%
Tredelnik
28%
23%
18%
21%
19%
Coffe
15%
16%
Hot chocolate 10% 10% 10% – – – – – – 10% 10% 10%
Apple Cider
Total
100%
Sold volumes for each product (1. year)
Ice cream
324
756
1,728
2,160
3,402
3,888
4,860
4,104
2,203
562
24,872
Stroopwaffle
2,074
2,268
3,024
2,765
2,624
2,430
2,376
2,808
2,754
3,370
31,936
Tredelnik
1,814
1,890
2,592
2,419
2,236
1,944
2,093
26,631
Coffe
972
1,134
1,296
1,458
1,620
1,763
2,246
18,081
Hot chocolate
648
1,102
1,123
5,400
Apple Cider 648 648 756 1,102 1,123 1,123 5,400
Total 6,480 6,480 7,560 8,640 8,640 9,720 9,720 10,800 10,800 11,016 11,232 11,232 112,320
Sold volumes for each product (2. year)
Ice cream 562
594
1,231
2,506
3,186
4,536
5,184
5,832
4,925
659
32,465
Stroopwaffle
3,594
3,802
3,694
4,385
4,078
3,499
3,240
2,851
3,953
43,658
Tredelnik
3,145
3,326
3,078
3,758
3,568
2,981
2,333
2,722
2,462
3,294
36,554
Coffe
1,685
1,782
1,847
1,879
1,912
2,635
24,224
Hot chocolate 1,123
1,188
1,318
7,474
Apple Cider 1,123 1,188 1,231 1,296 1,318 1,318 7,474
Total 11,232 11,880 12,312 12,528 12,744 12,960 12,960 12,960 12,960 12,960 13,176 13,176 151,848
Revenue calculation
Volume
Year 1 Year 2 Year 3 Year 4 Year 5
Growth 4% 3% 2%
Sold volumes in the first shop:
Ice cream 24,872 32,465
33,763
34,776
35,472
Stroopwaffle 31,936 43,658
45,404
46,766
47,702
Tredelnik 26,631 36,554
38,016
39,156
39,939
Coffe 18,081 24,224
25,193
25,949
26,468
Hot chocolate 5,400 7,474
7,773
8,006
8,166
Apple cider 5,400 7,474 7,773 8,006 8,166
Sold volumes in the second shop:
Ice cream 24,872 32,465 33,763
Stroopwaffle 31,936 43,658 45,404
Tredelnik 26,631 36,554 38,016
Coffe 18,081 24,224 25,193
Hot chocolate 5,400 7,474 7,773
Apple cider 5,400 7,474 7,773
Price
Competitor (average)
final price
Ice cream
$ 3.50
Stroopwaffle
$ 2.00
$ 2.50
Tredelnik $ 3.50
$ 4.00
Coffe
$ 3.00
Hot chocolate $ 3.50 – $ 3.50
Apple cider
$ 5.00
Revenue
Ice cream
$ 87,053
$ 113,627
$ 205,225
$ 235,344
$ 242,323
Stroopwaffle
$ 79,839
$ 109,145
$ 193,350
$ 226,061
$ 232,765
Tredelnik
$ 106,523
$ 146,215
$ 258,586
$ 302,840
$ 311,821
Coffe
$ 54,244
$ 72,673
$ 129,824
$ 150,521
$ 154,985
Hot chocolate
$ 18,900
$ 26,158
$ 46,104
$ 54,178
$ 55,784
Apple cider
$ 27,000
$ 37,368
$ 65,863
$ 77,397
$ 79,692
Total revenue
Ice cream
0.5
0.83
0.61
Direct costs calculation
Summary
Direct
costs per unit
Ice cream
$
0.57
Stroopwaffle
$
0.44
Tredelnik
$
1.20
Coffee
$0.83
Hot chocolate
$0.61
Apple cider
$
0.70
Direct costs calculation Ice cream
quantity
egg
$0.46
$4.60
sugar
$0.23
corn starch
$0.14
$0.28
vanilla extract
$1.42
milk
$11.39
heavy cream
$14.24
$7.12
ice 0.5
$1.50
$0.75
one basin (65 scoops)
$26.02
costs per unit
$0.40
additional costs:
cone/
cup
$0.10
nap kin
$0.02
waffle
$0.05
Final direct costs for ice cream
Julia
Based on a survey for personal preferences
Ice cream 0.57
Direct costs Stroopwaffle
quantity for 20
fresh
yeast
1 oz
$2.50
tablespoon milk
1 tablespoon
butter
8 oz
caster sugar
7 oz
$0.07
egg 1 $0.46
cake
flour
8,8 oz
$2.30
Dutch molasses
syrup
4,6 oz
$1.68
cinnamon
teaspoon
nap kin 20 $0.40
20 Stroopwaffles
$8.71
costs per unit $0.44
Direct costs Tredelnik
quantity for 8
flour
4,25 oz
margarine
2,6 oz
$0.33
cream
8 ounces
$1.30
yeast
1,5 oz
$3.75
powder sugar
taplespoon
eggs
$1.38
shaved almonds for dusting
1
package
$2.00
package 8
$0.65
8 Tredelnik
$9.60
costs per unit $1.20
Direct costs coffee
total
milk
$0.21
beans
$0.22
cups
$0.20
syrup $0.20
costs per unit $0.83
Direct costs hot chocolate
milk $0.21 chocolate $0.20
cup $0.20
costs per unit $0.61
Direct costs apple cider
quantity costs per unit total
apple cider
1 gallon
$8.00
cups 16 $0.20
$3.20
1 gallon apple cider (16 cups)
$11.20
costs per unit 0.70
COGS and SGA
Costs
Year 1 Year 2 Year 3 Year 4 Year 5
Total sold volumes
Ice cream 24,872 32,465
58,636
67,241
69,235
Stroopwaffle 31,936 43,658
77,340
90,424
93,106
Tredelnik 26,631 36,554
64,646
75,710
77,955
Coffe 18,081 24,224
43,275
50,174
51,662
Hot chocolate 5,400 7,474
13,173
15,479
15,938
Apple cider 5,400 7,474 13,173 15,479 15,938
Direct costs of product
Ice cream 0.57
Stroopwaffle 0.44
Tredelnik 1.20
Coffee 0.83
Hot chocolate 0.61
Apple cider 0.70
COGS
Ice cream
$ 14,185
$ 18,515
$ 33,440
$ 38,348
$ 39,485
Stroopwaffle
$ 13,908
$ 19,013
$ 33,681
$ 39,380
$ 40,548
Tredelnik
$ 31,957
$ 43,864
$ 77,576
$ 90,852
$ 93,546
Coffee
$ 15,008
$ 20,106
$ 35,918
$ 41,644
$ 42,879
Hot chocolate
$ 3,294
$ 4,559
$ 8,035
$ 9,442
$ 9,722
Apple cider
$ 3,780
$ 5,232
$ 9,221
$ 10,836
$ 11,157
Total GOGS
Selling, General and Administative expenses
(new shed doubles expenses)
Rent $ 24,000 $ 24,000 $ 48,000 $ 48,000 $ 48,000
Permission $ 500 $ 500
$ 1,000
Insurance $ 600 $ 600
$ 1,200
Utilities $ 480 $ 480 $ 960 $ 960 $ 960
Logistics
Wages $ 163,800 $ 163,800 $ 327,600 $ 327,600 $ 327,600
% advertising on revenue
Advertising and Promotion
Total SGA $ 227,136 $ 215,039 $ 415,318 $ 421,214 $ 411,681
Rent $ 24,000
Utilities $ 480
Salary for 1 shed:
13$/h with 9h operating hours, 350 opening days and 4 workes per shift
Volumes
Ice cream 324 324 756 1,728 2,160 3,402 3,888 4,860 4,104 2,203 562 562
Stroopwaffle 2,074 2,074 2,268 3,024 2,765 2,624 2,430 2,376 2,808 2,754 3,370 3,370
Tredelnik 1,814 1,814 1,890 2,592 2,419 2,236 1,944 1,944 2,268 2,093 2,808 2,808
Coffe 972 972 1,134 1,296 1,296 1,458 1,458 1,620 1,620 1,763 2,246 2,246
Hot chocolate 648 648 756 1,102 1,123 1,123
Apple Cider 648 648 756 1,102 1,123 1,123
Price Direct costs of product
Ice cream $ 3.50 Ice cream 0.57
Stroopwaffle $ 2.50 Stroopwaffle 0.44
Tredelnik $ 4.00 Tredelnik 1.20
Coffe $ 3.00 Coffee 0.83
Hot chocolate $ 3.50 Hot chocolate 0.61
Apple cider $ 5.00 Apple cider 0.70
Year beginning (0)
May
October
Ice cream
$ 1,134
$ 2,646
$ 6,048
$ 7,560
$ 11,907
$ 13,608
$ 17,010
$ 14,364
$ 7,711
$ 1,966
Stroopwaffle
$ 5,184
$ 5,670
$ 6,912
$ 6,561
$ 6,075
$ 5,940
$ 7,020
$ 6,885
$ 8,424
Tredelnik
$ 7,258
$ 10,368
$ 9,677
$ 8,942
$ 7,776
$ 9,072
$ 8,372
$ 11,232
Coffe
$ 2,916
$ 3,402
$ 3,888
$ 4,374
$ 4,860
$ 5,288
$ 6,739
Hot chocolate
$ 2,268
$ – 0
$ 3,856
$ 3,931
Apple Cider
$ 3,240
$ 5,508
$ 5,616
Total $ – 0
$ 22,000
$ 25,704
$ 27,864
$ 28,037
$ 31,784
$ 31,833
$ 35,586
$ 35,316
$ 37,620
$ 37,908
COGS January February March April Mai June July August September Oktober November December
Ice cream
$ 185
$ 431
$ 985
$ 1,232
$ 1,940
$ 2,217
$ 2,772
$ 2,341
$ 1,257
$ 320
Stroopwaffle
$ 903
$ 988
$ 1,317
$ 1,204
$ 1,143
$ 1,058
$ 1,035
$ 1,223
$ 1,199
$ 1,467
Tredelnik
$ 2,177
$ 3,110
$ 2,903
$ 2,683
$ 2,333
$ 2,722
$ 2,512
$ 3,370
Coffe
$ 807
$ 941
$ 1,076
$ 1,210
$ 1,345
$ 1,463
$ 1,865
Hot chocolate
$ 395
$ 461
$ 672
$ 685
Apple Cider
$ 454
$ 529
$ 771
$ 786
Total
$ 4,921
$ 5,618
$ 6,489
$ 6,415
$ 6,976
$ 6,819
$ 7,484
$ 7,630
$ 7,874
$ 8,493
Delivery 4 times per month
$ 1,230
$ 1,405
$ 1,622
$ 1,604
$ 1,744
$ 1,705
$ 1,871
$ 1,907
$ 1,968
$ 2,123
15 days to pay invoice
Money to pay first month
$ 2,460
$ 5,270
$ 6,053
$ 6,452
$ 6,695
$ 6,897
$ 7,151
$ 7,557
$ 7,752
$ 8,183
Costs to pay upfront in the first month
Marketing costs 1. month
Permission $ 500
Rent $ 2,000
Average SGA for feb-dec
$ 17,470
0 January February March April May June July August September October November December
Total costs
$ 17,500
$ 19,930
$ 22,390
$ 22,739
$ 23,523
$ 23,921
$ 24,165
$ 24,367
$ 24,621
$ 25,026
$ 25,221
$ 25,653
$ 25,963
Difference (Break even)
$ 2,070
$ 391
$ 2,965
$ 4,341
$ 4,116
$ 7,619
$ 7,466
$ 10,965
$ 10,290
$ 12,398
$ 12,255
$ 11,945
Money needed to cover the negative period
$ 17,891
CAPEX
Investment
Shed (adjusted to our needs)
$ 15,000.00
Ice cream 3 in one machine
$ 20,000.00
Show desk
$ 8,000.00
Stroopwaffle maker
2=150$
$ 300.00
Tredelnik oven
2.000$
$ 4,000
Kitchen equipment
$ 2,000.00
Coffe maschines
2×2.000$
Total
$ 53,300.00
NWC to cover
Investment and NWC
$ 71,191
$ 71,000
Our Investment
Robert
$ 7,000
Julia $ 4,000
Courtney
Luke
Total
$ 19,000
Our ask
$ 52,000
Sources
Sources:
https://eatyourworld.com/destinations/europe/the_netherlands/amsterdam/what_to_eat/stroopwafel
http://norfolkdailynews.com/lite_rock/programs/dave_williams/most-popular-ice-cream-flavors/article_8e46a544-6a88-11e8-a65b-2bb11e94b092.html
http://norfolkdailynews.com/lite_rock/programs/dave_williams/most-popular-ice-cream-flavors/article_8e46a544-6a88-11e8-a65b-2bb11e94b092.html
https://www.dairyscience.info/index.php/ice-cream/218-ice-cream-startup.html
>Assumptions
:
years to ensure a sufficient forecast period with a second location in the third year
Assumptions – New Location in Rome
,4% of the total population in Milan; Target age is 5 – 64. Total population of Milan is 1,36 Mio.
with Age Structure of Italy
– 14
– 24
%
– 54
10% 75.0% 0% and Target Percentage
.00%
14 (http://www.indexmundi.com/italy/demographics_profile.html)
Year 3 000%
%
00%
Price
0 €
per minute. In addition there are 2 zones, city center and suburban areas. (Price calculation uses the factor 1.4 for city center/ 1 for suburban areas). There will be a sign-up fee of 29.99€ per registration (Not in the first two weeks).
0.10 € % in suburban areas.
Assumptions In additionto this the average is about 0
charges, a new one costs €200. We assume we can drive 50 which leads to a cycle costs per 100km of €0.80. This results in a total of € 0.
renting
costs
0
per scooter per year which covers every accident and spares but accounts for the deductible of the users.
. clothing item to purchase from low end to designer would be around €200.00.
Year 2 Year 3 Year 4 Year 5 .00
.00
.00
100.0% 100.0% 100.0% 100.0% .5%
.00
Revenue Revenue 1,359,905 1,359,905 1,359,905 1,359,905 4.4% 5.2% 7.4% 10% 2,864,731 2,864,731 11,902 12,867 10 1.4 1 0.1 1.4 1 Costs Year 1 Year 2 Year 3 Year 4 Year 5 5% 0.8 100 0.8 0.8 0.8 0.8 0.8 1.6 ,000
2000 2000 2000 2000 200 300 400 150 150 150 150 150 800 900 0 15000 0 0 20000 20000 20000 20000 35000 20000 20000 20000 0
10000 10000 10000 10000 25 15 40 10 10 805000 20 20 40 40 40 24000 24000 24000 24000 480000 960000 960000 0 0 0 0 50000 5000 5000 5000 5000 15000 15000 30000 30000 1000 1000 2000 2000 2000 300 300 600 600 600 Year 1 Year 2 Year 3 Year 4 Year 5 3,600 3,600 3,600 3,600 3,600 € 5,000.00 € 5,000.00 € 5,000.00 € 5,000.00 10 10 30 5 4 200 Driving Price
EBITDA
Year 1 Year 2 Year 3 Year 4 Year 5 -434162.36208559992 -19775.828963328153 -43089.229473370593 524990.31319889892 1013769.0485493522 II ASSIGNMENT P&L FINANCIAL PROJECTIONS ON EXCEL UNTIL EBITDA SEND YOUR EXCEL FILE BY EMAIL BY MAY 19TH IT WILL BE VALUED –> 20% WEIGHT ON TOTAL GRADE OF THE COURSE BUILD YOUR MODEL ON EXCEL FOLLOWING THE GIVEN EXAMPLES, AS FOLLOWS
Direct Costs
Labor wages Delivery vehicles Storage unit on delivery unit (Hot Box) Parking charges Fuel charge for delivery Payroll taxes for labor Transaction cost
Operative Costs
Salary to management executives Office rent Research and Development Insurance on the vehicle Insurance on office Utilities Marketing and Promotion costs Payroll taxes for executives MIDTERM ASSIGNMENT BY :- ROHAN KHEMKA Online grocery delivery DEBT FINANCIAL INSTITUITIONS DEBT FINANCIAL INSTITUTIONS IN my region DOCUMENTS REQUIRED FOR Acquiring LOANS ‘The one’ Equity capital investors Equity Capital Investors in My region Why fundraising and ‘the one’ for us Thank you
PROJECT NAME
Main Assumptions
PLEASE, USE THIS EXCEL FILE AS EXAMPLE TO PREPARE YOUR OWN
THE EXAMPLE IS ON A TOP-DOWN APPROACH. IN CASE YOUR BUSINESS IDEA IS BOTTOM-UP, PLEASE MAKE ADJUSTMENTS
Names of Team Members/Promoters
General Assumptions
Currenc
y
Euro
Excel Model:
Inputs in blue
Calculations in black
Approach:
Top Down Approach (from the market)
BP Period:
We used
5
Revenue
Year 3
We plan to open a location in Rome in the third year. We assume that based on the characteristics of the traffic and infrastructure that we can achieve a similar growth profile as in Milan.
Target Population
We assume that in the first year we can capture
4
1
Age Group
# Demographics Profile Italy
# Driver licence
Target %
Population of Milan
Age
0
13.8%
0%
Age
15
9.8%
70.0%
10
Age
25
43.0%
75.0%
Age 55 – 64
12.4%
5%
Age 65 +
21.0%
Total Population of Milan
100
4.4%
Data Source: Italy Demographics Profile
20
Year 2
Year 4
Year 5
Increase in Target Percentage
We assume the target percentage to increase as follows:
0.8
1.0000%
1.
200
1.4
Driving
We assume the average subscription price would be
0.1
price per min.
price per sign-up
29.99 €
Drives per scooter per day
We assume 25 drives per scooter per day with 60% of all drives in the city center and
40
Costs
Direct cost
Due to the use of electic scooters we have relatively high energy expenses. The average price per kWh is €0.20. Per 100km our scooter need 4 kWh, which leads to an energy expense per 100km of €0.
80
life cycle per battery
50
km per charge
1.6
Station
We assumed that we pay €10,000 per station. Our stations are located in the whole city, mostly on sidewalks. We not assumed that the city of Milan provides us rentfree, but in other countries we know that there is support for “environmental-transport companies”. (This is why we assume reduced rental fees). At the stations the clients can change charged batteries against empty ones.
Insurance
We assumed that we would have insurance costs of €
30
Promotional Costs per year
We based the amount we will spend to promote the company the assumtions of giving away free months of sign-up fees for each €29,99 and social media advertisment. In year 3 we will expand to more cities. Therefore our social media advertisement will double.
Other Costs
We assumed that the average leasing cost for a electro scooter will be around €2,500 (Including a discount due to bulk orders and visibility). Due to the order size we expect to be able to bring down the cost per scooter to €2,000. We order 500 scooter in year 1; additional 300 in year 2 and from year 3-5 addional 200 new scooters per year. Furthermore we assume, that we need two helmets per scooter, which have average leasing costs of €
150
Create Website
We assume that it will cost €30,000.00 to create our website and €5,000.00 for expenses to update and maintain the website for following years
Wages
We assumed that we would have in total 20 workers. 12 as mechanics, 5 in the client-service department, 1 financial officer and 2 admistrative worker for marketing and hr. We assume to pay a average wage of 24.000€ per year.
Our
Salary
We assumed that we pay ourself no salary, because it is our dream and our idea which should not affected our project in the first 5 years.
Technology Set-up costs
We assumed that we have €100.000 of technology set-up costs to let our system work well. This amaount is similar with the amount BMW (x-drive); cartogo and enjoy payed. Once the technology is setted, there will no following costs.
P&L
PROJECT NAME
Profit & Loss
Profit and Loss
Amounts in Euro
Year 1
Revenue
Revenue from drives
892,
800
71.4%
1,339,200.00
77.6%
1,562,
400
5
5.2%
1,785,
600
54.7%
2,008,800.00
50.9%
Revenue from sign-ups
356,937.64
28.6%
385,884.17
22.4%
1,268,076.77
44.8%
1,476,269.51
45.3%
1,941,133.85
49.1%
Total Revenue
1,249,737.64
100.0%
1,725,084.17
2,830,476.77
3,261,869.51
3,949,933.85
Expenses
Direct Costs
552,600.00
44.2%
798,560.00
46.3%
965,966.00
34.1%
1,064,279.20
32.6%
1,198,564.81
30.3%
Operating Expenses
1,131,300.00
90
946,300.00
54.9%
1,907,600.00
6
7.4%
1,672,600.00
51.3%
1,737,600.00
44.0%
Total Expenses
-1,683,
900
-134.7%
-1,744,860.00
-101.1%
-2,873,566.00
-101.5%
-2,736,879.20
-83.9%
-2,936,164.81
-74.3%
EBITDA
-434,162.36
-34.7%
-19,775.83
-1.1%
-43,089.23
-1.5%
524,990.31
16.1%
1,013,769.05
25.7%
PROJECT NAME Revenue
Year 1 Year 2 Year 3 Year 4 Year 5
Total Population of Milan
1,359,905
Subscription Target Population
6.2%
8.8%
Total Target Population of VespR Milan
59,509
70,389
83,988
100,307
119,345
Percentage of Sign-Ups
20.0%
22.0%
24.2%
26.6%
29.3%
Total Number of new Sign-ups in Milan
11,902
12,867
17,211
22,120
28,470
Total
Population of Rome
2,864,731
Subscription Target Population 4.4% 5.2% 6.2%
Total Target Population of VespR Rome
125,361
148,278
176,926
Percentage of Sign-Ups 20.0% 22.0% 24.2%
Total Number of new Sign-ups in Rome
25,072
27,105
36,257
Total Sign-Ups
42,283
49,225
64,726
Revenue from Sign-ups
356937.6379
385884.171
1268076.771
1476269.513
1941133.854
Revenue from drives
892800
1339200
1562400
1785600
2008800
Total Revenue
1249737.638
1725084.171
2830476.771
3261869.513
3949933.854
Driving Price
averag. Minutes per drive
zone factor
City center
Suburban
Price per min.
averg. Price per drive city center
averg. Price per drive suburban
percentage of drives in city center
0.6
percentage of drives in suburban
0.4
averg. price per drive
€ 1.24
PROJECT NAME Costs
Costs
Direct Costs
Driving
Energy expense per 100 km
0.84
0.882
0.9261
0.972405
cycle costs per 100 km
Total Energy expense per 100 km
1.64
1.682
1.7261
1.772405
Driven km per year
3,600
5,400,000
6,300,000
7,200,000
8,100,000
Total Driving Costs
57600
88560
105966
124279.2
143564.805
Leasing Motorbike
Average Leasing Cost per Motorbike
2000
Quantity of Item
350
450
Average Leasing Cost per (Helmets)
Quantity of Item 2 400 600
700
Total Cost Leasing Motorbike
4
60000
6
90000
80
5000
9
20000
10
35000
Advertisement
First Signup Bonus
15000
Social Media Ads and SEO
40000
Total Advertisement Costs
55000
Total Direct Cost
552600
798560
965966
1064279.2
1198564.805
Operating Expenses y
Station
Total Station Renting Costs per year
125000
200000
400000
4
50000
500000
Station setup costs per Station
1000
New Station
Total Costs Station
425000
355000
555000
605000
Salary
worker (Service, mechanic)
annual salary per worker
24000
Total Salary expense
480000
960000
Other costs
Technology set-up cost
100000
Create website and app
Service Station (garage)
30000
Insurance 300 60000 90000
105000
120000
135000
Electricty
Internet Service Station
Total Operating Expense
1131300
946300
1907600
1672600
1737600
Total Expenses
1683900
1744860
2873566
2736879.2
2936164.805
Additional Calculations
Scooters
Scooters 200 300 350 400 450
drives per year per scooter
Total drives per year
720,000
1,080,000
1,260,000
1,440,000
1,620,000
Stations
Station 25 40 80 90 100
Rent per Station per year
€ 5,000.00
Total Renting costs
€ 125,000.00
€ 200,000.00
€ 400,000.00
€ 450,000.00
€ 500,000.00
Drive Characteristics
averg. Drives per day per scooter
days per year
360
averg. Driving minutes
averg. Km/h in city
averg. Km per drive
Energy expense per 100 km
averg. kWh per 100 km
averg. Price per kWh
0.20 €
Cycle costs per 100km
Battery costs
life cycle per battery 500
km per charge 50
averag. Minutes per drive 10
zone factor 0
City center 1.4
Suburban 1
Price per min. 0.1
averg. Price per drive city center 1.4
averg. Price per drive suburban 1
percentage of drives in city center 0.6
percentage of drives in suburban 0.4
averg. price per drive € 1.24
–>
Chart Data
Year 1 Year 2 Year 3 Year 4 Year 5
EBITDA
-434162.3621
-19775.82896
-43089.22947
524990.3132
1013769.049
Sources
Sources:
costs electro scooter
http://www.elektroroller-aktuell.com/elektroroller-kosten.html
averg. Km/h in european cities
https://de.statista.com/statistik/daten/studie/37200/umfrage/durchschnittsgeschwindigkeit-in-den-15-groessten-staedten-der-welt-2009/
driving price
https://de.drive-now.com/en/#!/rates
creating website
http://www.websitebuilderexpert.com/how-much-should-a-website-cost/
Population of Milan
http://worldpopulationreview.com/world-cities/milan-population/
Population of Rome
http://worldpopulationreview.com/world-cities/rome-population/
Social media ads
https://de-de.facebook.com/business/learn/how-much-facebook-ads-cost
https://en.wikipedia.org/wiki/Milan
https://en.wikipedia.org/wiki/Rome
General Assumptions
Volumes
Price per unit
Variable cost per unit
Overhead costs (included sources)
Revenue for each product/service calculated from assumptions sheet:
Unit volume x price per unit
Cost for each product/service calculated from assumptions sheet:
Unit volume x variable cost (or COGS if any) per unit
Overhead (SG&A) costs
ASSUMPTIONS SHEET:
REVENUE SHEET:
COST SHEET:
INCOME STATEMENT (P&L) SHEET (OUTPUT) UNITIL EBITDA
4 SHEETS (more for charts or graphics)
In the current market with the situation the world has been put into, we see that everybody still needs their essentials. In India we can see that it is very difficult for most of the people to receive their daily fruits and vegetables. We see a huge gap in the demand vs the supply of how groceries are being delivered.
Our Business plan is to make delivery available to anyone and everyone within our region.
We even see, that a lot of people are trying to do all sorts of things to earn a living, especially now, since a lot of them have to feed their families. A huge sector of Indians work part time jobs, and are helpless at a time like this.
Our idea is to impact our nation in 2 parts.
Helping people get their essentials delivered to them at their homes.
Helping the economy grow by giving a chance to the people to earn their living at a time like this and even beyond.
We Plan to start this business on the east coast of India. Starting with Kolkata, and then expanding all over the the state, and then moving forward from there.
We are part of the E-commerce industry.
Debt Financial Institutions are those that lend you money for a period of time and charge a certain amount of interest on it.
There are multiple types of Debt Financial Institutions ;- Commercial Banks, Credit Unions, Savings/Loans Associations, finance companies and consumer finance companies.
India has a huge growing market for Start Ups, and has seen a lot of players shifitng from Equity based funds to Debt raising funds.
There are multiple debt financing institutions in India, but only a select few focusing on Startups.
The top few for me are :
Bajaj Finserv – www.bajajfinserv.in
Lending Kart. – www.lendingkart.com
IndiFi. – www.indifi.com
Alteria Capital. – alteriacapital.com
Sidbi – www.sidbi.in
Innoven Capital – www.innovencapital.com
When acquiring a loan, banks usually acquire a lot of documents to be sure that they would get their principal and their interest back.
When it comes to Startups, sometimes they do not have everything that could be required. For example: A Bank might require to see the last 12 months of revenue, but a startup of 6 months old may not have that. Thus a lot of Debt Financial institutions may require only certain things from start ups before providing them with funds.
Some of these documents are :-
Proof Of Identity
Address Proof
Age proof
Bank Statements : previous 6 months
Proof of Income
Signature Proof
IFSC code proof
Business Ownership Proof
Amongst all the Debt Financial institutions in India there is One that is most appropriate and convenient to me.
Bajaj Finserv – They are the largest Debt financers for startups in India, they are super flexible with their tenor repayment time, ranging from 12 to 60 months.
They have the fastest approval scheme i.e within 24 hours.
They send people to your doorstep to collect the documents.
Absolutely no collateral required.
And easy online account management.
Equity Capital Investors are those that buy out a percentage of a business. I.e they purchace a certain amonut of stock. It could be 5%, 10%, 25% and sometimes way more, depending on what the investor feels about the business, and how me he is willing to invest.
Equities that are bought on the stock market are called stock funds.
Equities that are bought privately are either called Venture Capital investments or Private Equity fund investments.
There are many equity capital Investors in India, they even include multinational firms that have a base in India, as there are a lot of Big Venture Caps that want to invest in India.
The ones that look interesting to my and my business are ;
Augment Ventures – http://www.augmentventures.in
Seeders. – https://www.seeders.in
Kolkata Angels. – https://angel.co/
kolkata
/investors
Sidbi Ventures. – https://www.sidbiventure.co.in
In today’s world, no startup can work without an investment of sorts as they need to survive in the long run.
Most of the startups today are not even close to breakeven cost, and are heavily losing money and so are very dependent on capital from other investors.
We as a company would need fundraising for 2 major reasons.
To create a SAAS based model where grocery stores from all across the city can come together and sell thei products to everyone.
The Second, is to pay salary to all the riders who are willing to take the groceries from Point A (the store) to Point B (The customer).
According to me, one of the best places to look for investors in my region are taking a look at angel Investors.
Kolkata Angel’s are best suited to me because, most of the investors are from the same city as I plan to start up in.
They have known the city, and know consumer trends in the city to help work.
I also think that word of mouth is one of the biggest advertisers anywhere around the world. And having people from your city spread the word could attract most number of customers