MAT 144 Grand Canyon University Algebra Worksheet

Major Assignment 2 Excel Grading SheetRubric Category
(% weighting for
assignment)
Project Budget
(15%)
Budget Summary
and Analysis
(11%)
CPI Values, Slope,
Y-Intercept, and
Cost Projection
(16%)
Funding – Inputs
(5%)
Sponsorship,
Fundraising, and
Loan Payment
Calculations
(12%)
Major Assignment 2 Excel Grading Sheet
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 the assignment.)
You have listed at least 8 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.
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 4 sections.
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. (1/2 point each)
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 using cell
references.
Your Percentage of Total formulas are correctly calculated from the Subtotals and
Budget Total in the Summary section, using cell references.
Subtotal and Budget Total cells are formatted as Currency showing the $ symbol and
with 2 decimal places of precision, and percentages are formatted as Percentage with 1
decimal place.
Your month and year entries are correct following the first (generated) values.
Your CPI values are correct for the months and years given.
Your slope and y-intercept formulas are correct and use the appropriate Excel functions.
The year number to use for your CPI projection is correct, and you have brought your
Budget Total forward using an Excel formula with appropriate cell reference.
Your formulas to calculate the projected CPI value, 5-year inflation rate, and 5-year
budget projection are correct.
Your formatting is correct for all cells: your CPI values are formatted as number with 3
decimals of precision; your 5-year inflation rate is formatted as a percentage with 2
decimal places; and your 5-year budget and budget projection are formatted as currency
with the $ symbol and 2 decimal places.
Your interest rates correctly match the corresponding entries from the table for the years
and months given.
Your 5-year projected budget total is brought forward from the Project Budget and
Projection sheet, using an Excel formula.
Your interest rates are formatted as percentages with 2 decimals and your budget total is
formatted as currency using the $ symbol and 2 decimal places.
For your Sponsorship calculation, your number of compoundings and time inputs are
correct, and your principal amount and 5-year sponsorship amount are correct Excel
formulas using appropriate cell references.
For your Fundraising calculation, your number of compoundings and time inputs are
correct, and your 5-year fundraising total calculation is a correct Excel formula using
appropriate cell references.
For your Loan calculation, your number of payments and time inputs are correct, and
your additional amount and monthly payment calculations are correct Excel formulas
using appropriate cell references.
All cells with dollar amounts are explicitly formatted as Currency using the $ symbol
and with 2 decimal places. Cells with n or t entries are formatted as Number with 0
decimal places.
l Grading Sheet
(optional for
student use) Did
you meet the
requirements?
Possible
Points
Your Points
% Score for Rubric
Category
Rubric Score
1
8
8
32
16
10
Subtotals
75
0
0.00%
1
0
0.00%
1
0
0.00%
1
16
4
16
9
Subtotals
45
10
6
16
8
24
12
Subtotals
76
6
4
4
Subtotals
14
0
0.00%
1
0
0.00%
1
28
20
28
11
Subtotals
87
1 Enter your full name to the right. If your full name is
less than 10 letters long, add additional letters ‘X’ at the
end until you reach length 10
Name
2a Below, you will develop a budget for your project, including entries for personnel costs (for
yourself, other paid individuals, legal or consulting services, etc.); administrative costs (rents,
fees, utilities, insurance, IT costs, office supplies, travel, etc.); outreach costs (advertising,
recruitment, fundraising, etc.); and program costs (supplies for use in assistance, cash
distributions, payments for direct services to recipients, etc.). You must enter at least 8 budget
items in all. Each category must have at least one budget item. For at least 3 budget items, your
expense quantity must be greater than one. Format all costs as Currency with 2 decimal places.
To start, modify the cost of your time as appropriate.
Project Budget
Personnel Costs
Budget Item
Costs of your time (per week)
Quantity needed
Number
Cost Per Item
Value per item
Total Cost
Formula
Subtotal:
0
Administrative Costs
Budget Item
Quantity needed
Cost Per Item
Total Cost
Subtotal:
Outreach Costs
Budget Item
Quantity needed
Cost Per Item
Total Cost
Subtotal:
Program Costs
Budget Item
Quantity needed
Cost Per Item
Total Cost
Subtotal:
Assignment Advisory: You must use the latest desktop version of Excel for Mic
for this assigment. (This is provided free by GCU; contact the Help Desk for more inform
help installing the software.) Using an earlier version of Excel or a different spreadsheet
may result in missing or corrupted template elements. Copying cells from or into this tem
may likewise result in corrupted data.
2b 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 and the percentages as Percentage
with one decimal place.
Budget Summary and Analysis
Budget Category
Subtotal
Personnel Costs
Percentage of Total
0
Administrative Costs
Outreach Costs
Program Costs
Budget Total
Budget Cost Projection
3a Below, you will generate a 5-year projected cost for your budget, starting with your budget total. You
you look up. 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
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 ab
First, fill in the CPI value for the given month and year. Then, fill in the CPI values for the next 6 years, ad
For example, if you start in February 2011, your next CPI value will come from February 2012. Format you
of precision.
Year Number
1
CPI Value
Month
Your full name entry must
be longer
2
3
4
5
6
3b Now, from the year number and CPI values, find the slope and y-intercept of the best-
3b Now, from the year number and CPI values, find the slope and y-intercept of the bestSlope (m)
Y-Intercept (0, b)
3c Finally, project the CPI forward an additional 5 years using your slope and intercept; calculate the 5-ye
and the calculated value; and apply the inflation rate to your budget total to find a 5-year budget projection.
The Year Number to use for your further 5-year projection
The projected CPI value for that year
The 5-year inflation rate based on the last CPI value in the table
and the projected CPI value
Your Budget Total (brought forward from above)
Your 5-year Budget Total Projection
ersion of Excel for Microsoft 365
Desk for more information and
different spreadsheet program
ls from or into this template
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
h your budget total. You will base your projection on CPI values that
rveymost?cu
(CPI Values)
00SA0″
ke you to a CPI table for about the last 10 years.
ues for the next 6 years, advancing exactly one year for each value.
February 2012. Format your CPI values as Numbers with 3 decimals
Year
Your full name entry must
be longer
and y-intercept of the best-fit line for your CPI values.
and y-intercept of the best-fit line for your CPI values.
ntercept; calculate the 5-year inflation rate from your year 6 CPI value
5-year budget projection.
Your name (brought forward from the
Monthly Budget sheet):
Name
4 On this sheet, you will consider how to cover the projected cost of your Project Budget. In particular, you will cal
be invested at a given interest rate with annual compounding; the 5-year balance for a given monthly donation amoun
for a 5-year loan to cover the remainder of your 5-year projected cost, using a third interest rate.
Start by looking up rates to use as APRs in the following historical table of 30-year fixed mortgage rates:
http://www.freddiemac.com/pmms/pmms30.html
Enter these items as percentages or decimals; for example, a value of 4.03 in the table would be entered here as 4.03%
APR Year
Your full name entry must be
longer
APR Month
4
Interest Rate
5 Now, perform each of the calculations indicated. Refer to the Financial formulas section provided on the Instructi
Format all amounts as Currency with the $ symbol and 2 decimal places, and all n and t entries as Number with 0 dec
Bring your 5-year projected budget total
forward from the Project Budget and
Projection sheet
6 Sponsorship (invest a fixed amount for 5 years with annual
compounding)
Percentage of your Budget Total to invest
Complete the first interest rate
entry in section 4 above
Principal amount to invest (P), based on your
budget total and the percentage above.
Your APR (r)
Number of compoundings per year (n)
Number of years (t)
Your 5-year sponsorship total
Complete the interest rate entry
in section 4 above
7 Fundraising (invest monthly donations for 5 years with
monthly compounding)
Monthly contribution amount (PMT)
Your APR (r)
Number of compoundings per year (n)
Number of years (t)
Your 5-year fundraising total
(Hint: use the “future value of periodic
payments” formula in the assignments
instructions)
Complete the second interest
rate entry in section 4 above
Complete the second interest
rate entry in section 4 above
of your Project Budget. In particular, you will calculate the 5-year balance for a percentage of your projected cost assumed to
-year balance for a given monthly donation amount invested at a second interest rate; and a monthly loan payment required
st, using a third interest rate.
table of 30-year fixed mortgage rates:
(Mortgage Rates)
www.freddiemac.com/pmms/pmms30.html
of 4.03 in the table would be entered here as 4.03% or 0.0403. Also format each rate as a Percentage with 2 decimal places.
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
nancial formulas section provided on the Instructions word document as needed.
laces, and all n and t entries as Number with 0 decimals.
8 Loan payment (determine the monthly payment required for a 5year loan to cover the remainder of the budget costs)
The additional amount you need to cover your
5-year budget costs (P)
Your APR (r)
Number of payments per year (n)
Number of years (t)
The monthly loan payment (PMT)
Legend
If a cell is shaded
Blue
Green
Gold
Any other color
or a percentage of your projected cost assumed to
rest rate; and a monthly loan payment required
each rate as a Percentage with 2 decimal places.
ded.
e the monthly payment required for a 5e remainder of the budget costs)
Complete the third interest rate entry
in section 4 above
Legend
You should
Enter a text response
Enter a number
Enter an Excel formula
Make no changes
MAT-144 Major Assignment 2 Instructions
Please follow these instructions when completing Major Assignment 2. You will be completing a
Microsoft Word template and an Excel template for the assignment. Refer to the assignment
rubric for details about how the assignment will be scored.
Overview
You have decided that 5 years from now, you will spend 12 weeks implementing a project to
provide humanitarian assistance to people in your local community. At that time, you will take a
break from work or retirement and spend at least 30 hours per week for 12 weeks engaged in this
project. You will be in charge of your own project working directly with people in need, not
simply volunteering with an existing organization. In the 5 years between now and the start of
the project, you will devise a plan and prepare funding.
Project details in Word
For your project, you will identify a group of local people to assist with a need from the provided
list. You will develop a project that has the potential to improve their lives in both the short and
long run. Choose one project from the list below to use for Major Assignment 2. The list is
organized under different topics to help you find a project that interests you.
Education
– An afterschool tutoring program
– School supply distribution
– Day camp for kids
– Vocational training
Health Care
– Pop-up clinic for vaccines, general health care, etc.
– Health care awareness program – mental health, nutrition, maternity care, etc.
– Mental health support groups
– Help lines/call centers
Community Improvement
– Home rehabilitation
– Community safety program
Other Service Projects
– Food distribution
– Clothing distribution
– Companion program for senior citizens
– Homeless assistance
© 2021. Grand Canyon University. All Rights Reserved. Version – Fa21
You will contact experts or representatives from local churches, charities, non-governmental
and/or governmental organizations to get information about areas of need in your community
and/or additional information about what is necessary to run a project like this.
For your project, you will provide specifics of what you will achieve and how the success of
your project should be measured.
You will also identify the people, resources, and materials needed to provide your service. Your
project must include the assistance of other people, either whom you will recruit as volunteers,
whom you will pay for expert assistance, or both.
You will complete the Microsoft Word template using 1,500 to 2,000 words to explain specifics
of your project implementation. For full credit, you must address each point in each section.
The objective is to demonstrate that you have thought through in detail what it would mean to
provide a certain type of assistance.
Although you may cross-reference points between sections, the questions from each section must
be answered in that section to get credit for your response. The amount of credit you receive will
depend on how thoroughly you have considered your implementation: you should attempt to
write so that a reasonable reader would need to ask few or no follow-up questions to clarify your
overall objective, people you will help, assistance provided, project management, timeline,
assessment procedures, ethics and CWV analysis, and reflections. Write-ups that leave many
questions unanswered or with significant gaps will earn less credit. Be sure to support your
claims with evidence.
Here are the sections that you will be completing in the Microsoft Word template:
1. A title for your write-up, including your name, the date, the course, and your instructor’s
name. Your title should capture the essence of your project (e.g., “Tiny Homes for the
Homeless in Mendenhall, MS”)
2. Whom will you help?
3. What assistance will you provide?
4. How will you start up and manage your project?
5. What will you accomplish in the main part of your project?
6. How will you wrap up and evaluate your project?
7. Ethics and Christian Worldview analysis
8. Summary and Reflections
9. References: at least three scholarly sources. These can be journal articles or official
government websites.
Financial computations in Excel
Separately from your presentation to sponsors (the project details in Word), you will complete
calculations in Excel detailing your expected costs, sponsorship and fundraising contributions,
© 2021. Grand Canyon University. All Rights Reserved. Version – Fa21
and loan details. A template is provided for this part of the assignment, including additional
information to assist you.
As part of your financial calculations, you will develop the following:
1. A budget for your project, including personnel, administrative, outreach, and program
costs, plus a percentage breakdown of each type of cost and an estimated cost per person
that you expect to assist. Budget costs will be in current dollars.
2. A 5-year forward projection of your budget cost, using extrapolation of CPI values from
5 years that you will look up in a table.
3. A 5-year projection of a sponsorship amount; the sponsorship amount will be a given
percentage of your 5-year budget projection and will accrue interest at a given APR.
4. A 5-year fundraising total that you expect to raise through monthly donations; the
monthly donation amount and APR will be provided.
5. A 5-year loan payment calculation in which you calculate the monthly payment amount
required to pay off monthly, over a period of 5 years, the difference between the cost of
the project and the sum of your sponsorship and fundraising totals. (The loan would be
taken out at the time the project is implemented.)
Financial formulas
The following formulas, given symbolically and in Excel format, may be useful for completing
Major Assignment 2:
Compound interest formula: If an initial amount P grows at an annual rate r with n
compoundings per year, then the value A of that amount after t years is given by the formula
𝑟 𝑛𝑡
𝐴 = 𝑃 (1 + )
𝑛
A = P*(1+r/n)^(n*t)
Future value of periodic payments: If an amount P is contributed at the end of each of n
periods per year for t years and the balance earns interest at an annual percentage rate of r, the
total amount A accrued after t years is given by
𝑟 𝑛𝑡
((1 + ) − 1)
𝑛
𝐴=𝑃
𝑟
𝑛
A = P*((1+r/n)^(n*t) –1)/(r/n)
Loan payment formula: The amount PMT that must be paid n times per year for t years to pay
off a loan principal amount of P at an APR of r is given by
© 2021. Grand Canyon University. All Rights Reserved. Version – Fa21
𝑃𝑀𝑇 = 𝑃
𝑟
𝑛
𝑟 −𝑛𝑡
(1 − (1 + ) )
𝑛
PMT = P*(r/n)/(1 – (1 + r/n)^(-n*t))
Inflation rate formula: Given an old CPI value at one point and a new CPI value n years later,
the n-year inflation rate IR is given by
IR =
(new CPI − old CPI)
old CPI
IR = (new CPI – old CPI)/(old CPI)
© 2021. Grand Canyon University. All Rights Reserved. Version – Fa21