May you please help me with this homework. Please provide me with guidance.
Thanks a whole bunch!
Amy
Use theSales Forecast,Comparative Growth DataandComparative Operating Statisticsto complete this milestone.
- Analyze thecompetitors' relative strengthsin the marketplace.
- Write a short paragraph describing the growth rate of each competitor. You will get the solution from the comparative growth data sheet.
- How much of the market does each competitor now own? Do you see a trend of gaining or losing market share? Present your findings using Power BI.
- Perform your analysis for the cars and trucks category. Create pie charts using columnsCandFfrom the comparative growth Excel sheet in Power BI.
- Perform your analysis for the connected cars and trucks category. Create pie charts using columnsHandKfrom the comparative growth Excel sheet in Power BI.
- Determine each competitor's financial strength by looking at their comparative operating data.
Themes Forward * Backward ~ Pane Effects Area v Titles Scale: 80% Ilil Print | Print Arrange Page Setup Scale to Fit Sheet Options Themes image1.png X V fx C D E G K B H 3 5 6 7 Annual Cars Global Market Projected Market share Market share Projected Projected Market share share Conencted percentage for Markets and & Light Trucks Market for Revenue in percentage CAGR over revenues in percentage for CAGR over next Connected percentag 10 years Car connected cars Competitors 2020 (billions) for cars and the next 10 2030 cars and trucks for Revenues trucks now Cars & Light and trucks in 3 in 2030 TAM years (billions) Trucks connected (billions) in 2030 g Global $ 3,227.70 100% 4.10% $ 4,810.0 100% 53.9 100% 25.20% $ 510.07 100% 10 Your Company 187.10 5.80% 3.10% $ 254.2 5.28% 3.83 7.10% 10.20% $ 10.11 1.98% 11 BMW S 126.10 3.91% 3.70% S 181.5 3.77% 1.62 3.00% 25.50% $ 15.67 3.07% 12 Toyota S 275.40 8.53% 3.90% $ 404.5 8.41% 4.80 8.90% 24.80% $ 43.97 8.62% 13 VW S 282.90 8.76% 1.30% S 433. 9.00% B.36 15.50% 23.20% $ 67.30 13.19% 14 15 Note for student: Projected Global revenue for in-car connected services by 2030: $81.1 Billion 16 17 18 19 20 21 22 23 24 26 in Ready Type here to search X hpFormula B C D E 8 in billions USD Your Company VW BMW Toyota 10 Revenue 187.1 282.9 126.1 275.4 11 Operating Income 5.7 20.5 9 22.5 12 Net Income 0.9 16.1 6.1 19.7 13 14 Assets 310 590.6 275.9 484.7 15 Liabilities 270.2 443.2 203.4 293.9 16 Equity 39.9 147.4 72.5 190.8 17 18 Number Employees 190,000 304,174 133,778 359,542 19 20 21 22 23 24 25 26 27 inB C D E F G K Year 0 1 2 3 4 5 6 8 9 10 0 Sales Traditional 183.3 188.7125 194.26527 199.95985 205.79753 211.77936 217.90604 224.17793 230.59495 237.15659 244.09 1 Sales Connected 3.8 4.1876 5.2345 5.768419 7.2105238 9.0131547 11.266443 14.083054 17.603818 22.004772 27.505965 2 Total Sales 187.1 192.9001 199.49977 205.72826 213.00806 220.79252 229.17249 238.26098 248.19877 259.16136 271.59597 3 4 Gross Margin Traditional 27.495 28.306875 29.13979 29.993977 30.86963 31.766904 32.685906 33.626689 34.589242 35.573488 36.6135 5 Gross Margin Connected 0.798 0.879396 1.099245 1.211368 1.51421 1.8927625 2.3659531 2.9574414 3.6968017 4.6210022 5.7762527 6 Total Gross Margin 28.293 29.186271 30.239035 31.205345 32.38384 33.659667 35.05186 36.58413 38.286044 40.19449 42.389753 7 8 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 19 20 21 Notes: Your company grows connected cars at 10.2%/year for 2 years, then grows connected cars at 25%/year. 22 Your company grows traditional cars at industry growth rate of 4.1%/year. 23 Margin for connected cars: 21%. 24 Margin for traditional cars: 15%. 25 Numbers shown in billions. 26 There are two additional R&D costs for this option in year 0 and year 1 27 28 29 30 31 32 Sales Growth of Whole Market Sales Growth Connected VehiMan Option A for Innovation Option B for Innovation + Ready Type here to search OA B C D E F G H K 8 Year 5 6 8 9 10 9 10 Sales Traditional 183.3 188.7125 194.26527 199.95985 205.79753 211.77936 217.90604 224.17793 230.59495 237.15659 244.09 11 Sales Connected 3 4.1876 4.6147352 5.0854382 5.6041529 6.1757765 6.8057057 7.4998877 8.2648762 9.1078936 10.11 12 Total Sales 187.1 192.9001 198.88 205.04528 211.40169 217.95514 224.71175 231.67781 238.85982 246.26448 254.2 13 14 Gross Margin Traditional 27.495 28.306875 29.13979 29.993977 30.86963 31.766904 32.685906 33.626689 34.589242 35.573488 36.6135 15 Gross Margin Connected 0.798 0.879396 0.9690944 1.067942 1.1768721 1.2969131 1.4291982 1.5749764 1.735624 1.9126577 2.1231 16 Total Gross Margin 28.293 29.186271 30.108885 31.061919 32.046502 33.063817 34.115105 35.201665 36.324866 37.486146 38.7366 17 18 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 19 20 21 Notes: Your company grows connected cars at 10.2%/year. 22 Your company grows traditional cars at industry growth rate of 4.1 %/year. 23 Margin for connected cars: 21%. 24 Margin for traditional cars: 15%. 25 R&D and Capital Costs grow at a rate of 3%. 26 27 28 an Sales Growth of Whole Market Sales Growth Connected Ve Mar Option A for Innovation Option B for Innovation Ready Type here to search OB C D E G K Year O 2 3 4 5 6 7 8 9 10 Your Company 3.8 4.1876 4.6147352 5.0854382 5.6041529 6.1757765 6.8057057 7.4998877 8.2648762 9.1078936 10.11 0 BMW 1.6 2.008 2.52004 3.1626502 3.969126 4.9812531 6.2514727 7.8455982 9.8462258 12.357013 15.67 1 Toyota 4.8 5.9904 7.4760192 9.330072 11.64393 14.531624 18.135467 22.633063 28.246063 35.251086 43.97 2 vw 8.4 10.3488 12.749722 15.707657 19,351833 23.841459 29.372677 36.187138 44.582554 54.925707 67.3 Sales Growth Connected Vehicle Market 80 70 60 50 40 30 24 20 25 10 26 27 10 11 28 Sales Growth of Whole Market Sales Growth Connected Vah. Mar Option A for Innovation Option B for Innovation + Ready Type here to search O2 3 6 8 9 10 Year 0 8 198.88 205.04528 211.40169 217.95514 224.71175 231.67781 238.85982 246.26448 254.2 9 Your Company 187.1 192.9001 168.634 174.87346 181.5 10 BMW 126.1 130.7657 135.60403 140.62138 145.82437 151.21987 156.81501 162.61716 11 Toyota 275.4 286.1406 297.30008 308.89479 320.94168 333.45841 346.46329 359.97536 374.01439 388.60096 404.5 379.8596 396.19356 413.22989 433.1 12 VW 282.9 295.0647 307.75248 320.98584 334.78823 349.18412 364.19904 13 14 15 Sales Growth of the Whole Market 16 500 17 450 18 400 19 350 20 300 21 250 22 200 23 150 24 100 25 50 26 27 10 11 28 Sales Growth of Whole Market Sales Growth Connected veh, Mar Option A for Innovation Option B for Innovation + Ready Type here to search O
