Mathematics Help with math assignment that's due on September 3, 2023

Excel Grading Sheet

Major Assignment 3 Excel Grading Sheet
Rubric Category (% weighting for assignment)	Requirements for full credit	(optional for student use) Did you meet the requirements?	Possible Points	Your Points	% Score for Rubric Category	Rubric Score
Project Budget (15%)	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.)		2
	You have listed at least 8 budget items total with at least 1 item in each category.		8
	You have entered the number of times purchased and purchase amount for each item, and at least 3 items are purchased more than once.		8
	Your Total Cost for each item is a formula multiplying the number of times purchased by the purchase amount, using appropriate cell references.		16
	Your Subtotal formulas are correct for each of your 4 sections.		16
	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)		10
		Subtotals	60	0	0.00%	1
Budget Summary and Analysis (11%)	You have transferred your Subtotals from the Budget to the Summary and Analysis section, using formulas with cell references.		8
	Your Budget Total is correctly calculated from your Subtotals as a formula using cell references.		4
	Your Percentage of Total formulas are correctly calculated from the Subtotals and Budget Total in the Summary section, using cell references.		16
	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.		9
		Subtotals	37	0	0.00%	1
CPI Values, Slope, Y-Intercept, and Cost Projection (16%)	Your month and year entries are correct following the first (generated) values.		10
	Your CPI values are correct for the months and years given.		6
	Your slope and y-intercept formulas are correct and use the appropriate Excel functions.		8
	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.		4
	Your formulas to calculate the projected CPI value, 5-year inflation rate, and 5-year budget projection are correct.		12
	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.		12
		Subtotals	52	0	0.00%	1
Funding - Inputs (5%)	Your interest rates correctly match the corresponding entries from the table for the years and months given.		6
	Your 5-year projected budget total is brought forward from the Project Budget and Projection sheet, using an Excel formula.		2
	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.		4
		Subtotals	12	0	0.00%	1
Sponsorship, Fundraising, and Loan Payment Calculations (12%)	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.		12
	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.		8
	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.		12
	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.		11
		Subtotals	43	0	0.00%	1
						1 1 1 1 1

Project Budget and Projection

1 Enter your full name in the blue-shaded box here. If your full name is less than 10 letters long, add additional letters 'X' at the end until you reach length 10.					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
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.					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.			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
					Budget Summary and Analysis
					Budget Category	Subtotal	Percentage of Total
					Personnel Costs	$1,000.00
					Administrative Costs
					Outreach Costs
Project Budget					Program Costs
Personnel Costs					Budget Total
Budget Item	Quantity needed	Cost Per Item	Total Cost
Costs of your time (per week)	1	$1,000.00	$1,000.00		Budget Cost Projection
					3a Below, you will generate a 5-year projected cost for your budget, starting with your budget total. You will base your projection on CPI values that 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 about the last 10 years. First, fill in the CPI value for the given month and year. Then, fill in the CPI values for the next 6 years, advancing exactly one year for each value. For example, if you start in February 2011, your next CPI value will come from February 2012. Format your CPI values as Numbers with 3 decimals of precision.
Subtotal:			$1,000.00
Administrative Costs
Budget Item	Quantity needed	Cost Per Item	Total Cost
Subtotal:
Outreach Costs					CPI Value	Month	Year
Budget Item	Quantity needed	Cost Per Item	Total Cost			Your full name entry must be longer	Your full name entry must be longer
Subtotal:
Program Costs					3b Now, from the CPI values and Years in columns F and H, find the slope and y-intercept of the best-fit line for your CPI values.
Budget Item	Quantity needed	Cost Per Item	Total Cost
					Slope (m)
					Y-Intercept (0, b)
					3c Finally, project the CPI forward an additional 5 years using your slope and intercept; calculate the 5-year inflation rate from your last CPI value and the calculated value; and apply the inflation rate to your budget total to find a 5-year budget projection.
Subtotal:
					The Year to use for your further 5-year projection. (If you project forward another 5 years from your last year in the chart above, what year will you be at then?)
					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

CPI Values

https://data.bls.gov/cgi-bin/surveymost?cu

Funding

Your name (brought forward from the Monthly Budget sheet):	0
4 On this sheet, you will consider how to cover the projected cost of your Project Budget. In particular, you will calculate the 5-year balance for a percentage of your projected cost assumed to be invested at a given interest rate with annual compounding; the 5-year balance for a given monthly donation amount invested at a second interest rate; and a monthly loan payment required for a 5-year loan to cover the remainder of your 5-year projected cost, using a third interest rate. For all the above calculations, you will look up rates in the "Historical Mortgage Rates" table of 30-year fixed mortgage rates, based on the years and months specified in step 6 below. (This table is included as a tab in this template.)
Make sure to format these as percentage values. For example, if 4.03 appears in the table, then it should appear here as 4.03%.
APR Year	Your full name entry must be longer	Your full name entry must be longer	Your full name entry must be longer
APR Month	Your full name entry must be longer	Your full name entry must be longer	Your full name entry must be longer
Interest Rate
5 Now, perform each of the calculations indicated. Please refer to the financial formulas section of the instructions for Major Assignment 3, where you will find the equations for the Compound Interest Formula, Future Value of Periodic Payments Formula, and Loan Payment Formula to use for the final calculation in the Sponsorship, Fundraising, and Loan Payment sections below, respectively. Important Note: for these calculations, you MUST use direct calculation formulas, and you MAY NOT use built-in Excel functions to calculate these values. Format all amounts (in the gold-shaded cells) as Currency with the $ symbol and 2 decimal places, and all n and t entries (in the green-shaded cells) as Number with 0 decimals.
Bring your 5-year projected budget total forward from the Project Budget and Projection sheet
			8 Loan Payment (determine the monthly payment required for a 5-year loan to cover the remainder of the budget costs)
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		The additional amount you need to cover your 5-year budget costs (P)
Principal amount to invest (P), based on your budget total and the percentage in B31			Your APR (r)	Complete the third interest rate entry in section 4 above
Your APR (r)	Complete the interest rate entry in section 4 above		Number of payments per year (n)
Number of compoundings per year (n)			Number of years (t)
Number of years (t)			The monthly loan payment (PMT)
Your 5-year sponsorship total
7 Fundraising (invest monthly donations for 5 years with monthly compounding)			Legend
Monthly contribution amount (PMT)	Complete the second interest rate entry in section 4 above		If a cell is shaded	You should
Your APR (r)	Complete the second interest rate entry in section 4 above		Blue	Enter a text response
Number of compoundings per year (n)			Green	Enter a number
Number of years (t)			Gold	Enter an Excel formula
Your 5-year fundraising total (Hint: use the "future value of periodic payments" formula in the assignments instructions)			Any other color	Make no changes

Historical Mortgage Rates

	Month
Year	1	2	3	4	5	6	7	8	9	10	11	12
1971				7.31	7.43	7.53	7.6	7.7	7.69	7.63	7.55	7.48
1972	7.44	7.32	7.29	7.29	7.37	7.37	7.40	7.40	7.42	7.42	7.43	7.44
1973	7.44	7.44	7.46	7.54	7.65	7.73	8.05	8.50	8.82	8.77	8.58	8.54
1974	8.54	8.46	8.41	8.58	8.97	9.09	9.28	9.59	9.96	9.98	9.79	9.62
1975	9.43	9.10	8.89	8.82	8.91	8.89	8.89	8.94	9.12	9.22	9.15	9.10
1976	9.02	8.81	8.76	8.73	8.76	8.85	8.93	9.00	8.98	8.92	8.81	8.79
1977	8.72	8.67	8.69	8.75	8.83	8.86	8.94	8.94	8.90	8.92	8.92	8.96
1978	9.01	9.14	9.20	9.35	9.57	9.71	9.74	9.78	9.76	9.86	10.11	10.35
1979	10.39	10.41	10.43	10.50	10.69	11.04	11.09	11.09	11.30	11.64	12.83	12.90
1980	12.88	13.04	15.28	16.32	14.26	12.71	12.19	12.56	13.20	13.79	14.21	14.79
1981	14.90	15.13	15.40	15.58	16.40	16.70	16.83	17.28	18.16	18.45	17.82	16.95
1982	17.48	17.60	17.16	16.89	16.68	16.70	16.82	16.27	15.43	14.61	13.82	13.62
1983	13.25	13.04	12.80	12.78	12.63	12.87	13.43	13.81	13.73	13.54	13.44	13.42
1984	13.37	13.23	13.39	13.65	13.94	14.42	14.67	14.47	14.35	14.13	13.64	13.18
1985	13.08	12.92	13.17	13.20	12.91	12.22	12.03	12.19	12.19	12.14	11.78	11.26
1986	10.89	10.71	10.08	9.94	10.15	10.69	10.51	10.20	10.01	9.98	9.70	9.32
1987	9.20	9.08	9.04	9.83	10.60	10.54	10.28	10.33	10.89	11.26	10.65	10.64
1988	10.38	9.89	9.93	10.20	10.46	10.46	10.43	10.60	10.48	10.30	10.27	10.61
1989	10.73	10.65	11.03	11.05	10.77	10.20	9.88	9.99	10.13	9.95	9.77	9.74
1990	9.90	10.20	10.27	10.37	10.48	10.16	10.04	10.10	10.18	10.17	10.01	9.67
1991	9.64	9.37	9.50	9.50	9.47	9.62	9.58	9.24	9.01	8.86	8.71	8.50
1992	8.43	8.76	8.94	8.85	8.67	8.51	8.13	7.98	7.92	8.09	8.31	8.21
1993	7.99	7.68	7.50	7.46	7.47	7.42	7.21	7.11	6.91	6.83	7.16	7.17
1994	7.07	7.15	7.68	8.32	8.60	8.40	8.61	8.51	8.64	8.93	9.17	9.20
1995	9.15	8.83	8.46	8.32	7.96	7.57	7.61	7.86	7.64	7.48	7.38	7.20
1996	7.03	7.08	7.62	7.93	8.07	8.32	8.25	8.00	8.23	7.92	7.62	7.60
1997	7.82	7.65	7.90	8.14	7.94	7.69	7.50	7.48	7.43	7.29	7.21	7.10
1998	6.99	7.04	7.13	7.14	7.14	7.00	6.95	6.92	6.72	6.71	6.87	6.74
1999	6.79	6.81	7.04	6.92	7.15	7.55	7.63	7.94	7.82	7.85	7.74	7.91
2000	8.21	8.33	8.24	8.15	8.52	8.29	8.15	8.03	7.91	7.80	7.75	7.38
2001	7.03	7.05	6.95	7.08	7.15	7.16	7.13	6.95	6.82	6.62	6.66	7.07
2002	7.00	6.89	7.01	6.99	6.81	6.65	6.49	6.29	6.09	6.11	6.07	6.05
2003	5.92	5.84	5.75	5.81	5.48	5.23	5.63	6.26	6.15	5.95	5.93	5.88
2004	5.71	5.64	5.45	5.83	6.27	6.29	6.06	5.87	5.75	5.72	5.73	5.75
2005	5.71	5.63	5.93	5.86	5.72	5.58	5.70	5.82	5.77	6.07	6.33	6.27
2006	6.15	6.25	6.32	6.51	6.60	6.68	6.76	6.52	6.40	6.36	6.24	6.14
2007	6.22	6.29	6.16	6.18	6.26	6.66	6.70	6.57	6.38	6.38	6.21	6.10
2008	5.76	5.92	5.97	5.92	6.04	6.32	6.43	6.48	6.04	6.20	6.09	5.29
2009	5.05	5.13	5.00	4.81	4.86	5.42	5.22	5.19	5.06	4.95	4.88	4.93
2010	5.03	4.99	4.97	5.10	4.89	4.74	4.56	4.43	4.35	4.23	4.30	4.71
2011	4.76	4.95	4.84	4.84	4.64	4.51	4.55	4.27	4.11	4.07	3.99	3.96
2012	3.92	3.89	3.95	3.91	3.80	3.68	3.55	3.60	3.50	3.38	3.35	3.35
2013	3.41	3.53	3.57	3.45	3.54	4.07	4.37	4.46	4.49	4.19	4.26	4.46
2014	4.43	4.30	4.34	4.34	4.19	4.16	4.13	4.12	4.16	4.04	4.00	3.86
2015	3.67	3.71	3.77	3.67	3.84	3.98	4.05	3.91	3.89	3.80	3.94	3.96
2016	3.87	3.66	3.69	3.61	3.60	3.57	3.44	3.44	3.46	3.47	3.77	4.20
2017	4.15	4.17	4.20	4.05	4.01	3.90	3.97	3.88	3.81	3.90	3.92	3.95
2018	4.03	4.33	4.44	4.47	4.59	4.57	4.53	4.55	4.63	4.83	4.87	4.64
2019	4.46	4.37	4.27	4.14	4.07	3.80	3.77	3.62	3.61	3.69	3.70	3.72
2020	3.62	3.47	3.45	3.31	3.23	3.16	3.02	2.94	2.89