Simulation Project $1$

Excel Worksheet for Long$-$Term Savings

Name $\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

What you need to do in this project:

$(1)$ Develop an Excel spreadsheet for calculating personal long$-$term savings,

$(2)$ Do calculations by using your spreadsheet, and record the results in the Table of Results on the next page.

Save the Excel Template to your computer:

In Blackboard, $$Open$$ the template of the Excel spreadsheet, Template$-$Project $1$; and then save it $($Save as$)$ in your computer as an Excel file $(.$xls or $.$xlsx$)$ with the following new name:

your name$-$Project $1,$ for example, John$-$Smith$-$Project $1.$

Develop your spreadsheet based on this template in your computer.

The Template for Project $1$:

Guidelines and Notes:

$$ Your worksheet should do the calculations up to year $50.$

$$ Make sure of understanding meanings of the columns in the Template, as well as the relationship among the columns.

$$ In the finished worksheet, you enter values in cells E$3($initial amount$),$ E$4($interest rate$)$ and E$5($annual $ in$),$ the balance at the end of year $50$ will show in cell E$6.$

$$ In the template, the numbers in cells E$3,$ E$4,$ and E$5$ are just arbitrary examples.

$$ Use Excel formulas or Excel functions to let Excel do all calculations, rather than doing calculations by hand or your calculators.

$$ Use relative or absolute cell addresses as far as possible, rather than specific numbers, in a formula or a function to facilitate the Excel worksheet generation.

$$ Start each formula with an equality sign $=.$ For example, $=$F$10$; $=$B$9*$C$9.$

$$ Use Copy$-$Paste in Excel as far as possible.

$$ The key F$9$ is used for $$recalculating$.$

Do Calculations by using your Excel worksheet and type the results in the blanks in the table below.

Table of the Results: $($Assume in each case $$$ amount taken out during the year$=0.)$

Balance at beginning of year $1$ Annual interest rate $ amount put in annually every year Balance at the end of year $50$

$$10,000$

$3\%$ $$0$

$$10,000$

$8\%$ $$0$

$$03\%$ $$1,200($i$.$e$.,$ $$100$ per month$)$

$$0$

$5\%$ $$1,200$

$$0$

$8\%$ $$1,200$

$$03\%$ $$4,800($i$.$e$.,$ $$400$ per month$)$

$$0$

$8\%$ $$4,800$

$$10,000$

$4\mathrm{.}5\%$ $$2,400$

$$10,0006\%$ $$7,200($i$.$e$.,$ $$600$ per month$)$

Submit your works:

You need to submit two $(2)$ files of your works.

$(1)$ Submit this Word file, $$Project $1,$ Instruction and Results$,$ in class, with your answers filled in the table above.

$(2)$ Email your Excel spreadsheet to me as an attachment of the email.

$($a$)$ The e$-$mail title: Proj$-1-$YourName, ex$.-$Proj$-1-$JohnSmith.

$($b$)$ Put your Excel spreadsheet as the attachment.

$($c$)$ Your Excel file$$s name: Your Name $-$ Project $1($ex$.$ John$-$Smith$-$Project $1),$ with extension $.$xls or $.$xlsx$.$

$($d$)$ Your Excel file must be readable on Windows PC and run in MS Excel. If you use Apple computer, you need to convert your file to $.$xlsx Excel file which can be opened on Windows.

For your reference:

Detailed Guidelines for doing the project Personal Long$-$Term Saving

$(1)$ In cell B$9,$ enter $=$$E$$3,$ which is the initial amount.

$(2)$ In cell C$9,$ enter $=$$E$$4,$ which is the annual interest rate. $($$$-$signs are for fixing the cell address in later$-$on copying.

$(3)$ In cell D$9,$ enter $=$B$9*$C$9,$ which calculates the annual yield of year $1.$

$(4)$ In cell E$9,$ enter $=$$E$$5,$ which is annual $ in$.($$$-$signs are for fixing the cell address in later$-$on copying.

$(5)$ In cell F$9,$ enter $=$B$9+$D$9+$E$9,$ which is the balance at the end of year $1.$

$(6)$ In B$10,$ enter $=$F$9.($i$.$e$.,$ beginning balance of year $2=$ ending balance of year $1)$

$(7)$ Copy C$9$:F$9$ to C$10$:F$10.$

$(8)$ Copy Row $10($A$10$:F$10)$ all the way down to Row $58($A$58$:F$58,$ which is for year $50.)$

$(9)$ In E$6,$ enter $=$F$58.($To bring ending balance of year $50$ up to this cell.$)$

$(10)$ Carefully check the resulted worksheet, to avoid demonstrable or weird errors.

$(11)$ Now, you can change initial amount in E$3,$ annual interest rate in E$4,$ annual dollar in in E$5,$ to see the result of $50-$year savings in E$6$ in various scenarios and plans.