Decision analysis There is a case with data available on excel. An interactive dashboard is required

profileKeer49
Exceldashboard.xlsx
Home>Computer Science homework help>Decision analysis There is a case with data available on excel. An interactive dashboard is required

Dashboard

Calculations

Reitrement Model (B) In this model, we have THREE decision variables: Question: The dashboard you did as part of homework #2 was static — you took some data from the regional sales database and created a few charts. In this Case, you will develop an interactive dashboard — a graphical user interface to a spreadsheet. Bob (me) will use it to access your model, but he won’t have to worry about all of the messy Excel stuff. For example, Bob might choose his retirement age from a list of drop-down ages or pick and choose his contribution about from a "spinner" that will click up or down one percentage point at a time. All of the calculations are done on other tabs — away from Bob — Bob is presented with a graphical summary (graph(s) / chart(s) / table(s) of summarized numbers. That's the big picture idea.
Bob's contribution rate (like Model A), the % of his
Parameters final salary that he needs to live on, and his age at retirement
Current Income 116,000 The "output" from the model is how old Bob will be when the
Current Savings 167,000 retirement funds finally run out. It doesn't assume anyone dies.
Employer Saving Rate 10%
Age 46
ROA pre- 8.0%
ROA post- 5.0%
Income Growth Rate 4.0%
Inflation Rate 2.0%
Tax Rate (post-) 35.0%
Decision
Savings Rate 8.0%
% of final Income 70% Age funds run out: 77
Retirement Age 67
Model Contribution Contribution Post-Retirement Funds Depleted?
Age Income Employer Bob Assets Start Assets End Age Spending Withdrawl Asset Start Asset End 1 = Yes
46 116,000 11,600 9,280 167,000 201,240 67 185,036 284,671 2,508,679 2,349,442 0
47 120,640 12,064 9,651 201,240 239,054 68 188,737 290,364 2,349,442 2,176,550 0
48 125,466 12,547 10,037 239,054 280,763 69 192,511 296,171 2,176,550 1,989,206 0
49 130,484 13,048 10,439 280,763 326,711 70 196,362 302,095 1,989,206 1,786,571 0
50 135,704 13,570 10,856 326,711 377,274 71 200,289 308,137 1,786,571 1,567,763 0
51 141,132 14,113 11,291 377,274 432,860 72 204,295 314,299 1,567,763 1,331,852 0
52 146,777 14,678 11,742 432,860 493,909 73 208,381 320,585 1,331,852 1,077,859 0
53 152,648 15,265 12,212 493,909 560,898 74 212,548 326,997 1,077,859 804,755 0
54 158,754 15,875 12,700 560,898 634,345 75 216,799 333,537 804,755 511,456 0
55 165,104 16,510 13,208 634,345 714,812 76 221,135 340,208 511,456 196,820 0
56 171,708 17,171 13,737 714,812 802,904 77 225,558 347,012 196,820 -140,351 1
57 178,577 17,858 14,286 802,904 899,280 78 230,069 353,952 -140,351 -501,320 1
58 185,720 18,572 14,858 899,280 1,004,652 79 234,670 361,031 -501,320 -887,418 1
59 193,149 19,315 15,452 1,004,652 1,119,791 80 239,364 368,252 -887,418 -1,300,040 1
60 200,874 20,087 16,070 1,119,791 1,245,532 81 244,151 375,617 -1,300,040 -1,740,659 1
61 208,909 20,891 16,713 1,245,532 1,382,778 82 249,034 383,129 -1,740,659 -2,210,822 1
62 217,266 21,727 17,381 1,382,778 1,532,508 83 254,015 390,792 -2,210,822 -2,712,155 1
63 225,956 22,596 18,077 1,532,508 1,695,781 84 259,095 398,608 -2,712,155 -3,246,370 1
64 234,995 23,499 18,800 1,695,781 1,873,743 85 264,277 406,580 -3,246,370 -3,815,269 1
65 244,395 24,439 19,552 1,873,743 2,067,633 86 269,562 414,711 -3,815,269 -4,420,744 1
66 254,170 25,417 20,334 2,067,633 2,278,794 87 274,954 423,006 -4,420,744 -5,064,786 1
67 264,337 26,434 21,147 2,278,794 2,508,679 88 280,453 431,466 -5,064,786 -5,749,492 1
68 274,911 27,491 21,993 2,508,679 2,758,857 89 286,062 440,095 -5,749,492 -6,477,061 1
69 285,907 28,591 22,873 2,758,857 3,031,029 90 291,783 448,897 -6,477,061 -7,249,811 1
70 297,343 29,734 23,787 3,031,029 3,327,033 91 297,619 457,875 -7,249,811 -8,070,177 1
71 309,237 30,924 24,739 3,327,033 3,648,858 92 303,571 467,032 -8,070,177 -8,940,718 1
72 321,606 32,161 25,729 3,648,858 3,998,656 93 309,643 476,373 -8,940,718 -9,864,127 1
73 334,471 33,447 26,758 3,998,656 4,378,753 94 315,835 485,901 -9,864,127 -10,843,234 1
74 347,850 34,785 27,828 4,378,753 4,791,666 95 322,152 495,619 -10,843,234 -11,881,015 1
75 361,764 36,176 28,941 4,791,666 5,240,117 96 328,595 505,531 -11,881,015 -12,980,596 1
76 376,234 37,623 30,099 5,240,117 5,727,049 97 335,167 515,642 -12,980,596 -14,145,268 1
77 391,283 39,128 31,303 5,727,049 6,255,644 98 341,870 525,954 -14,145,268 -15,378,486 1
78 406,935 40,693 32,555 6,255,644 6,829,343 99 348,708 536,474 -15,378,486 -16,683,883 1
79 423,212 42,321 33,857 6,829,343 7,451,869 100 355,682 547,203 -16,683,883 -18,065,281 1
80 440,141 44,014 35,211 7,451,869 8,127,244 101 362,796 558,147 -18,065,281 -19,526,692 1
81 457,746 45,775 36,620 8,127,244 8,859,818 102 370,051 569,310 -19,526,692 -21,072,336 1
82 476,056 47,606 38,084 8,859,818 9,654,293 103 377,453 580,696 -21,072,336 -22,706,649 1
83 495,098 49,510 39,608 9,654,293 10,515,754 104 385,002 592,310 -22,706,649 -24,434,292 1
84 514,902 51,490 41,192 10,515,754 11,449,697 105 392,702 604,156 -24,434,292 -26,260,163 1
85 535,498 53,550 42,840 11,449,697 12,462,063 106 400,556 616,239 -26,260,163 -28,189,410 1
86 556,918 55,692 44,553 12,462,063 13,559,273 107 408,567 628,564 -28,189,410 -30,227,445 1
87 579,195 57,920 46,336 13,559,273 14,748,270 108 416,738 641,136 -30,227,445 -32,379,953 1
88 602,363 60,236 48,189 14,748,270 16,036,557 109 425,073 653,958 -32,379,953 -34,652,909 1
89 626,457 62,646 50,117 16,036,557 17,432,244 110 433,574 667,037 -34,652,909 -37,052,592 1
90 651,516 65,152 52,121 17,432,244 18,944,096 111 442,246 680,378 -37,052,592 -39,585,599 1