Grand Canyon University Algebra Worksheet

Major Assignment 1 Grading SheetCompetency
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.
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
using cell references.
Monthly
Budget
Summary and
Your Percentage of Total formulas are correctly calculated from the
Analysis
Subtotals and Budget Total in the Summary section, using cell references.
Charts
Budget Cost
Projection
All dollar amounts are formatted as Currency showing the $ symbol and with
2 decimal places of precision, and all percentages are formatted as
Percentage with 1 decimal place.
Your bar chart correctly shows the Subtotals as bars, has the Budget
Category entries as labels, and has an appropriate title and axis labels (12
points for including the chart, 4 points for each additional element).
Your pie chart correctly shows the Percentages as pie slices and has an
appropriate title (12 points for including the chart, 4 points each for showing
the percentages as data labels and for having a correct title).
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.
You have correctly entered your Budget Total from cell G21 of the Monthly
Budget tab using an appropriate cell reference.
Your “value of t” entries are correct.
Your 1-year, 5-year, and 10-year projections are correct Excel formulas using
Budget
cell references.
Projections
Your percent increase calculations are correct Excel formulas using cell
references.
Budget
Projections
Your Current Budget, Projected Budget and Percent Increase cells are
formatted as indicated in the instructions.
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
and Predicted
the slope, y-intercept, and years of education.
Incomes
Your slope, y-intercept, and Predicted incomes are formatted as indicated in
the instructions.
You have included an XY-Scatterplot of the BLS data, adding an appropriate
title and axis labels.
Chart
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
abbreviations from the table (including capitalization) and adding an N/A
Conversions
entry if fewer than 3 conversions are needed.
Conversions
Your formulas for the ratios are correct, using appropriate cell references.
Your final quantity formulas are correct and use cell references for all inputs.
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
Conversions functions.
nt 1 Grading Sheet
(optional for
student use) Did
you meet the
requirements?
Points
possible
Your
points
1
10
20
30
20
25
20
8
40
11
28
20
Subtotals
233
2
6
12
3
2
3
24
24
0
Scoring comments
7
Subtotals
83
0
24
68
19
24
8
Subtotals
143
0
8
20
40
32
16
Subtotals
116
0
Totals
575
0
Percentage
100.00%
0.00%
Scaled out of
125
125.00
0.00
1 Enter your full name here. If your full name is less
than 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.
Monthly Budget
Housing and Utilities
Budget Item
Mortgage payment
Number of times
purchased each
month
Number
Cost per purchase
Cost per item
Total cost
Cost
Subtotal:
0
Food and Entertainment
Budget Item
Number of times
purchased each
month
Cost per purchase
Total cost
Subtotal:
Insurance, Health, and Medical
Budget Item
Number of times
purchased each
month
Cost per purchase
Total cost
Subtotal:
Savings and Charitable Giving
Budget Item
Number of times
purchased each
month
Cost per purchase
Total cost
Subtotal:
Miscellaneous
Budget Item
Number of times
purchased each
month
Cost per purchase
Subtotal:
Total cost
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
Housing and Utilities
Food and Entertainment
Insurance, Health, and Medical
Savings and Charitable Giving
Miscellaneous
Subtotal
Percentage of Total
0
Budget Total
4 Below you will insert two charts for this data. First, insert a bar chart that shows
each Subtotal amount as a bar, has the Budget Categories as bar labels, changes the
chart title from the default, and adds axis titles for both axes. 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, changes the chart title from the default, and
includes the percentages as data labels.
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 years and forward to 24 years. (That is, the line should start at 8 years and end at 24 years on
the horizontal axis.)
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.
Name Monthly Budget
BLS Data
Years of Education
(X)
Average Weekly Income
(Y)
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
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
10
12
13
14
16
18
19
20
Best-Fit Line Parameters
Slope (m)
Y-Intercept (0, b)
0
Predicted Incomes Based on Best Fit
Years of
Average Weekly
Education
Income
(X)
(Y = m*X + b)
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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
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.
7 On this sheet, you will consider several conversions related to calculations you might see in a professional context
and that you should order ratios so that 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.
the other hand, if you use a ratio of the First Units over the Second units, then your multiplier will include a cell from
would multiply by B12/D12.
=
=
Conversion
Factor
2.20462
29.5735
pounds (lb)
milliliter (mL)
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)
Quantity of
First Units
=
1
1
kilogram (kg)
fluid ounce (floz)
1
Second Units
8 Now, use entries from the conversion table to perform the following conversions. Note that you may use entries o
conversion is possible. For each part, the number of ratios required is shown in the table. Note that each of your rati
the example. No special formatting is required for the cells containing your formulas. In the blue cells, enter the rati
You should use the abbreviations provided above, including capitalization as given. Format your final answer to num
Initial quantity
and units to
convert from
x
Example: convert fluid
ounces per kilogram to
milliliters per pound
10
floz/kg
x
A) Convert milligrams per
liter to micrograms per
fluid ounce
50
mg/L
x
B) Convert grams per
hour to kilograms per day
20
g/h
x
C) Convert square feet per
gallon to square meters
per liter (hint: one
conversion factor is
applied twice)
5000
ft^2/gal
x
First ratio and units
29.5735
D) Convert milliliters per
kilogram per hour to
teaspoons per pound per
day
30
mL/(kg*h)
x
9 Some conversions use ratios plus another additive term (summand). Here, you’ll convert from Fahrenheit to Celsius and fro
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: specifically, you should convert the value from Fahrenheit in B50 to Cels
No special formatting is required for your cells here.
Fahrenheit
Celsius
Your full name
entry must be
longer
Your full name
entry must be
longer
ht see in a professional context. For each conversion, you’ll identify and apply appropriate ratios to yield the given result. Remem
for intermediate steps.
ble in your formulas in part 8. Note if you use a ratio of the Second Units over the First Units, then your multiplier will include
ltiplier will include a cell from column B over a cel from Column D. For example, when multiplying by lb/kg, you would multip
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
Note that you may use entries only from the table above, even if a more direct
ble. Note that each of your ratio formulas will include cell references, as illustrated in
In the blue cells, enter the ratio of units that you multiplied by for each conversion.
ormat your final answer to number with 4 decimal places.
First ratio and units
mL/floz
x
x
Second ratio and units
0.453592909
kg/lb
x
x
x
x
x
x
x
x
x
om Fahrenheit to Celsius and from Celsius to Fahrenheit, using the following symbolic
e from Fahrenheit in B50 to Celsius in B51 and from Celsius in C51 to Fahrenheit in C50.
x
atios to yield the given result. Remember that ratios can use either unit over the other,
nits, then your multiplier will include a cell from column D over a cel from Column B; on
multiplying by lb/kg, you would multiply by D12/B12; when multiplying by kg/lb, you
Third ratio and units
=
Final quantity and units to
convert to
=
134.1433
=
=
=
mL/lb
=