Inspired by the recent success of the Canadian Olympic swimming team, Louisa wants to
open a swim club to develop interest in swimming among children. She wants to determine
the number of morning and afternoon classes the club can offer to make the highest weekly
profit without exceeding the maximum legal pool capacities and other practical conditions.
Use Solver to find the maximum weekly profit using the following constraints:
Each type of lesson (e.g., Guppy) is scheduled at least once in the morning and once
in the afternoon
Each type of lesson is scheduled 3 times per week or more
Each type of lesson is scheduled 10 times per week or less
The total number of morning classes is 10 or less
The total number of afternoon classes is 15 or less
The total number of classes per week is 20
To help you get started, you can design your spreadsheet as below:
Include the Answer Report, so that I can see that you used Solver to solve the problem.
Profit per lesson type is given. Do not change these. You will have to create your
spreadsheet with appropriate formula so that you can find the Total Weekly Profit. For
Solving Method, Simplex LP should do.
2.You are interested in purchasing a condo in downtown Toronto. You recently came across
a unit on John St. that was listed for $600,000. You are able to put a down payment of 20%,
but will need to obtain a mortgage. Your bank is offering a fixed rate of 5.5% for a 25-year
mortgage.
Using the Data Table function in Excel, prepare a couple of “mortgage payment table”.
In the first table, show how a change in interest rate will impact your monthly mortgage
payments. Vary interest rates from 2% to 8% in increments of 50 basis points.
In the second table, show how your monthly mortgage payments would vary depending on
the interest rate and price of condo. Use the same interest rate range as before. For the
condo price, use the range between 500K and 700K in increments of 25K.
Lastly, prepare 3 scenarios using the Scenario Manager function of Excel. Work with 3
variables: interest rate, price of condo, down payment percentage. You can determine how
each scenario would look like (e.g., optimistic scenario: rate is 2.5%, price of condo is 600K,
down payment is 30%. You will create 2 more scenarios). For Results, of course, we are
interested in the monthly payment.
For the monthly mortgage payment, use the PMT function. E.g., =-PMT(Rate, Nper, loan
amount). Since you are looking for monthly payment, the rate you should use is the
monthly interest rate. Nper refers to the number of payment period. Since we are working
with a 25-years mortgage, there are 300 monthly payment periods. Note the negative sign
in front of PMT. We do this so that the answers are positive numbers.
Remember for the data tables and scenario manager to work, you need to set up properly.
This means creating a “calculator” first. See below for an example. You can then work with
Data Table and Scenario Manager.
Also remember to format your worksheet as appropriate, since you are hoping to impress
your banker (with your spreadsheet) that you have done your “homework” and obtain a
good rate.
Problem 3 (5 marks)
Connect your Excel worksheet to a dataset on leading cause of death in the United States.
The dataset can be found at https://www.data.gov/ In the search box, type “Leading Causes
of Death in the United States” and then press enter. Work with a dataset called “NCHS -
Leading Causes of Death: United States”.
You can connect to the web data source by copying the link from the web. Right click your
mouse on CSV button and select copy link. Paste the link to URL box when you are trying to
connect using Excel.
Before loading the data, transform your data first. Delete the column named “113 Cause
Name”, leaving the columns for Year, Cause Name, State, Deaths, and Age-adjusted Death
Rate (per 100,000 deaths).
There are 10865 records covering 10 causes of death (excluding a category called “all
causes”, which really means all other causes) in 50 states (plus Washington DC and the US)
from 1999 to 2017. That is a lot of data!!
Once your spreadsheet is connected and data transformed, perform 2 “queries” of your
interest. State the query/question and answer using Excel and show your work. Your work
will be assessed based on the "complexity" of the question and the "thoroughness" of the
answer. However, you don't need to go too deep with this.
Here is an example. Of course, your queries should not be similar to mine!
Question:
What is the biggest of cause of death in Mississippi (the state with the highest death rate per
100,000) and forecast the trajectory of death rate from the cause.
Answer:
Cancer is the leading cause of death in Mississippi at 204 deaths per 100000. Plotting a
trendline and projecting forward for 2 years, we can conclude that the death from cancer is
declining in Mississippi.
