Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Financial Planning.xlsx by hedongchenchen

VIEWS: 22 PAGES: 5

									Microsoft Excel 12.0 Answer Report
Worksheet: [Financial Planning.xlsx]Model
Report Created: 9/5/2010 8:56:05 PM


Target Cell (Min)
    Cell           Name            Original Value     Final Value
  $Q$19 Initial Cash Requirement         $1,708.67       $1,728.79


Adjustable Cells
    Cell           Name            Original Value     Final Value
  $D$19 Initial Cash                      1708.67          1728.79
  $E$19 Bond 1                              147.73          144.99
  $F$19 Bond 2                              190.85          187.86
  $G$19 Bond 3                              201.34          228.19
  $H$19 Year 1 Savings                      646.12          636.15
  $I$19 Year 2 Savings                      509.23          501.61
  $J$19 Year 3 Savings                      354.86          349.68
  $K$19 Year 4 Savings                      185.32          182.68
  $L$19 Year 5 Savings                        0.00             0.00
  $M$19 Year 6 Savings                        0.00             0.00
  $N$19 Year 7 Savings                        0.00             0.00
  $O$19 Year 8 Savings                        0.00             0.00


Constraints
   Cell          Name                Cell Value        Formula          Status    Slack
  $Q$8 Year 1 Cash Flow                     430.00   $Q$8=$S$8        Not Binding     0
  $Q$9 Year 2 Cash Flow                     210.00   $Q$9=$S$9        Not Binding     0
  $Q$10 Year 3 Cash Flow                    222.00   $Q$10=$S$10      Not Binding     0
  $Q$11 Year 4 Cash Flow                    231.00   $Q$11=$S$11      Not Binding     0
  $Q$12 Year 5 Cash Flow                    240.00   $Q$12=$S$12      Not Binding     0
  $Q$13 Year 6 Cash Flow                    195.00   $Q$13=$S$13      Not Binding     0
  $Q$14 Year 7 Cash Flow                    225.00   $Q$14=$S$14      Not Binding     0
  $Q$15 Year 8 Cash Flow                    255.00   $Q$15=$S$15      Not Binding     0
Microsoft Excel 12.0 Sensitivity Report
Worksheet: [Financial Planning.xlsx]Model
Report Created: 9/5/2010 8:56:05 PM


Adjustable Cells
                               Final Reduced Objective     Allowable    Allowable
    Cell           Name        Value   Cost   Coefficient   Increase    Decrease
  $D$19    Initial Cash       1728.79    0.00           1        1E+30            1
  $E$19    Bond 1              144.99    0.00           0 0.067026339 0.013026775
  $F$19    Bond 2              187.86    0.00           0 0.012795531 0.020273774
  $G$19    Bond 3              228.19    0.00           0 0.022906851 0.749663022
  $H$19    Year 1 Savings      636.15    0.00           0 0.109559907 0.05507386
  $I$19    Year 2 Savings      501.61    0.00           0 0.143307365 0.056948823
  $J$19    Year 3 Savings      349.68    0.00           0 0.210854199 0.059039182
  $K$19    Year 4 Savings      182.68    0.00           0 0.413598622 0.061382404
  $L$19    Year 5 Savings        0.00    0.06           0        1E+30 0.064025159
  $M$19    Year 6 Savings        0.00    0.01           0        1E+30 0.012613604
  $N$19    Year 7 Savings        0.00    0.02           0        1E+30 0.021318233
  $O$19    Year 8 Savings        0.00    0.67           0        1E+30 0.670839393

Constraints
                              Final Shadow Constraint Allowable      Allowable
   Cell          Name         Value  Price   R.H. Side   Increase    Decrease
  $Q$8     Year 1 Cash Flow   430.00    1.00        430       1E+30 1728.793855
  $Q$9     Year 2 Cash Flow   210.00    0.96        210       1E+30 661.5938616
  $Q$10    Year 3 Cash Flow   222.00    0.92        222       1E+30 521.6699405
  $Q$11    Year 4 Cash Flow   231.00    0.89        231       1E+30 363.6690626
  $Q$12    Year 5 Cash Flow   240.00    0.85        240       1E+30 189.9881496
  $Q$13    Year 6 Cash Flow   195.00    0.76        195 2149.927647 157.8558478
  $Q$14    Year 7 Cash Flow   225.00    0.72        225 3027.962172 198.1879195
  $Q$15    Year 8 Cash Flow   255.00    0.67        255 1583.881915         255
     A B          C              D            E          F          G             H
1    Financial Planning
2                                                                                            Decisio
3      Objective Function: Initial Cash     Bond 1     Bond 2     Bond 3    Year 1 Savings
4        Coefficients            1
5
6
7      Constraint Definitions:                                                                  Co
8        Year 1 Cash Flow        1           -1.15        -1        -1.35        -1
9        Year 2 Cash Flow                   8.875%     5.500%     11.750%       1.04
10       Year 3 Cash Flow                   8.875%     5.500%     11.750%
11       Year 4 Cash Flow                   8.875%     5.500%     11.750%
12       Year 5 Cash Flow                   8.875%     5.500%     11.750%
13       Year 6 Cash Flow                  108.875%    5.500%     11.750%
14       Year 7 Cash Flow                             105.500%    11.750%
15       Year 8 Cash Flow                                        111.750%
16
17
18     Solver Calculations: Initial Cash    Bond 1     Bond 2     Bond 3    Year 1 Savings
19                            1728.79       144.99     187.86     228.19       636.15
           I              J              K              L              M              N
1
2                  Decision Variables
3    Year 2 Savings Year 3 Savings Year 4 Savings Year 5 Savings Year 6 Savings Year 7 Savings
4
5
6
7                    Coefficients
8
9         -1
10       1.04            -1
11                      1.04            -1
12                                     1.04             -1
13                                                     1.04            -1
14                                                                    1.04            -1
15                                                                                   1.04
16
17
18   Year 2 Savings Year 3 Savings Year 4 Savings Year 5 Savings Year 6 Savings Year 7 Savings
19      501.61         349.68         182.68           0.00           0.00           0.00
           O          P              Q                  R       S
1
2
3    Year 8 Savings
4
5
6
7                                                    Operator Limit
8                                 430.00                =      430
9                                 210.00                =      210
10                                222.00                =      222
11                                231.00                =      231
12                                240.00                =      240
13                                195.00                =      195
14                                225.00                =      225
15         -1                     255.00                =      255
16
17
18   Year 8 Savings       Initial Cash Requirement
19        0.00                     $1,728.79

								
To top