excel assignment help! Due in 6 hours!!
axxxd.gh
AdvancedModel.xlsxOriginal Model
| DoWe Cheat'em & Howe | ||||||
| Operating Company Data for Fiscal Year 2019 | ||||||
| FY 2019 Values in Millions of Dollars | FY 2019 | |||||
| Company | Expected New Division | Revenue | Profits | % Profit | ||
| 1 | DWC&H Consumer | $270 | $105 | 38.89% | Instructions: | |
| 2 | DWC&H Military | $300 | $205 | 68.33% | Use CountIf and SumIF as appropriate to generate the totals | |
| 3 | DWC&H Consumer | $175 | $74 | 42.29% | in the cells to the left. | |
| 4 | DWC&Y Agriculture | $75 | $15 | 20.00% | ||
| 5 | DWC&H Commercial | $970 | $125 | 12.89% | Using the consolidated data from the Original model, prepare two 3-D pie charts showing the percent each new division has of revenues and profits. Remember to use the elements of good charting. Put these pie charts into separate worksheets. | |
| 6 | DWC&H Consumer | $350 | $102 | 29.14% | Then prepare a 3-D column chart showing comparing revenue and profit dollars for each of the new divisions. | |
| 7 | DWC&H Consumer | $475 | $125 | 26.32% | ||
| 8 | DWC&H Military | $1,500 | $900 | 60.00% | ||
| 9 | DWC&H Consumer | $75 | $22 | 29.33% | ||
| 10 | DWC&H Consumer | $103 | $33 | 32.04% | ||
| 11 | DWC&H Commercial | $875 | $103 | 11.77% | ||
| 12 | DWC&H Commercial | $523 | $98 | 18.74% | ||
| 13 | DWC&H Consumer | $183 | $57 | 31.15% | ||
| 14 | DWC&H Military | $125 | $85 | 68.00% | ||
| 15 | DWC&H Consumer | $647 | $133 | 20.56% | ||
| 16 | DWC&Y Agriculture | $103 | $38 | 36.89% | ||
| 17 | DWC&Y Agriculture | $155 | $43 | 27.74% | ||
| Expected Totals by New Division: | ||||||
| Revenue | Profits | % Profit | # Companies | |||
| DWC&H Consumer | $2,278.00 | $651.00 | 28.58% | 8 | ||
| DWC&H Military | $1,925.00 | $1,190.00 | 61.82% | 3 | ||
| DWC&H Commercial | $2,368.00 | $326.00 | 13.77% | 3 | ||
| DWC&Y Agriculture | $333.00 | $96.00 | 28.83% | 3 | ||
Profit Projection Instructions
| DoWe Cheat'em & Howe | |
| Profit Projection Modeling Instructions… | |
| Executive management at DWC&H wants to get an idea of how profits might look for each of the new operating divisions over the next five years. | |
| After consulting with economists, academic experts and other practioners of voodoo, they have decided on the following likely parameters. | |
| For years when the "Sunny Conditions" occur, revenue growth and profit growth are expected as follows for each new operating division. | |
| Revenue growth of 6.50% and profit growth of 6.75% | |
| For years when "Average Conditions" occur, revenue growth and profit growth are expected as follows: | |
| Revenue growth of 5.90% and profit growth of 6.0% | |
| For years when "The Sky is Falling" occur, revenue growth and profit growth are expected as follows: | |
| Revenue growth of -1.90% and profit growth of .30% | |
| To clarify… All growth or declines are in dollar amounts. Do NOT use the percent profit calcuation in this model. | |
| The Profit Projection worksheet has an outline of how you might think about the initial setup of the model. You are to create a model where the | |
| executives of DWC&H can plug in the type of year it is and then project what revenues will look like based on that projection. You are to create a | |
| set of vertical column charts showing the revenue and profit for each division over the 7 years projected | |
Profit Projection
| What we know: | |||||||||||
| Starting Figures for Each Division: | Year Type: | Profit and Revenue Projections: | |||||||||
| Revenue | Profit | Year One | 2 | Revenue Chg. | Profit Chg. | ||||||
| DWC&H Consumer | $2,278 | $651 | Year Two | 3 | Type One | 6.50% | 6.75% | ||||
| DWC&H Military | $1,925 | $1,190 | Year Three | 2 | Type Two | 5.90% | 6.00% | ||||
| DWC&H Commercial | $2,368 | $326 | Year Four | 1 | Type Three | -1.90% | 0.30% | ||||
| DWC&Y Agriculture | $333 | $96 | Year Five | 2 | |||||||
| Prediction of Revenue & Profit | |||||||||||
| Year One | Year Two | Year Three | Year Four | Year Five | |||||||
| Revenue | Profit | Revenue | Profit | Revenue | Profit | Revenue | Profit | Revenue | Profit | ||
| DWC&H Consumer | |||||||||||
| DWC&H Military | |||||||||||
| DWC&H Commercial | |||||||||||
| DWC&Y Agriculture | |||||||||||
Hints: Remember that the management can change the type of year to see different "what-if" effects. Figures in year one are based on starting figures. Figures in years two and beyond are based on previous year's figures. Nested-ifs are likely needed and cell references required.
