Strategic Plan and Power BI on VDI
gracehdz5212MBA580SalesForecast1.xlsx
Sales Growth of Whole Market
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Your Company | 187.1 | 192.9001 | 198.8800031 | 205.0452831961 | 211.4016869752 | 217.9551392714 | 224.7117485888 | 231.6778127951 | 238.8598249917 | 246.2644795665 | 254.2 |
BMW | 126.1 | 130.7657 | 135.6040309 | 140.6213800433 | 145.8243711049 | 151.2198728358 | 156.8150081307 | 162.6171634315 | 168.6339984785 | 174.8734564222 | 181.5 |
Toyota | 275.4 | 286.1406 | 297.3000834 | 308.8947866526 | 320.9416833321 | 333.458408982 | 346.4632869323 | 359.9753551227 | 374.0143939724 | 388.6009553374 | 404.5 |
VW | 282.9 | 295.0647 | 307.7524821 | 320.9858388303 | 334.7882299 | 349.1841237857 | 364.1990411085 | 379.8595998762 | 396.1935626708 | 413.2298858657 | 433.1 |
Sales Growth of the Whole Market
Your Company 187.1 192.90009999999998 198.88000309999995 205.04528319609994 211.40168697517902 217.95513927140956 224.71174858882324 231.67781279507673 238.85982499172408 246.26447956646751 254.2 BMW 126.1 130.76569999999998 135.60403089999997 140.62138004329995 145.82437110490204 151.21987283578341 156.81500813070738 162.61716343154353 168.63399847851062 174.8734564222155 181.5 Toyota 275.39999999999998 286.14059999999995 297.30008339999995 308.89478665259992 320.9416833320513 333.45840898200129 346.4632869322993 359.97535512265893 374.01439397244258 388.60095533736779 404.5 VW 282.89999999999998 295.06469999999996 307.75248209999995 320.98583883029994 334.78822990000282 349.18412378570292 364.19904110848813 379.85959987615308 396.19356267082765 413.22988586567322 433.1
Sales Growth Connected Veh. Mar
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Your Company | 3.8 | 4.1876 | 4.6147352 | 5.0854381904 | 5.6041528858 | 6.1757764802 | 6.8057056812 | 7.4998876606 | 8.264876202 | 9.1078935746 | 10.11 |
BMW | 1.6 | 2.008 | 2.52004 | 3.1626502 | 3.969126001 | 4.9812531313 | 6.2514726797 | 7.8455982131 | 9.8462257574 | 12.3570133255 | 15.67 |
Toyota | 4.8 | 5.9904 | 7.4760192 | 9.3300719616 | 11.6439298081 | 14.5316244005 | 18.1354672518 | 22.6330631302 | 28.2460627865 | 35.2510863576 | 43.97 |
VW | 8.4 | 10.3488 | 12.7497216 | 15.7076570112 | 19.3518334378 | 23.8414587954 | 29.3726772359 | 36.1871383546 | 44.5825544529 | 54.925707086 | 67.3 |
Sales Growth Connected Vehicle Market
Your Company 3.8 4.1875999999999998 4.6147352000000001 5.0854381904000006 5.6041528858208007 6.1757764801745232 6.8057056811523253 7.499887660629863 8.2648762020141096 9.1078935746195491 10.11 BMW 1.6 2.008 2.5200399999999998 3.1626501999999994 3.9691260009999989 4.9812531312549986 6.2514726797250226 7.8455982130549025 9.8462257573839018 12.357013325516796 15.67 Toyota 4.8 5.9904000000000002 7.4760192000000005 9.3300719615999999 11.6439298080768 14.531624400479847 18.13546725179885 22.633063130244967 28.246062786545718 35.251086357609054 43.97 VW 8.4 10.348800000000001 12.749721600000001 15.7076570112 19.351833437798401 23.841458795367629 29.372677235892919 36.187138354620075 44.582554452891934 54.925707085962863 67.3
Option A for Innovation
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Sales Traditional | 183.3 | 188.7125 | 194.2652679 | 199.9598450057 | 205.7975340894 | 211.7793627912 | 217.9060429077 | 224.1779251344 | 230.5949487897 | 237.1565859918 | 244.09 |
Sales Connected | 3.8 | 4.1876 | 5.2345 | 5.768419 | 7.21052375 | 9.0131546875 | 11.2664433594 | 14.0830541992 | 17.603817749 | 22.0047721863 | 27.5059652328 |
Total Sales | 187.1 | 192.9001 | 199.4997679 | 205.7282640057 | 213.0080578394 | 220.7925174787 | 229.172486267 | 238.2609793337 | 248.1987665387 | 259.1613581781 | 271.5959652328 |
Gross Margin Traditional | 27.495 | 28.306875 | 29.139790185 | 29.9939767509 | 30.8696301134 | 31.7669044187 | 32.6859064362 | 33.6266887702 | 34.5892423185 | 35.5734878988 | 36.6135 |
Gross Margin Connected | 0.798 | 0.879396 | 1.099245 | 1.21136799 | 1.5142099875 | 1.8927624844 | 2.3659531055 | 2.9574413818 | 3.6968017273 | 4.6210021591 | 5.7762526989 |
Total Gross Margin | 28.293 | 29.186271 | 30.239035185 | 31.2053447409 | 32.3838401009 | 33.6596669031 | 35.0518595416 | 36.584130152 | 38.2860440458 | 40.1944900579 | 42.3897526989 |
R&D and Capital Costs | 4.1 | 4.133 | 1.16699 | 1.2019997 | 1.238059691 | 1.2752014817 | 1.3134575262 | 1.352861252 | 1.3934470895 | 1.4352505022 | 1.4783080173 |
Notes: | Your company grows connected cars at 10.2%/year for 2 years, then grows connected cars at 25%/year. | ||||||||||
Your company grows traditional cars at industry growth rate of 4.1 %/year. | |||||||||||
Margin for connected cars: 21%. | |||||||||||
Margin for traditional cars: 15%. | |||||||||||
Numbers shown in billions. | |||||||||||
There are two additional R&D costs for this option in year 0 and year 1. | |||||||||||
Option B for Innovation
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||
Sales Traditional | 183.3 | 188.7125 | 194.2652679 | 199.9598450057 | 205.7975340894 | 211.7793627912 | 217.9060429077 | 224.1779251344 | 230.5949487897 | 237.1565859918 | 244.09 | ||
Sales Connected | 3.8 | 4.1876 | 4.6147352 | 5.0854381904 | 5.6041528858 | 6.1757764802 | 6.8057056812 | 7.4998876606 | 8.264876202 | 9.1078935746 | 10.11 | ||
Total Sales | 187.1 | 192.9001 | 198.8800031 | 205.0452831961 | 211.4016869752 | 217.9551392714 | 224.7117485888 | 231.6778127951 | 238.8598249917 | 246.2644795665 | 254.2 | ||
Gross Margin Traditional | 27.495 | 28.306875 | 29.139790185 | 29.9939767509 | 30.8696301134 | 31.7669044187 | 32.6859064362 | 33.6266887702 | 34.5892423185 | 35.5734878988 | 36.6135 | ||
Gross Margin Connected | 0.798 | 0.879396 | 0.969094392 | 1.06794202 | 1.176872106 | 1.2969130608 | 1.429198193 | 1.5749764087 | 1.7356240024 | 1.9126576507 | 2.1231 | ||
Total Gross Margin | 28.293 | 29.186271 | 30.108884577 | 31.0619187708 | 32.0465022194 | 33.0638174795 | 34.1151046292 | 35.2016651789 | 36.3248663209 | 37.4861455494 | 38.7366 | ||
R&D and Capital Costs | 1.1 | 1.133 | 1.16699 | 1.2019997 | 1.238059691 | 1.2752014817 | 1.3134575262 | 1.352861252 | 1.3934470895 | 1.4352505022 | 1.4783080173 | ||
Notes: | Your company grows connected cars at 10.2%/year. | ||||||||||||
Your company grows traditional cars at industry growth rate of 4.1 %/year. | |||||||||||||
Margin for connected cars: 21%. | |||||||||||||
Margin for traditional cars: 15%. | |||||||||||||
R&D and Capital Costs grow at a rate of 3%. | |||||||||||||