Creating an Excel spreadsheet

rohankhemka
IIASSIGNMENTTop-downexample.xlsx
Home>Business & Finance homework help>Creating an Excel spreadsheet

Assumptions

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
Currency: Euro
Excel Model: Inputs in blue
Calculations in black
Approach: Top Down Approach (from the market)
BP Period: We used 5 years to ensure a sufficient forecast period with a second location in the third year
Revenue Assumptions
Year 3 - New Location in Rome 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,4% of the total population in Milan; Target age is 15 - 64. Total population of Milan is 1,36 Mio. Age Group # Demographics Profile Italy # Driver licence Target %
Population of Milan with Age Structure of Italy Age 0 - 14 13.8% 0%
Age 15 - 24 9.8% 70.0% 10%
Age 25 - 54 43.0% 75.0% 10%
Age 55 - 64 12.4% 75.0% 5%
Age 65 + 21.0% 0%
Total Population of Milan and Target Percentage 100.00% 4.4%
Data Source: Italy Demographics Profile 2014 (http://www.indexmundi.com/italy/demographics_profile.html)
Year 2 Year 3 Year 4 Year 5
Increase in Target Percentage We assume the target percentage to increase as follows: 0.8000% 1.0000% 1.200% 1.400%
Driving Price We assume the average subscription price would be 0.10 € 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). price per min. 0.10 € 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% in suburban areas.
Costs Assumptions
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 In additionto this the average life cycle per battery is about 500 charges, a new one costs €200. We assume we can drive 50km per charge which leads to a cycle costs per 100km of €0.80. This results in a total of €1.60.
Station renting 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 costs We assumed that we would have insurance costs of €300 per scooter per year which covers every accident and spares but accounts for the deductible of the users.
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. clothing item to purchase from low end to designer would be around €200.00.
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 Year 2 Year 3 Year 4 Year 5
Revenue
Revenue from drives 892,800.00 71.4% 1,339,200.00 77.6% 1,562,400.00 55.2% 1,785,600.00 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 100.0% 2,830,476.77 100.0% 3,261,869.51 100.0% 3,949,933.85 100.0%
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.5% 946,300.00 54.9% 1,907,600.00 67.4% 1,672,600.00 51.3% 1,737,600.00 44.0%
Total Expenses -1,683,900.00 -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%

Revenue

PROJECT NAME Revenue
Revenue
Year 1 Year 2 Year 3 Year 4 Year 5
Total Population of Milan 1,359,905 1,359,905 1,359,905 1,359,905 1,359,905
Subscription Target Population 4.4% 5.2% 6.2% 7.4% 8.8%
Total Target Population of VespR Milan 59,509 70,389 83,988 100,307 119,345
Percentage of Sign-Ups 10% 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 2,864,731 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 11,902 12,867 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 10
zone factor
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

Costs

PROJECT NAME Costs
Costs
Year 1 Year 2 Year 3 Year 4 Year 5
Direct Costs
Driving
Energy expense per 100 km 5% 0.8 0.84 0.882 0.9261 0.972405
cycle costs per 100 km 100 0.8 0.8 0.8 0.8 0.8
Total Energy expense per 100 km 1.6 1.64 1.682 1.7261 1.772405
Driven km per year 3,600,000 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 2000 2000 2000 2000
Quantity of Item 200 300 350 400 450
Average Leasing Cost per (Helmets) 150 150 150 150 150
Quantity of Item 2 400 600 700 800 900
Total Cost Leasing Motorbike 460000 690000 805000 920000 1035000
Advertisement
First Signup Bonus 15000 0 15000 0 0
Social Media Ads and SEO 20000 20000 40000 20000 20000
Total Advertisement Costs 35000 20000 55000 20000 20000
Total Direct Cost 552600 798560 965966 1064279.2 1198564.805
Operating Expenses y
Station
Total Station Renting Costs per year 125000 200000 400000 450000 500000
Station setup costs per Station 10000 10000 10000 10000 10000
New Station 25 15 40 10 10
Total Costs Station 425000 355000 805000 555000 605000
Salary
worker (Service, mechanic) 20 20 40 40 40
annual salary per worker 24000 24000 24000 24000 24000
Total Salary expense 480000 480000 960000 960000 960000
Other costs
Technology set-up cost 100000 0 0 0 0
Create website and app 50000 5000 5000 5000 5000
Service Station (garage) 15000 15000 30000 30000 30000
Insurance 300 60000 90000 105000 120000 135000
Electricty 1000 1000 2000 2000 2000
Internet Service Station 300 300 600 600 600
Total Operating Expense 1131300 946300 1907600 1672600 1737600
Total Expenses 1683900 1744860 2873566 2736879.2 2936164.805
Additional Calculations
Year 1 Year 2 Year 3 Year 4 Year 5
Scooters
Scooters 200 300 350 400 450
drives per year per scooter 3,600 3,600 3,600 3,600 3,600
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 € 5,000.00 € 5,000.00 € 5,000.00 € 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 10
days per year 360
averg. Driving minutes 10
averg. Km/h in city 30
averg. Km per drive 5
Energy expense per 100 km
averg. kWh per 100 km 4
averg. Price per kWh 0.20 €
Cycle costs per 100km
Battery costs 200
life cycle per battery 500
km per charge 50
Driving Price
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

EBITDA

Year 1 Year 2 Year 3 Year 4 Year 5 -434162.36208559992 -19775.828963328153 -43089.229473370593 524990.31319889892 1013769.0485493522

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