Great Deal! Get Instant $25 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Grading Sheet Major Assignment 2 Grading Sheet CompetencyRequirements for full credit(optional for student use) Did you meet the requirements?Points possibleYour pointsScoring comments Savings...

1 answer below »

Grading Sheet
    Major Assignment 2 Grading Sheet
        Competency    Requirements for full credit    (optional for student use) Did you meet the requirements?    Points possible    Your points    Scoring comments
    Savings and Loan Analysis    Name    You have entered your full name in the field provided.        1
        Interests Rates    The interest rates you have entered come from the mortgage rates table and match those for the months and years provided.        3
            You have explicitly formatted the cells to display as Percentage with 2 decimal places of precision.        3
        Monthly Costs and Savings    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.        4
            Your Total Cost and Monthly Savings formulas are co
ect and use appropriate cell references.        8
            All cost cells are formatted as Cu
ency showing the $ symbol and with 2 decimal places of precision.        6
        Savings Table    You have
ought forward your monthly savings amount, using Excel formulas.        3
            Your number of contributions per year and number of years entries are co
ect.        6
            Your formulas for total amount saved, total contributions, and total accrued interest are co
ect and use cell references.        36
            All cells are explicitly formatted with the format given in the last column of the table.        18
        Loan Table    You have entered the co
ect number of contributions per year and number of years.        6
            Your formulas for payment amount, total amount paid, and total amount of interest paid are co
ect and use cell references as inputs.        36
            All cells are explicitly formatted with the format given in the last column of the table.        15
        Comparison    You have co
ectly
ought forward your savings and loan amounts, using cell references.        6
            Your savings and loan cells are explicitly formatted with the format given in the last column of the table.        6
            You have answered the comparison questions co
ectly, answering either "yes" or "no" for each one.        3
                Subtotals    160    0
    Budget Cost Projection    Inflation Rate Calculation    Your reference CPI is co
ect for the month and year given.        1
            Your next-year CPI, month, and year are co
ect.        3
            Your inflation rate calculation is co
ect.        6
            Your CPI values and inflation rate are explicitly formatted as indicated in the instructions.        3
        Budget Projections    You have co
ectly entered your Budget Total from cell G21 of the Monthly Budget sheet from your Major Assignment 1.        2
            Your "value of t" entries are co
ect.        3
            Your 1-year, 5-year, and 10-year projections are co
ect Excel formulas using cell references.        12
            Your percent increase calculations are co
ect Excel formulas using cell references.        12
            Your Cu
ent Budget, Projected Budget and Percent Increase cells are formatted as indicated in the instructions.        7
                Subtotals    49    0
    Conversions    Monthly Savings    You have
ought forward your monthly savings amount from the Savings and Loan Analysis sheet, using an Excel formula with a sheet and cell reference.        2
        Cu
ency Conversions    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.        4
            You have chosen appropriate countries from the list provided below the table, using the procedure described in the instructions.        4
            You have entered the date(s) on which you looked up the exchange rates for your cu
encies, and all dates are within 2 weeks of the due date of your assignment.        4
            You have entered both the full name of your country's cu
ency and the co
ect cu
ency code as indicated on the website.        8
            You have provided each exchange rate to at least 5 significant digits, and the exchange rate matches the rate for the date you looked it up.        4
            Your savings amount in the foreign cu
ency is a co
ect Excel formula, using cell references.        16
            Your calculation of the value of foreign cu
ency units into dollars are co
ect Excel formulas, using cell references. (Note that the amount to convert is autogenerated and may differ from the amount shown in assignment resources.)        16
            The cells containing your dates, savings amounts, and value of foreign cu
ency converted to dollars are co
ectly formatted as specified in the last column of the table.        12
                Subtotals    70    0
                Totals    279    0
                Percentage    100.00%    0.00%
                Scaled out of 100    100.00    0.00
Savings and Loan Analysis
    1 In Major Assignment 1, you created a monthly budget, which included a recu
ing 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.
Below, you'll start by entering and adding up the costs of your electric, gas, water, and other energy utilities. Then, given a percent savings due to your energy-saving improvements, you'll calculate how much you'll save over 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 following formula for your calculations: given an amount P contributed at the end of each of n periods per year for t years and earning interest at an annual percentage rate of r, the total amount A accrued after t years is given by (in Excel format):
XXXXXXXXXXA = P*((1+
n)^(n*t)-1)/(
n)
Next, you'll develop a cost to install energy-efficient improvements (installing energy-efficient doors and windows, adding insulation, upgrading to more efficient appliances or lights, and so on) and then calculate a monthly payment if you were to finance the installation cost by a loan of 5, 10, or 15 years. Here, you will use this formula: given a loan principal amount P and an annual interest rate of r, the payment amount PMT required to pay 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 format):
XXXXXXXXXXPMT = P*(
n)/(1-(1+
n)^(-n*t))
Important Note: For your savings and loan payment calculations, as well as for calculating the associated interest, you MUST use direct calculation formulas, and you MAY NOT use built-in Excel functions to calculate these values.
For all the above calculations, you will look up rates in the following historical table of 30-year fixed mortgage rates, based on the years and months specified in step 6 below.
XXXXXXXXXXhttp:
www.freddiemac.com/pmms/pmms30.html                                    
                                        
    2 Enter your full name in the blue-shaded box here (If there are fewer than 9 letters, add additional a
itrary letters)        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 co
upted template elements. Copying cells from or into this template may likewise result in co
upted data.
    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 Yea
    Your full name entry must be longer    Your full name entry must be longer    Your full name entry must be longe
        
APR Month
    Your full name entry must be longer    Your full name entry must be longer    Your full name entry must be longe
        
Interest Rate
    
                
    4 Enter, or estimate, your monthly utility costs, then calculate your total monthly cost and monthly savings. Format all cells as Cu
ency showing the $ symbol and with 2 decimals of precision.    Electric            Legend
        Gas            If a cell is shaded    You should
        Water            Blue    Enter a text response
        Other            Green    Enter a numbe
        Total Cost            Gold    Enter an Excel formula
        
Monthly Percent Savings
    Complete the first interest rate entry in section 3 above        Any other color    Make no changes
        Monthly Savings (total cost times percent savings)
    5 Complete this table for your 5-year, 10-year, and 15-year savings    Calculation #1
(5-year savings)    Calculation #2
(10-year savings)    Calculation #3
(15-year savings)    Format the entries in each row as…
    Contribution amount (P)
(Bring forward your Monthly Savings amount, using a formula, for each entry)                …Cu
ency with 2 decimal places
    
APR from the table (r)
    Complete the second interest rate entry in section 3 above    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)                …a Number with 0 decimal places
    Number of years (t)                …a Number with 0 decimal places
    Total amount saved (A):                …Cu
ency with 2 decimal places
    Total contributions:                …Cu
ency with 2 decimal places
    Total accrued interest:                …Cu
ency with 2 decimal places
    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)    Calculation #3
(15-year loan)    Format the entries in each row as…
    
Loan principal (P)
    Your full name entry must be longer    Your full name entry must be longer    Your full name entry must be longe
    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
Answered Same DayFeb 05, 2022

Solution

Rochak answered on Feb 06 2022
73 Votes
Grading Sheet
    Major Assignment 2 Grading Sheet
        Competency    Requirements for full credit    (optional for student use) Did you meet the requirements?    Points possible    Your points    Scoring comments
    Savings and Loan Analysis    Name    You have entered your full name in the field provided.    Yes    1
        Interests Rates    The interest rates you have entered come from the mortgage rates table and match those for the months and years provided.    Yes    3
            You have explicitly formatted the cells to display as Percentage with 2 decimal places of precision.    Yes    3
        Monthly Costs and Savings    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.    Yes    4
            Your Total Cost and Monthly Savings formulas are co
ect and use appropriate cell references.    Yes    8
            All cost cells are formatted as Cu
ency showing the $ symbol and with 2 decimal places of precision.    Yes    6
        Savings Table    You have
ought forward your monthly savings amount, using Excel formulas.    Yes    3
            Your number of contributions per year and number of years entries are co
ect.    Yes    6
            Your formulas for total amount saved, total contributions, and total accrued interest are co
ect and use cell references.    Yes    36
            All cells are explicitly formatted with the format given in the last column of the table.    Yes    18
        Loan Table    You have entered the co
ect number of contributions per year and number of years.    Yes    6
            Your formulas for payment amount, total amount paid, and total amount of interest paid are co
ect and use cell references as inputs.    Yes    36
            All cells are explicitly formatted with the format given in the last column of the table.    Yes    15
        Comparison    You have co
ectly
ought forward your savings and loan amounts, using cell references.    Yes    6
            Your savings and loan cells are explicitly formatted with the format given in the last column of the table.    Yes    6
            You have answered the comparison questions co
ectly, answering either "yes" or "no" for each one.    Yes    3
                Subtotals    160    0
    Budget Cost Projection    Inflation Rate Calculation    Your reference CPI is co
ect for the month and year given.    Yes    1
            Your next-year CPI, month, and year are co
ect.    Yes    3
            Your inflation rate calculation is co
ect.    Yes    6
            Your CPI values and inflation rate are explicitly formatted as indicated in the instructions.    Yes    3
        Budget Projections    You have co
ectly entered your Budget Total from cell G21 of the Monthly Budget sheet from your Major Assignment 1.    Yes    2
            Your "value of t" entries are co
ect.    Yes    3
            Your 1-year, 5-year, and 10-year projections are co
ect Excel formulas using cell references.    Yes    12
            Your percent increase calculations are co
ect Excel formulas using cell references.    Yes    12
            Your Cu
ent Budget, Projected Budget and Percent Increase cells are formatted as indicated in the instructions.    Yes    7
                Subtotals    49    0
    Conversions    Monthly Savings    You have
ought forward your monthly savings amount from the Savings and Loan Analysis sheet, using an Excel formula with a sheet and cell reference.    Yes    2
        Cu
ency Conversions    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.    Yes    4
            You have chosen appropriate countries from the list provided below the table, using the procedure described in the instructions.    Yes    4
            You have entered the date(s) on which you looked up the exchange rates for your cu
encies, and all dates are within 2 weeks of the due date of your assignment.    Yes    4
            You have entered both the full name of your country's cu
ency and the co
ect cu
ency code as indicated on the website.    Yes    8
            You have provided each exchange rate to at least 5 significant digits, and the exchange rate matches the rate for the date you looked it up.    Yes    4
            Your savings amount in the foreign cu
ency is a co
ect Excel formula, using cell references.    Yes    16
            Your calculation of the value of foreign cu
ency units into dollars are co
ect Excel formulas, using cell references. (Note that the amount to convert is autogenerated and may differ from the amount shown in assignment resources.)    Yes    16
            The cells containing your dates, savings amounts, and value of foreign cu
ency converted to dollars are co
ectly formatted as specified in the last column of the table.    Yes    12
                Subtotals    70    0
                Totals    279    0
                Percentage    100.00%    0.00%
                Scaled out of 100    100.00    0.00
Savings and Loan Analysis
    1 In Major Assignment 1, you created a monthly budget, which included a recu
ing 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.
Below, you'll start by entering and adding up the costs of your electric, gas, water, and other energy utilities. Then, given a percent savings due to your energy-saving improvements, you'll calculate how much you'll save over 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 following formula for your calculations: given an amount P contributed at the end of each of n periods per year for t years and earning interest at an annual percentage rate of r, the total amount A accrued...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here