We are given the following data on the value of a portfolio over time: Amount Year...

Transcribed Image Text:

We are given the following data on the value of a portfolio over time: Amount Year 0 $7,500.00 3 $7,860.00 5 $8,130.00 7 $8,415.00 10 $8,870.00 11 $9,030.00 14 $9,550.00 17 $10,110.00 20 $10,720.00 Use Microsoft Excel, the following model, and the solve function to find the best-fitting model for the data. Round all numbers to two decimal places! TotalValue (time) = CashAmount + Deposit Amount * ratetime Hint: The following steps are recommended 1. You need to start with some initial values for CashAmount, DepositAmount, and Rate. For example, CashAmount =3000, DepositAmount =3000 and Rate = 1.01. 2. Reproduce the following table using the above model and your initial guesses! Year Amount 0 7,500.00 Predicted (Amount) 20 10,720.00 Sum of Error^2 Error^2 = [Predicted (Amount) -Amount] ^2 [Sum of the above rows!] 3. Use the solver function and minimize the sum of Error^2 by changing CashAmount, DepositAmount, and Rate. At the end of the process, you will find a model that fits that data the best (it will have the lowest Sum or Error^2). 4. Provide the optimal model parameters: CashAmount, DepositAmount and Rate! We are given the following data on the value of a portfolio over time: Amount Year 0 $7,500.00 3 $7,860.00 5 $8,130.00 7 $8,415.00 10 $8,870.00 11 $9,030.00 14 $9,550.00 17 $10,110.00 20 $10,720.00 Use Microsoft Excel, the following model, and the solve function to find the best-fitting model for the data. Round all numbers to two decimal places! TotalValue (time) = CashAmount + Deposit Amount * ratetime Hint: The following steps are recommended 1. You need to start with some initial values for CashAmount, DepositAmount, and Rate. For example, CashAmount =3000, DepositAmount =3000 and Rate = 1.01. 2. Reproduce the following table using the above model and your initial guesses! Year Amount 0 7,500.00 Predicted (Amount) 20 10,720.00 Sum of Error^2 Error^2 = [Predicted (Amount) -Amount] ^2 [Sum of the above rows!] 3. Use the solver function and minimize the sum of Error^2 by changing CashAmount, DepositAmount, and Rate. At the end of the process, you will find a model that fits that data the best (it will have the lowest Sum or Error^2). 4. Provide the optimal model parameters: CashAmount, DepositAmount and Rate!