MAT 144 GCU Savings & Loan Analysis Budget Cost Projection & Conversions Excel

      Major Assignment 2 Grading Sheet
Competency
Name
Requirements for full credit
You have entered your full name in the field provided.
The interest rates you have entered come from the mortgage rates table and
match those for the months and years provided.
You have explicitly formatted the cells to display as Percentage with 2
decimal places of precision.
Your Electric, Gas, Water, and Other entries are reasonable values, with at
least two nonzero entries. For zero entries, you have explicitly entered
values of 0.
Monthly Costs
Your Total Cost and Monthly Savings formulas are correct and use
and Savings
appropriate cell references.
All cost cells are formatted as Currency showing the $ symbol and with 2
decimal places of precision.
You have brought forward your monthly savings amount, using Excel
formulas.
Your number of contributions per year and number of years entries are
correct.
Savings and
Savings Table
Your formulas for total amount saved, total contributions, and total accrued
Loan Analysis
interest are correct and use cell references.
All cells are explicitly formatted with the format given in the last column of
the table.
You have entered the correct number of contributions per year and number
of years.
Interests
Rates
Loan Table
Your formulas for payment amount, total amount paid, and total amount of
interest paid are correct and use cell references as inputs.
All cells are explicitly formatted with the format given in the last column of
the table.
You have correctly brought forward your savings and loan amounts, using
cell references.
Your savings and loan cells are explicitly formatted with the format given in
Comparison
the last column of the table.
You have answered the comparison questions correctly, answering either
“yes” or “no” for each one.
Your reference CPI is correct for the month and year given.
Your next-year CPI, month, and year are correct.
Inflation Rate
Your inflation rate calculation is correct.
Calculation
Your CPI values and inflation rate are explicitly formatted as indicated in the
instructions.
Budget Cost
Projection
Budget Cost
Projection
Budget
Projections
Monthly
Savings
Conversions
You have correctly entered your Budget Total from cell G21 of the Monthly
Budget sheet from your Major Assignment 1.
Your “value of t” entries are correct.
Your 1-year, 5-year, and 10-year projections are correct Excel formulas using
cell references.
Your percent increase calculations are correct Excel formulas using cell
references.
Your Current Budget, Projected Budget and Percent Increase cells are
formatted as indicated in the instructions.
You have brought forward your monthly savings amount from the Savings
and Loan Analysis sheet, using an Excel formula with a sheet and cell
reference.
You have entered the first two letters of your first and last names, using the
letter M if one or both names consist of only one letter.
You have chosen appropriate countries from the list provided below the
table, using the procedure described in the instructions.
You have entered the date(s) on which you looked up the exchange rates for
your currencies, and all dates are within 2 weeks of the due date of your
assignment.
You have entered both the full name of your country’s currency and the
correct currency code as indicated on the website.
Currency You have provided each exchange rate to at least 5 significant digits, and the
Conversions exchange rate matches the rate for the date you looked it up.
Your savings amount in the foreign currency is a correct Excel formula, using
cell references.
Your calculation of the value of foreign currency units into dollars are correct
Excel formulas, using cell references. (Note that the amount to convert is
autogenerated and may differ from the amount shown in assignment
resources.)
The cells containing your dates, savings amounts, and value of foreign
currency converted to dollars are correctly formatted as specified in the last
column of the table.
nt 2 Grading Sheet
(optional for
student use) Did
you meet the
requirements?
Points
Your points
possible
1
3
3
4
4
6
3
6
18
18
6
18
15
6
6
3
Subtotals
120
1
3
3
3
0
Scoring comments
2
3
6
6
7
Subtotals
34
0
2
4
4
4
8
4
8
8
12
Subtotals
54
0
Totals
208
0
Percentage
100,00%
0,00%
Scaled out of
100
100,00
0,00
1 In Major Assignment 1, you created a monthly budget, which included a recurring cost for utilities. Here, you’ll consider
off the cost of those improvements.
Below, you’ll start by entering and adding up the costs of your electric, gas, water, and other energy utilities. Then, give
the next 5, 10, and 15 years if you contribute your monthly savings into an account with a given APR. Here, you’ll use the f
per year for t years and earning interest at an annual percentage rate of r, the total amount A accrued after t years is give
A = P*((1+r/n)^(n*t)
Next, you’ll develop a cost to install energy-efficient improvements (installing energy-efficient doors and windows, adding in
payment if you were to finance the installation cost by a loan of 5, 10, or 15 years. Here, you will use this formula: give
off the loan with n payments per year for t years (with payments made at the end of each period) is given by (in Excel forma
PMT = P*(r/n)/(1
For all the above calculations, you will look up rates in the following historical table of 30-year fixed mortgage rates, based
http://www.freddiemac.com/pmms/
(Mortgage Rates
Assignment Advisory: You must use the la
2 Enter your full name here
provided free by GCU; contact the Help Desk
version of Excel or a different spreadsheet prog
from or into this te
(If fewer than 9 letters, add additional
arbitrary letters)
Savings
3 Look up three interest rates from
the historical mortgage rate table,
formatting them as Percentage with 2
decimal places. Make sure to enter
these as percentage values. For
example, 4.03 in the table is 4.03% or
0.0403.
APR Year
Your full name entry must be
longer
APR Month
Your full name entry must be
longer
Interest Rate
Electric
Gas
4 Enter, or estimate, your monthly
utility costs, then calculate your total
monthly cost and monthly savings.
Format all cells as Currency showing
the $ symbol and with 2 decimals of
precision.
Water
Other
Total Cost
4 Enter, or estimate, your monthly
utility costs, then calculate your total
monthly cost and monthly savings.
Format all cells as Currency showing
the $ symbol and with 2 decimals of
precision.
Monthly Percent Savings
Complete the first interest rate
entry in section 3 above
Monthly Savings (total cost times
percent savings)
5 Complete this table for your 5-year,
10-year, and 15-year savings
Contribution amount (P)
(Bring forward your Monthly Savings
amount, using a formula, for each
entry)
APR from the table (r)
Calculation #1
(5-year savings)
Calculation #2
(10-year savings)
Complete the second interest rate
entry in section 3 above
Complete the second interest
rate entry in section 3 above
Number of contributions per year (n)
Number of years (t)
Total amount saved (A):
Total contributions:
Total accrued interest:
Loan
6 Continue by completing this table
for your 5-year, 10-year, and 15-year
loans, based on the principal and
interest rates given with monthly
payments
Calculation #1
(5-year loan)
Calculation #2
(10-year loan)
Loan principal (P)
Your full name entry must be
longer
Your full name entry must be
longer
APR from the table (r), with a slightly
higher rate for longer loans
Complete the third interest rate
entry in section 3 above
Complete the third interest rate
entry in section 3 above
Number of contributions per year (n)
Number of years (t)
Payment amount (PMT):
Total amount paid over the time of the
loan:
Total amount of interest paid:
Comparison
7 Use Excel formulas to transfer your
amounts from above
Total savings from energy
improvements
Total loan payments
Have you broken even at this point
(yes or no)? (“Breaking even” here
means that your total savings outweigh
your total loan payments.)
After 5 years
After 10 years
ties. Here, you’ll consider making some energy-saving home improvements and compare your potential savings against paying
gy utilities. Then, given a percent savings due to your energy-saving improvements, you’ll calculate how much you’ll save over
PR. Here, you’ll use the following formula for your calculations: given an amount P contributed at the end of each of n periods
rued after t years is given by (in Excel format):
A = P*((1+r/n)^(n*t)-1)/(r/n)
ors and windows, adding insulation, upgrading to more efficient appliances or lights, and so on) and then calculate a monthly
use this formula: given a loan principal amount P and an annual interest rate of r, the payment amount PMT required to pay
is given by (in Excel format):
MT = P*(r/n)/(1-(1+r/n)^(-n*t))
ed mortgage rates, based on the years and months specified in step 6 below.
w.freddiemac.com/pmms/pmms30.html
Mortgage Rates)
sory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. (This is
CU; contact the Help Desk for more information and help installing the software.) Using an earlier
ifferent spreadsheet program may result in missing or corrupted template elements. Copying cells
from or into this template may likewise result in corrupted data.
Your full name entry must be
longer
Your full name entry must be
longer
Your full name entry must be
longer
Your full name entry must be
longer
Legend
If a cell is shaded
You should
Blue
Enter a text response
Green
Enter a number
Gold
Enter an Excel formula
Any other color
Calculation #3
(15-year savings)
Format the entries in each
row as…
…Currency with 2 decimal
places
Complete the second interest
rate entry in section 3 above
…a Number with 0 decimal
places
…a Number with 0 decimal
places
…Currency with 2 decimal
places
…Currency with 2 decimal
places
…Currency with 2 decimal
places
Calculation #3
(15-year loan)
Format the entries in each
row as…
Your full name entry must be
longer
Complete the third interest rate
entry in section 3 above
…a Number with 0 decimal
places
…a Number with 0 decimal
places
…Currency with 2 decimal
places
Make no changes
…Currency with 2 decimal
places
…Currency with 2 decimal
places
After 15 years
Format the entries in each
row as…
…Currency with 2 decimal
places
…Currency with 2 decimal
places
against paying
ou’ll save over
ch of n periods
e a monthly
uired to pay
Your name (brought forward from the
Savings and Loan Analysis sheet):
0
8 On the Monthly Budget sheet in Major Assignment 1, you evaluated your current expenses. Here, you will project your bu
rate that you develop from values in the Consumer Price Index.
As a first step, look up the CPI value for the given month and year as well as the CPI value one year later; then, calculate
values. Use this procedure to look up the CPI value:
1. Go to Bureau of Labor Statistics page link https://data.bls.gov/cgi-bin/surveymost?cu (or use link below)
2. Check the box to the left of text “U.S. city average, All items – CUUR0000SA0”
3. Press the “Retrieve Data” button at the bottom of the list. This should take you to a CPI table for about th
Here, format your CPI entries as Number with 3 decimals of precision, and format your yearly inflation rate as a Percentage w
(CPI Values)
CPI Value
Month
Your full name entry must
be longer
Reference CPI
CPI one year later
Yearly inflation rate (r)
9 Next, enter your budget total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. Then, use the fo
formula to project your monthly budget forward 1, 5, and 10 years into the future:
A = B*(1+r)^t
where A is the budget after t years; B is the initial budget; and r is the yearly inflation rate. Here, also calculate how m
budget is in percent than your initial budget. Format your Projected Budget entries as Currency with the $ symbol and 2 dec
precision; format your Percent Increase cells as Percentages with 2 decimals of precision.
Value of t
Current Monthly Budget (B)
Monthly Budget next year
Monthly Budget in 5 years
Monthly Budget in 10 years
Projected Budget
ent expenses. Here, you will project your budget costs forward, using an inflation
CPI value one year later; then, calculate a yearly inflation rate based on those CPI
bin/surveymost?cu (or use link below)
CUUR0000SA0″
s should take you to a CPI table for about the last 10 years.
t your yearly inflation rate as a Percentage with 2 decimals of precision.
Year
Your full name entry
must be longer
m your Major Assignment 1. Then, use the following
Legend
If a cell is shaded
You should
Blue
Enter a text response
Green
Enter a number
Enter an Excel
formula
Make no changes
Gold
Any other color
tion rate. Here, also calculate how much larger each
ies as Currency with the $ symbol and 2 decimals of
Percent Increase over
Current Budget
10 On this second conversion sheet, you will convert your monthly savings into the equivalent amounts in several foreign
amount of the local currency into the equivalent number of US dollars.
Start by transferring your monthly savings from the Savings and Loan Analysis sheet, using an Excel formula that reference
Your monthly savings in dollars
11 Now, from the list below the table below, select four countries that start with the first two
letters of your first and last names. If your first or last name is only one letter long, use the letter
M as the second letter of each name that is one letter long. If there is no country starting with a
particular letter or you have run out of countries to choose from for a particular letter, go to the
next letter of the alphabet that you still have available choices for and select a country starting
with that letter. (If you are at the letter Z, go back to A.)
For each country, identify the name of the country’s currency, the currency code (based on the ISO-4217 standard), and th
following web page: https://www.xe.com/currencyconverter
(Currency Converter)
Then, convert your monthly savings above into this currency and a given number of units of the local currency into dollars.
formulas that use a cell reference for the exchange rate; you may not use the currency converter link for this calculation (a
your calculation there).
Add special formatting as indicated in the last column of the table. Other entries may use general formatting.
An example is provided for you. Note that this country is not available for you to choose from the list.
Example
The letter
T
Country starting with the letter
(or next available letter)
Tajikistan
The date that you looked up the
conversion rate (must be within
2 weeks of your assignment due
date)
Full name of the country’s
currency as listed on the XE
website
Currency code (ISO-4217)
5/23/2020
Tajikistani somoni
TJS
First letter of your
first name
Second letter of
your first name
Exchange rate for the currency
to at least 5 significant digits (or
exact rate if there are fewer
than 5 significant digits)
10,26863117
Your savings in the country’s
currency. Note that you must
enter a formula here and then
format the cell to display the
currency code; do not enter text
in this cell.
TJS 0,00
Your full name entry must be
longer
$97,38
Choose your countries from this list
Afghanistan
Cambodia
Guatemala
Lebanon
Albania
Canada
Guernsey (UK)
Liberia
Algeria
Cayman Islands (UK)
Guinea
Libya
Angola
Chile
Guyana
Macau (China)
Argentina
Armenia
China
Colombia
Haiti
Honduras
Madagascar
Malawi
Aruba (Netherlands)
Comoros
Hong Kong (China)
Malaysia
Hungary
Maldives
Iceland
Mauritania
Azerbaijan
Congo, Democratic
Republic of the
Costa Rica
Bahamas
Croatia
India
Mauritius
Bahrain
Cuba
Indonesia
Mexico
Bangladesh
Czechia
International
Moldova
Monetary Fund (IMF)
Barbados
Denmark
Iran
Mongolia
Belarus
Belize
Djibouti
Dominica
Iraq
Isle of Man (UK)
Bermuda (UK)
Dominican Republic
Israel
Morocco
Mozambique
Myanmar (formerly
Burma)
Bhutan
Bolivia
Bosnia and Herzegovina
Egypt
Jamaica
Namibia
Eritrea
Ethiopia
Japan
Jersey (UK)
Nepal
New Zealand
Australia
Botswana
Falkland Islands (UK)
Jordan
Nicaragua
Brazil
Fiji
Kazakhstan
Nigeria
Brunei
Gambia
Kenya
Bulgaria
Georgia
Kuwait
Burundi
Ghana
Kyrgyzstan
North Korea
North
Macedonia (formerl
y Macedonia)
Norway
Cabo Verde
Gibraltar (UK)
Laos
Oman
alent amounts in several foreign currencies and convert a given
an Excel formula that references the appropriate cell directly:
Legend
If a cell is shaded
Blue
Green
Gold
Any other color
You should
Enter a text
response
Enter a number
Enter an Excel
formula
Make no changes
4217 standard), and the exchange rate for $1, using the
f the local currency into dollars. These calculations must be Excel
verter link for this calculation (although you’re welcome to check
general formatting.
om the list.
First letter of your
last name
Second letter of
your last name
Format this
entry as
Date
Currency with
the country’s
currency code
as a symbol
Currency with
the $ symbol
Pakistan
Switzerland
Papua New Guinea
Syria
Paraguay
Taiwan
Peru
Tanzania
Philippines
Poland
Qatar
Thailand
Tonga
Trinidad and
Tobago
Romania
Tunisia
Russia
Turkey
Rwanda
Turkmenistan
Saint Helena (UK)
Uganda
Samoa
Ukraine
Sao Tome and
Principe
Saudi Arabia
Serbia
United Arab
Emirates
United Kingdom
Uruguay
Seychelles
Uzbekistan
Sierra Leone
Singapore
Somalia
Vanuatu
Venezuela
Vietnam
South Africa
South Korea
Sri Lanka
Sudan
Suriname
Sweden
Wallis and
Futuna (France)
Yemen
Zambia
Major Assignment 2
SAVINGS AND LOAN ANALYSIS, BUDGET COST
PROJECTION, AND CONVERSIONS
How to Use the Grading Sheet
As for Major Assignment 1, the Grading
Sheet lists all the elements you will be
required to complete in the Excel sheet,
along with relative points for each.
As you complete elements, you can check
them off in the column titled “Did you meet
the requirements?” (see the screenshot at
the right). This will also let you track your
progress and identify items you still need to
complete.
After your assignment is graded, your
scaled score will show up at the bottom.
Track your
progress in
this column.
Assignment Advisory and Your Name
1. On the Savings and Loan Analysis sheet,
the Assignment Advisory reminds you the
you should use the desktop version of Excel
for Microsoft 365 to complete your
assignment; other programs (like Google
Sheets or Excel Online) may not work
correctly, and you may get fewer than full
points as a result.
2. When you’re ready, start by entering your
full name in cell B22. If your full name is
shorter than 9 letters long, add additional
letter of your choice at the end until you
reach a total of 9 letters. Note that your
name is used to generate data on each
sheet of the assignment, so you must
complete this cell to be able to continue.
Follow the Legend
As with Major Assignment 1, pay attention to
the Legend, which you will see in each
Major Assignment. This gives you a visual
indicator of what to enter into each cell in
the assignment.
You will enter text into blue-shaded cells,
numbers into green-shaded cells, and Excel
formulas with appropriate cell references
into gold-shaded cells.
Your cell entries must be of the given type to
be counted as correct. For example, if a cell
is shaded gold, entering a number into the
cell would be an incorrect response.
Format Your Cells!
Remember to follow the cell
formatting instructions throughout the
assignment and as indicted on the
Excel Grading Sheet.
Correct formatting counts
substantially toward your score on the
assignment.
For a review how to format cells,
please see the resources for Major
Assignment 1.
Savings – Rate Lookups
On the Savings and Loan Analysis sheet, start by
looking up the interest rate for each of the given
year and month combinations. (These are
generated based on your name and so may be
different from what is shown here.)
Use the link provided, or copy the given URL into
your browser, to access the table of rates to use.
You will need to scroll through the page to find
one or more of your rates.
Rates are given as numbers representing
percentages; for example, a value of 3.80 in the
table means 3.80%, and you will need to enter
this as either 0.0380 or as 3.8%.
Format your rates as Percentage with 2 decimal
places. (The Excel instructions indicate
Percentage with 1 decimal place, so that would
also be correct.)
Savings – Monthly Utility
Costs and Savings
After you have entered your APRs, calculate your
monthly utility costs and savings in section 4.
1. Enter monthly costs for your electric, gas, water,
and other utilities. These can come from your Major
Assignment 1 budget. If a particular utility cost does
not apply, enter 0 instead of leaving the cell blank.
2. Use an Excel formula to add up the utility costs;
remember to use cell references in your formula.
3. Calculate the Monthly Savings:
Monthly Savings = Total Cost * Monthly Percent Savings
4. Format your entries as Currency with 2 decimal
places.
Savings – 5-, 10-, and 15-Year
Savings
Calculate the 5-year, 10-year, and 15-year savings:
1. Use an Excel formula to bring forward the monthly
savings amount that you just calculated.
2. Enter the number of contributions per year and
number of years for each calculation.
3. Calculate the Total amount saved, using the future
value formula provided in text box 1. (This may also look
familiar from Topic 1 DQ 1.)
4. Calculate your total contributions and accrued
interest:
TC = (Contribution amount) * (total # of contributions)
Accrued Interest = (Total saved) – (Total contributions)
5. Format your entries as indicated in the instructions.
Loans – 5-, 10-, and 15-Year
Loan Payments
Next, calculate the monthly payment amounts for 5year, 10-year, and 15-year loans:
1. Enter the number of contributions per year and
number of years for each calculation.
3. Calculate the monthly payment amount, using the
loan payment formula provided in text box 1. (This may
also look familiar from Topic 1 DQ 1.)
4. Calculate your total amount paid, and total interest:
Total paid = (Payment amount) * (total # of payments)
Total interest = (Total Paid) – (Loan principal)
5. Format your entries as indicated in the instructions.
Savings and Loan Comparisons
Finally, compare your total savings against your loan
costs for the 5-year, 10-year, and 15-year cases:
1. Use Excel formulas to bring your Total Amount Saved
forward from the Savings section for each time period.
2. Use Excel formulas to bring your Total Amount Paid
forward from the Loan section for each time period.
3. Compare the savings total against the loan total for
each time period. If your total savings is greater than
your total cost, you have broken even! Enter either
“Yes” or “No in each field here. (Depending on your
loan amounts and interest rates, it is possible that you
may either always or never break even.)
4. Format your entries as indicated in the instructions.
Budget Cost Projection –
Inflation Rate
On the Budget Cost Projection, start by calculating the
inflation rate:
1. Use the direct link to the Bureau of Labor Statistics
page or paste the URL into a browser window. Follow
the instructions to get to the CPI table.
2. Now, find the month and year given in the grayshaded cells. Transfer this CPI value and the one from a
year later into your CPI Value column. Also complete
the corresponding Month and Year entries.
3. Calculate your yearly inflation rate:
IR = (later CPI – earlier CPI) / (earlier CPI)
(Note that you may end up with a negative inflation
rate, as in this example.)
4. Format your cells as indicated in the instructions.
Monthly Budget Projection
Use the formula in text box 9 to project your monthly
budget from Major Assignment 1 forward 1, 5, and 10
year.
1. CORRECTION: In cell C33, enter the value of your
monthly budget from your Budget Total entry on the
Monthly Budget sheet of your Major Assignment 1. (That
is, this will be a value rather than a formula.)
2. Enter the number of years in the “Value of t” column.
3. Use the formula in text box 9 to calculate the 1-year,
5-year, and 10-year projected budget, based on the
current budget and your calculated yearly inflation
rate.
4. Calculate the Percent Increase in each row based on
the projected budget PB and initial budget IB:
% increase = (PB – IB) / IB
5. Format your entries as indicated in the instructions.
Conversions – Monthly Savings
On the Conversions sheet, use an Excel formula to
bring your Monthly Savings amount forward from
the Savings and Loan Analysis sheet.
To reference a cell from another sheet in Excel, you
can either type the = sign and then select the cell
from that sheet, or use a formula like
=‘Sheet Name’!CellReference
For example, to reference cell A2 on the Savings
and Loan Analysis sheet, you would use the formula
=’Savings and Loan Analysis’!A2
Once you have entered an appropriate formula
here, make sure the formatting is Currency with 2
decimals.
Conversions – Select Your
Countries
Select 4 distinct countries from the list
provided below the currency conversion
table, using the rules given in text box 11:
All 4 countries must come from the table
titled “Choose your countries from this
list”
All 4 countries must be different.
If you’ve run out of countries starting with
a particular letter, choose a country
starting with the next available letter.
Conversions – Currency Name,
Code, and Conversion Rate
For each country, retrieve the currency name,
code, and current exchange rate using the XE
website link.
Make sure you are converting from $1 (1 USD) to
the equivalent number of foreign currency units.
In the example at the right, one US dollar (USD)
can be exchanged for 29.261 Taiwanese New
Dollars (TWD)
Enter the full name and three-letter currency
code for the currency.
Enter the date that you looked up the
conversion rates. Note that currency exchange
rates change daily, so your conversion rates will
differ from those in this example, even if your
countries are the same.
Conversions – Your Savings
Converted to Another
Currency
For each country, convert your monthly
savings into an equivalent value in foreign
currency:
Amount in FC = (Amount in $) * (Conversion Rate)
Format each amount as Currency with 2
decimals, using the appropriate currency
code for each country as a prefix. Here,
you can use the Format Cells dialog and
select the appropriate currency code from
the Symbol dropdown list.
Conversions – Units of Another
Currency Converted to Dollars
For each country, convert the specified
number of units of the foreign currency into
the equivalent amount in dollars:
Amount in $ = (Amount in FC) / (Conversion Rate)
Although this will be an Excel formula, your
Amount in FC entry will be a hardcoded
input based on the number of units
specified in the first box in this row.
Format these amounts as Currency with 2
decimal places and using the $ symbol as a
prefix.
Great Job! This
concludes your Major
Assignment 2!
Major Assignment 1 Grading Sheet
Competency
Name
Requirements for full credit
You have entered your full name in the field provided. (Note that entering
your name on this sheet is required in order to complete your other sheets.)
You have listed at least 10 budget items total with at least 1 item in each
category.
You have entered the number of times purchased and purchase amount for
each item, and at least 3 items are purchased more than once.
Budget
Your Total Cost for each item is a formula multiplying the number of times
purchased by the purchase amount, using appropriate cell references.
Your Subtotal formulas are correct for each of your 5 sections.
Monthly
Budget
You have explicitly formatted your Cost Per Purchase, Total Cost, and Subtotal
cells to display as Currency with the $ sign and 2 decimal places of precision.
You have transferred your Subtotals from the Budget to the Summary and
Analysis section, using formulas with cell references.
Your Budget Total is correctly calculated from your Subtotals as a formula
Summary and using cell references.
Analysis
Your Percentages are formulas that correctly calculate your Budget Total
from your Subtotals, using cell references.
All cells are formatted as Currency showing the $ symbol and with 2 decimal
places of precision.
Charts
Your bar chart correctly shows the Subtotals as bars, has the Budget Category
entries as labels, and has an appropriate title and axis labels (3 points for
including the chart, 1 point for each additional element).
Your pie chart correctly shows the Percentages as pie slices and has an
appropriate title (3 points for including the chart, 1 point each for showing
the percentages and having a correct title).
Income
Analysis
You have correctly calculated the slope and y-intercept for the data provided,
using appropriate Excel functions.
Best-Fit Line Your formulas for Predicted Incomes are correct, using cell references for the
and Predicted slope, y-intercept, and years of education.
Incomes
Your slope, y-intercept, and Predicted incomes are formatted as indicated in
the instructions.
Chart
You have included an XY-Scatterplot of the BLS data, adding an appropriate
title and axis labels.
Analysis
Chart
Conversions
You have added a trendline to your scatterplot, extending it to 8 years on the
left and 24 years on the right.
You have identified the correct units for your final quantity, using the units
abbreviations (including capitalization) provided in the conversion factors
table.
You have identified the conversion ratios to use, using correct units
Conversions abbreviations from the table (including capitalization) and adding an N/A
entry if fewer than 3 conversions are needed.
Your formulas for the ratios are correct, using appropriate cell references.
Your final quantity is calculated correctly and uses cell references.
Fahrenheit /
Your Fahrenheit to Celsius conversion formulas are correct and use cell
Celsius
references. The calculations are direct and do not use built-in Excel functions.
Conversions
nt 1 Grading Sheet
(optional for
student use) Did
you meet the
requirements?
Subtotals
Points
possible
Your
points
1
1
Great job!
10
10
Great job!
10
10
Great job!
10
10
Great job!
10
10
Great job!
25
25
Great job!
5
5
Great job!
2
2
Great job!
10
10
Great job!
11
11
Great job!
7
7
Great job!
5
5
Great job!
106
106
6
6
17
17
19
10.5
6
6
Scoring comments
Great job!
Great job!
Predicted income should be rounded to 0
decimal places of precision.
Great job!
Subtotals
2
1
50
40.5
4
4
Trendline spans beyond years 8 to 24.
Great job!
10
4
10
0
8
1
4
4
Good for A) and B). See note on sheet for
C) and D).
No Execl formulas or cell refernces used.
No Excel formulas or cell references.
Great job!
Subtotals
36
13
Totals
192
159.5
Percentage
100.00%
83.07%
Scaled out of
100
100.00
83.07
1 Enter your full name here. If your full name is less than
Benny Shamoon
5 letters long, add additional letters ‘X’ at the end until you
reach length 5
2 Below, you will develop a simplified monthly budget, including entries for 5 separate
categories as given. You must enter at least 10 budget items total across all categories, with
up to 5 entries per category. Each category must include at least one budget item. For at
least 3 budget items, the number of times purchased per month must be greater than 1.
Format all costs as Currency with 2 decimal places.
3
Monthly Budget
Housing and Utilities
Budget Item
Number of times
purchased each
month
Cost per
purchase
Total cost
Mortgage payment
1
$1,600.00
$1,600.00
Electric
Gas
Cable
Internet
1
1
1
1
$300.00
$25.00
$40.00
$50.00
Subtotal:
$300.00
$25.00
$40.00
$50.00
$2,015.00
Food and Entertainment
Budget Item
Number of times
purchased each
month
Food
Dinner Out
Movies
Cost per
purchase
2
2
1
Total cost
$100.00
$30.00
$25.00
$200.00
$60.00
$25.00
Subtotal:
$285.00
Insurance, Health, and Medical
Budget Item
Health Insurance
Medicine
Number of times
purchased each
month
Cost per
purchase
1
1
Total cost
$350.00
$50.00
$350.00
$50.00
Subtotal:
$400.00
this table, and then c
category. Format the
percentages as Perce
Savings and Charitable Giving
Budget Item
Number of times
purchased each
month
Sponsorship
Church
Cost per
purchase
1
1
Insurance, Health, and Me
Total cost
$250.00
$100.00
$250.00
$100.00
Subtotal:
$350.00
Miscellaneous
Budget Item
Clothing
LA Fitness
Number of times
purchased each
month
Cost per
purchase
1
1
Total cost
$60.00
$56.00
$60.00
$56.00
Subtotal:
$116.00
Assignment Advisory: You must use the latest desktop version of Excel for Microsoft 365
for this assigment. (This is provided free by GCU; contact the Help Desk for more information
and help installing the software.) Using an earlier version of Excel or a different spreadsheet
program may result in missing or corrupted template elements. Copying cells from or into this
template may likewise result in corrupted data.
Legend
If a cell is shaded
Blue
Green
Gold
Any other color
You should
Enter a text response
Enter a number
Enter an Excel formula
Make no changes
3 Here, use Excel formulas to transfer the subtotals and total from your budget into
this table, and then calculate the percentage of the budget total represented by each
category. Format the costs as Currency with two decimal places of precision and the
percentages as Percentage with one decimal place of precision.
Budget Summary and Analysis
Budget Category
Subtotal
Housing and Utilities
Food and Entertainment
Insurance, Health, and Medical
Savings and Charitable Giving
Miscellaneous
$2,015.00
$285.00
$400.00
$350.00
$116.00
Budget Total
$3,166.00
Percentage of Total
63.6%
9.0%
12.6%
11.1%
3.7%
4 Below you will insert two charts for this data. First, insert a bar chart that shows
each Subtotal amount as a bar and has the Budget Categories as bar labels. Then,
insert a pie chart that shows the percentage of the Budget Total represented by each
Budget Category based on the Percentage of Total column.
Budget
Miscellaneous
Savings and Charitable Giving
Savings and Charitable Giving
Insurance, Health, and Medical
Food and Entertainment
Housing and Utilities
$0.00
$500.00
$1,000.00 $1,500.00 $2,000.00 $2,500.00
Chart Title
11%4%
12%
9%
64%
Housing and Utilities
Food and Entertainment
Insurance, Health, and Medical
Savings and Charitable Giving
Miscellaneous
5 On this sheet, you will investigate the relationship between years of education and average income
First, consider the following chart of education versus average income. Below it, use Excel functions to find the slope and y
intercept of the best-fit line for the given coordinates. Then, to the right, use the slope and y-intercept to calculate the
average weekly income for all years of education from 8 through 24. Finally, create a chart showing the BLS data as a
scatterplot, and add an auto trendline to this chart showing years of education versus predicted average income
superimposed on the BLS data and forecasting backward to 8 and forward to 24 years.
Here, you should format your slope and y-intercept as numbers with 0 decimal places and your average weekly incomes as
Currency with the $ symbol and 0 decimal places.
In case you’d like to explore the data, numbers here are derived from Bureau of Labor Statistics figures at
https://www.bls.gov/emp/tables/unemployment-earnings-education.htm. However, you don’t need to take any steps
related to this reference for the assignment.
Melissa Tarius
Benny Shamoon
BLS Data
Years of Education
(X)
Average Weekly Income
(Y)
10
12
13
14
16
18
19
20
$606.00
$742.00
$822.00
$896.00
$1,254.00
$1,504.00
$1,844.00
$1,906.00
Best-Fit Line Parameters
Slope (m)
Y-Intercept (0, b)
139
-930
Predicted Incomes Based on Best Fit
Average Weekly
Years of Education
Income
(X)
(Y = m*X + b)
8
$185.60
9
$325.07
10
$464.54
11
$604.01
12
$743.47
13
$882.94
14
$1,022.41
15
$1,161.88
16
$1,301.35
17
$1,440.82
18
$1,580.29
19
$1,719.76
20
$1,859.23
21
$1,998.70
22
$2,138.17
23
$2,277.64
24
$2,417.11
average income
Excel functions to find the slope and yintercept to calculate the
chart showing the BLS data as a
predicted average income
Legend
If a cell is shaded
Blue
and your average weekly incomes as
Green
Gold
Statistics figures at
you don’t need to take any steps
Any other color
You should
Enter a text
response
Enter a number
Enter an Excel
formula
Make no changes
6 Add your chart here: an XY-scatterplot of the BLS Data in columns A and B (NOT the data in
columns C and D) plus an auto trendline forecasting backward to 8 and forward to 24 years
Average Weekly Income by Years of Education
$6,000.00
WEEKLY INCOME
$5,000.00
$4,000.00
$3,000.00
$2,000.00
$1,000.00
$0.00
0
5
10
15
20
25
-$1,000.00
YEARS OF EDUCATION
30
35
40
45
50
7 On this sheet, you will consider several conversions related to calculations you might see in a professional context. For e
and apply appropriate ratios to yield the given result. Remember that ratios can use either unit over the other, and that yo
units cancel in the numerator and denominator for intermediate steps.
First, examine this conversion factor table; you will use conversion factors from this table in your formulas in part 8. Not
Second Units over the First Units, then your multiplier will be the conversion factor itself; on the other hand, if you use a
Second units, then your multiplier will be 1 divided by the conversion factor. For example, when multiplying by lb/kg, you w
multiplying by kg/lb, you would multiply by 1/D12.
Quantity of
First Units
=
1
kilogram (kg)
=
Conversion
Factor
2.20462
1
fluid ounce (floz)
=
29.5735
milliliter (mL)
1
ounce (oz)
=
28.3495
gram (g)
1
kilogram (kg)
=
1000
gram (g)
1
gram (g)
=
1000
milligram (mg)
1
1
1
1
1
1
milligram (mg)
liter (L)
liter (L)
teaspoon (tsp)
meter (m)
day (d)
=
=
=
=
=
=
1000
33.8140
0.2642
4.9289
3.2808
24
microgram (mcg)
fluid ounces (floz)
gallons (gal)
milliliter (mL)
feet (ft)
hours (h)
Second Units
pounds (lb)
8 Now, use entries from the conversion table to perform the following conversions. Note that you may
more direct conversion is possible. For each part, the number of ratios required is shown in the table. Note that your ratios
one of the conversion factors above (like =F10) or the reciprocal (like =1/F9), as illustrated in the example. No special fo
containing your formulas. In the blue cells, enter the ratio of units that you multiplied by for each conversion. You should
above, including capitalization as given.
Example: convert fluid ounces per
kilogram to milliliters per pound
Initial quantity and units to
convert from
x
First ratio and units
x
Second ratio and units
x
Third ratio and units
=
10
A) Convert milligrams per liter to
micrograms per fluid ounce
floz/kg
50
mL/floz
1000
x
29.5735
x
x
0.453592909
mcg/mg
x
kg/lb
=
mg/L
0.02956
L/fl oz
=
Final quantity and units to
convert to
134.1432991
mL/lb
1,478
mcg/floz
9 Some conversions use ratios plus another additive term (summand). Here, you’ll convert from Fahrenheit to Celsius and
following symbolic formulas:
Celsius to Fahrenheit: F = (9/5)*C + 32
Fahrenheit to Celsius: C = (5/9)*(F – 32)
Use formulas to populate the two empty cells below. No special formatting is required for your cells here.
Fahrenheit
Celsius
1
-17.22222222
42.8
6
e in a professional context. For each conversion, you’ll identify
r unit over the other, and that you should order ratios so that
n your formulas in part 8. Note that if you use a ratio of the
n the other hand, if you use a ratio of the First Units over the
when multiplying by lb/kg, you would multiply by D12; when
Legend
If a cell is shaded
You should
Blue
Enter a text response
Green
Enter a number
Gold
Enter an Excel formula
Any other color
Make no changes
that you may use entries only from the table above, even if a
n the table. Note that your ratios for the formulas may be either
n the example. No special formatting is required for the cells
r each conversion. You should use the abbreviations provided
B) Convert grams per hour to
kilograms per day
20
C) Convert square feet per gallon to
square meters per liter (hint: one
conversion factor is applied twice)
g/h
5000
kg/g
0.2642
x
1000
ft^2/gal
30
Gal/L
29.5735
x
x
24
D) Convert milliliters per kilogram
per hour to teaspoons per pound
per day
x
x
h/d
3.7584
x
L/Gal
1000
x
0.0929
=
x
m^2/ft^2
=
4.9289
=
480000
kg/d
464.54.76949
rt from Fahrenheit to Celsius and from Celsius to Fahrenheit, using the
9/5)*C + 32
32)
your cells here.
M2/L
145764
For C), you want to think of it as three
steps.
1. ft -> m
2. ft -> m (again to deal with the
squared)
3. gal->L
To cancel the ft, you would need a ratio
in the form m/ft.
To cancel the gal, you would need your
ratio to be in the form gal/L
See my note in red to help with lining up
the units and how to read the table.
Then you can do a string of
multiplication.
nvert milliliters per kilogram
our to teaspoons per pound
per day
mL/(kg*h)
x
ml/kg
x
kg/h
x
tsp/lbs
=
tsp/lbs/day
hink of it as three
eal with the
would need a ratio
u would need your
o help with lining up
read the table.
For D), think of this one in three steps as well.
1. mL -> tsp as tsp/mL
2. kg -> lb as kg/lb (kg is in the denominator so we
will multiply with it in the numerator to divide out
the units)
3. h -> d as h/d (same reasoning as kg/lb; h is in the
denominator so we need to multiply with h in the
numerator