Question

Scenario Manager (a) Using the What-If Analysis feature, create 3 scenarios based on the table provided below. Changing Values Case 1 Case 2 Case 3 Annual contribution $1,000 $3,000 $5,000 Age when contributions start 20 30 Retirement age 65 67 69 Rate of return 2.5% 3.0% 4.0% Years in retirement Rate of return during retirement 2.5% 3.0% 4.0% Periods per year 12 12 25 25 30 (b) Generate a scenario summary report for the total annual contribution, the amount accumulated for retirement, and the pension amount per period during retirement. (c) Using the scenario summary results, three Bar charts (for the total annual contribution, the amount accumulated for retirement, and the pension amount per period during retirement) based the three cases. Move all charts new worksheet. Add appropriate chart elements for each chart. (d) Save the workbook.

Please use excel and show how you did it (formulas) thanks.

Answer 1

File Tools View New Microsoft Word Document - Word 6 - 0 X fx 52AEBES fx 2 che Roof um dugim Podpory 1 Total Anunt Carbon ed for rent con amount period during tromont Po Type here to search A4 ENG w. Screens 1-2 of 14 3 140% Type here to search O Bid DOE- ^ a to 1)) ENG * - + 21:32 10-02-2020

     

File Tools View New Microsoft Word Document - Word 0 - 0 X 2 Y Paris per Rate of Return 12 cuore legt Periods pery 11 Total A Carbution Pension muted during T here to wath A4 1918 Count: 1136 Type here to wach OO - AAI EN 4 Screens 3-4 of 14 DOE- 140% - + 21:33 10-02-2020 Type here to search O Bid ^ a ^) ENG 6

File Tools View New Microsoft Word Document - Word 0 - 0 X Home For u m Via Del 2 Chains Anton mung Total Contribution D uring Type here to search Type here to wach A&Od 0.% Screens 5-6 of 14 - + 140% Type here to search O Bilg DOE- ^ a to 1)) ENG * 21:34 10-02-2020

     

File Tools View New Microsoft Word Document - Word - o X What if a Developele Serie Erel tell me what you want to File Home Formulas Date Page Layout Show Dutres Review Fe Hell Home 2. Inset Page Layout Showeries Fonds Connections hods tom for Review View Deseoper U Stace y τα ηπει it Tell me what you want to do el 9 group fiber W Add a Sheet afΔΠΒr Go S ub for -X 1000 C D E F G H I J K L M N O P Q Entre of the enging als 2 Changing als 3 Annual Contribution Are when contribution starts 5 Retirementare Gute of Return reas in Retirement Rate of Reture during retirement Periods per year Periodsperas SCS6 0.690 12 Amount Accumulated forurement Dancion not per period during 1 Total Anna Co tion 12 Anunt Accumulated for retirement Pension amourt per period during 13 renant 114 S11772-10 Othere to search O O O . .Taw count? Samos A Atoa N es 1 Type here to search O @ O * A 200 Screens 7-8 of 14 E- 140% - + 21:34 10-02-2020 Type here to search O Bit Alo ) ENG

File Tools View New Microsoft Word Document - Word - X 2 hou HY Oh who c on carts assurent Refunduring met Total Annual Corbution 12 Anunt Accurated forurenant Anes ted for Type here to with 3 AGIM T here to search * W IN 2090 Note: Repeat the same steps to create Case 3. Screens 9-10 of 14 DOE- - + 140% Type here to search O Bid 21:35 ^ a ^) ENG 10-02-2020 6

File Tools View New Microsoft Word Document - Word - o X Home Page Layou t Vimeoper what you Home Page Layout Fondu Reden ve Developer tal e what you want to do 2 Changing Volws Acom 7 Tears in Retirement Periods per yea Total A Contribution Amount Accumulated for retirement Doncion amount per period during 13 retirement و مردم Type here to wath A to EMG 03 Double - click on cases to see different scenarios. Now, to create scenario summary, click on summary i.e the tab on the right. Now select the result cells and click ok. Screens 11-12 of 14 E- - + 140% 21:35 Type here to search od 9 Alo ) ENG 10-02-2020

File Tools View New Microsoft Word Document - Word - O X le Home Page Layout File What formulus are real Rew View Developer Hell Design Fornet Tell me what you want to do Review You Bedoes Ho t eleht you or do my TB del sorte Home out Page Layou Recoracio Share Gebrow New Vis Macro M Code Dalog Add B SA ore Security COM A 1 Scenario Summary Changing colle: Annual Contibution Age when contribution starts Chart Title 250% 30% 400% Rate of Return Years Retirement Rate of Return during retirement Portes per a 2.50% 12 12 12 time Sorunsur port w rested. Charging for each Total Annual Contibution 45000 12000 195000 Amount Accumulated for them at 516.032.46,040.5 4.52.045. Pension amount per period during 556.562,146.736,109.00 15 Notes Current Values to presents aluno channels 17 time Scenario Summary Report was created. Changing cells for each 15 senario we highlighting Town in den Typeber to ach AGEN . Scenario Summary She Shatt wage 122.330 Count: S: 11541432 1 Type here to search O @OX- Select the result cells and choose a chart type from DESIGN TAB. End of document I Screens 13-14 of 14 E- - + 140% 21:35 10-02-20206 Type here to search od 9 Alo ) ENG

Extremely important: While calculating values of Case 1 , please change the denomination in the formula for pension amount per period during retirement. The new formula should be =-FV(C8,C7,0,C12,0)/(12*C7). Instead of 20 , the value should be C7. Due to this change, the results for pension amount per period during retirement in case 2 and Case 3 will be 1717.38 and 4072.68 respectively. Kindly make the above changes. Since i am unable to attach the table due to some technical reasons , I am extremely sorry.

Note: Please upvote and comment if you liked the explanation. Also, comment for any doubts. Suggestions are always welcome.