- 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.
Need step by step how to do this in Excel
Option A
-
- Option B
Year 0 1 2 3 4 5 6 7 8 9 10 Sales Traditional Sales Connected Total Sales 183.3 3.8 188.7125 194.26527 199.95985 205.79753 211.77936 217.90604 224.17793 230.59495 237.15659 244.09 4.1876 5.2345 5.768419 7.2105238 9.0131547 11.266443 14.083054 17.603818 22.004772 27.505965 192.9001 199.49977 205.72826 213.00806 220.79252 229.17249 238.26098 248.19877 259.16136 271.59597 187.1 Gross Margin Traditional Gross Margin Connected Total Gross Margin 27.495 28.306875 29.13979 29.993977 0.798 0.879396 1.099245 1.211368 28.293 29.186271 30.239035 31.205345 30.86963 31.766904 32.685906 33.626689 34.589242 35.573488 36.6135 1.51421 1.8927625 2.3659531 2.9574414 3.6968017 4.6210022 5.7762527 32.38384 33.659667 35.05186 36.58413 38.286044 40.19449 42.389753 R&D and Capital Costs 4.1 4.133 1.16699 1.2019997 1.2380597 1.2752015 1.3134575 1.3528613 1.3934471 1.4352505 1.478308 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 and year 1. Year 0 1 2 6 7 8 10 Sales Traditional Sales Connected Total Sales 183.3 3.8 187.1 188.7125 194.26527 199.95985 205.79753 211.77936 217.90604 224.17793 230.59495 237.15659 4.1876 4.6147352 5.0854382 5.6041529 6.1757765 6.8057057 7.4998877 8.2648762 9.1078936 192.9001 198.88 205.04528 211.40169 217.95514 224.71175 231.67781 238.85982 246.26448 244.09 10.11 254.2 Gross Margin Traditional Gross Margin Connected Total Gross Margin 27.495 28.306875 29.13979 29.993977 30.86963 31.766904 32.685906 33.626689 34.589242 35.573488 0.798 0.879396 0.9690944 1.067942 1.1768721 1.2969131 1.4291982 1.5749764 1.735624 1.9126577 28.293 29.186271 30.108885 31.061919 32.046502 33.063817 34.115105 35.201665 36.324866 37.486146 36.6135 2.1231 38.7366 R&D and Capital Costs 1.1 1.133 1.16699 1.2019997 1.2380597 1.2752015 1.3134575 1.3528613 1.3934471 1.4352505 1.478308 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%