Description

In Major Assignment 1, you created a monthly budget, which included a recurring cost for utilities.Here, you’ll consider making some energy-saving home improvements and compare your potential savings against paying off the cost of those improvements.

1 attachmentsSlide 1 of 1attachment_1attachment_1

Unformatted Attachment Preview

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 tem

(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

te a monthly

quired 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

Purchase answer to see full

attachment

Tags:

competency

Monthly Budget

home improvements

Potential savings

Savings and Loan

User generated content is uploaded by users for the purposes of learning and should be used following Studypool’s honor code & terms of service.

## Reviews, comments, and love from our customers and community:

This page is having a slideshow that uses Javascript. Your browser either doesn't support Javascript or you have it turned off. To see this page as it is meant to appear please use a Javascript enabled browser.