Applications Programming --- Lab 3

Objective:

• Learn to build a model using Excel
• Get familiar with Excel formula, especially with cell references and some basic built-in functions

References:

• Lab 2 referneces
• General Models
• Excel Models

Problem 1 Description

We have a planet revolving around a sun and a moon revolving around the planet. We want to graph the orbit of the moon with respect to the sun.

To simplify the problem, we have the following assumptions:

• The orbit of the planet around the sun is a circle.
• The orbit of the moon around the planet is also a circle.
• Both orbits lie in the same plane.
• The radius of the planet orbit is R length unit.
• The radius of the moon orbit around the planet is r length unit.
• A full orbit of the planet takes one time unit (one year.
• The moon orbits around the planet m times in this one time unit (one year.

Then at time t (as percentage of a full planet orbit), the planet's position relative to the sun is:

<xp, yp> = <R*cos(2*Pi()*t), R*sin(2*Pi()*t)>

and the moon's position relative to the planet is:

<xr, yr> = <r*cos(2*Pi()*m*t), r*sin(2*Pi()*m*t)>

and the moon's position relative to the sun is:

<xm, ym> = <R*cos(2*Pi()*t) + r*cos(2*Pi()*m*t), R*sin(2*Pi()*t) + r*sin(2*Pi()*m*t)>
or, <xm, ym> = <xp+xr, yp+yr>

Problem 2 Description

A family wants to start saving for their child's post-secondary education. The family opened a savings account for exactly that purpose. According to their income and spending situation, they decided to save certain amount every month and deposit the savings to their account. An example of their saving plan for each month is shown on the worksheet named "Education Saving Plan" in the Lab 3 input file

The bank holding their savings account guarantees a yearly 2.5 percent interest growth on the money saved in the account at the beginning of each year.

The government also promise to match 25 percent of their new deposit, up to $500 (on a total of $2000 deposit), each year.

Your tasks

• Download the Lab 3's template workbook: lab3-input.xlsx
• In the worksheet named "Orbit Model", develop an Excel model for Problem 1, and insert a chart to visually present the orbit data.

  In the worksheet named "Education Saving Plan", develop an Excel model for Problem 2. This model should show the following columns from the start age of a child until when a child is 17 years old, one year's data in one row:

  • age
  • Initial account balance of the year
  • Yearly interest growth on each year's initial balance
  • Yearly contribution as the sum of all the months' savings
  • Government match for the year
  • End year account balance

  In this model, treat the government match rate, the maximum yearly match amount, the annual interest growth rate, the start age, and the family's monthly savings as parameters.

  A list of useful Excel functions include SUM, MAX, MIN, and AVERAGE.

• Save your work.

When you work on building the Excel models, you need to pay attention to the following things:

• Did you use the proper parameters in your model?
• Did you use the correct formula in your model?
• Did you use the right type of cell references in your formula? You should write similar formula once and copy-paste them to all the other appropriate cells.

There is a weekly Assignment 2 following this lab.