Prof Double R

 

Overview

You work as a middle manager for one of the top U.S. producers of luxury and mass-market automobiles and trucks. The company from the course scenario has decided to incorporate internet of things (IoT) technology in its vehicles (also called connected cars). Often organizations have to choose from multiple innovative implementation options. The choice is usually determined by many factors including the financial viability of each idea. In this assignment, you will compare the financial benefits for two innovation options and create data visualizations to help determine the option with greater financial benefit. This assignment will help you make a recommendation for one of the two options in Milestone One.

Prompt

Create a series of charts in an Excel spreadsheet and a memo that compares the financial benefits of options A and B. The charts in your spreadsheet should include a comparison of financial forecasts from both innovation options (discontinuous and incremental). You should create the charts using the data in this

Sales Forecast.

Make sure to add titles to all your charts so you can clearly reference them in your memo. Consider the following points:

1. Create a chart for sales forecast data.

   Graph sales forecast data for total sales for options A and B on a three-dimensional stacked column chart.

   Each column should show traditional sales on the bottom and connected sales on the top.
   Both options may appear on the same three-dimensional chart, or you can make a separate chart for each option.

2. Create a chart for gross margin forecast data.

   Graph gross margin forecast data for total sales for options A and B on a three-dimensional stacked column chart.

   Each column should show traditional sales on the bottom and connected sales on the top.
   Both options may appear on the same three-dimensional chart, or you can make a separate chart for each option.

3. Create a chart for gross margin less R&D and capital costs calculations.

   Calculate gross margin less R&D and capital costs for options A and B.
   Graph total gross margin less R&D and capital costs forecasts to compare options A and B on a line chart.

   Both options should appear on the same chart.

4. Explain any conclusions you can draw from your data visualizations.

   Review the charts and discuss any conclusions you can make from those data visualizations.
   Explain whether option A or option B will provide your company the most financial benefit. You should reference your data visualizations to justify your explanation and conclusions.

What to Submit

• Submit an Excel document with all your charts.
• Submit a ½- to 1-page Word document with 12-point Times New Roman font, double spacing, and one-inch margins. 

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
253.898678433

BMW
126.1
130.7657
135.6040309
140.6213800433
145.8243711049
151.2198728358
156.8150081307
162.6171634315
168.6339984785
174.8734564222
181.3437743098

Toyota
275.4
286.1406
297.3000834
308.8947866526
320.9416833321
333.458408982
346.4632869323
359.9753551227
374.0143939724
388.6009553374
403.7563925955

VW
282.9
295.0647
307.7524821
320.9858388303
334.7882299
349.1841237857
364.1990411085
379.8595998762
396.1935626708
413.2298858657
430.9987709579

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 253.89867843302798 BMW 126.1 130.76569999999998 135.60403089999997 140.62138004329995 145.82437110490204 151.21987283578341 156.81500813070738 162.61716343154353 168.63399847851062 174.8734564222155 181.34377430983747 Toyota 275.39999999999998 286.14059999999995 297.30008339999995 308.89478665259992 320.9416833320513 333.45840898200129 346.4632869322993 359.97535512265893 374.01439397244258 388.60095533736779 403.75639259552509 VW 282.89999999999998 295.06469999999996 307.75248209999995 320.98583883029994 334.78822990000282 349.18412378570292 364.19904110848813 379.85959987615308 396.19356267082765 413.22988586567322 430.99877095789714

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.0368987192

BMW
1.6
2.008
2.52004
3.1626502
3.969126001
4.9812531313
6.2514726797
7.8455982131
9.8462257574
12.3570133255
15.5080517235

Toyota
4.8
5.9904
7.4760192
9.3300719616
11.6439298081
14.5316244005
18.1354672518
22.6330631302
28.2460627865
35.2510863576
43.9933557743

VW
8.4
10.3488
12.7497216
15.7076570112
19.3518334378
23.8414587954
29.3726772359
36.1871383546
44.5825544529
54.925707086
67.6684711299

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.036898719230743 BMW 1.6 2.008 2.5200399999999998 3.1626501999999994 3.9691260009999989 4.9812531312549986 6.2514726797250226 7.8455982130549025 9.8462257573839018 12.357013325516796 15.508051723523577 Toyota 4.8 5.9904000000000002 7.4760192000000005 9.3300719615999999 11.6439298080768 14.531624400479847 18.13546725179885 22.633063130244967 28.246062786545718 35.251086357609054 43.993355774296099 VW 8.4 10.348800000000001 12.749721600000001 15.7076570112 19.351833437798401 23.841458795367629 29.372677235892919 36.187138354620075 44.582554452891934 54.925707085962863 67.668471129906251

Option A for Innovation

Year
0
1
2
3
4
5
6
7
8
9
10

Sales Traditional
183.3
190.8153
198.6387273
206.7829151193
215.2610146392
224.0867162394
233.2742716052
242.838516741
252.7948959274
263.1594866604
273.9490256135

Sales Connected
3.8
4.1876
4.6147352
5.768419
7.21052375
9.0131546875
11.2664433594
14.0830541992
17.603817749
22.0047721863
27.5059652328

Total Sales
187.1
195.0029
203.2534625
212.5513341193
222.4715383892
233.0998709269
244.5407149646
256.9215709402
270.3987136764
285.1642588467
301.4549908464

Gross Margin Traditional
27.495
28.622295
29.795809095
31.0174372679
32.2891521959
33.6130074359
34.9911407408
36.4257775112
37.9192343891
39.4739229991
41.092353842

Gross Margin Connected
0.798
0.879396
0.969094392
1.21136799
1.5142099875
1.8927624844
2.3659531055
2.9574413818
3.6968017273
4.6210021591
5.7762526989

Total Gross Margin
28.293
29.501691
30.764903487
32.2288052579
33.8033621834
35.5057699203
37.3570938463
39.383218893
41.6160361164
44.0949251582
46.8686065409

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
190.8153
198.6387273
206.7829151193
215.2610146392
224.0867162394
233.2742716052
242.838516741
252.7948959274
263.1594866604
273.9490256135

Sales Connected
3.8
4.1876
4.6147352
5.0854381904
5.6041528858
6.1757764802
6.8057056812
7.4998876606
8.264876202
9.1078935746
10.0368987192

Total Sales
187.1
195.0029
203.2534625
211.8683533097
220.865167525
230.2624927196
240.0799772864
250.3384044017
261.0597721294
272.2673802351
283.9859243327

Gross Margin Traditional
27.495
28.622295
29.795809095
31.0174372679
32.2891521959
33.6130074359
34.9911407408
36.4257775112
37.9192343891
39.4739229991
41.092353842

Gross Margin Connected
0.798
0.879396
0.969094392
1.06794202
1.176872106
1.2969130608
1.429198193
1.5749764087
1.7356240024
1.9126576507
2.107748731

Total Gross Margin
28.293
29.501691
30.764903487
32.0853792879
33.4660243019
34.9099204967
36.4203389338
38.0007539199
39.6548583915
41.3865806497
43.2001025731

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%.

image1