3 ma - major assignment 3 PDF

Preview

Summary

major assignment 3
...

Description

This document was exported from Numbers. Each table was converted to objects on each Numbers sheet were placed on separate worksheets. Plea calculations may differ in Excel.

Numbers Sheet Name

Numbers Table Name

Grading Sheet Table 1 Monthly Budget Table 1 Income Analysis Table 1 Conversions Table 1

an Excel worksheet. All other se be aware that formula

Excel Worksheet Name Grading Sheet Monthly Budget Income Analysis Conversions

Competency

Name

Budget

Monthly Budget Summary and Analysis

Charts

Best-Fit Line and Predicted Incomes

Income Analysis

Chart

3

Conversions

Conversions

Fahrenheit / Celsius Conversions

4

Major Assignment 1 Grading Sheet Requirements for full credit

(optional for student use) Did you meet the requirements?

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. 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. Your Percentages are formulas that correctly calculate your Budget Total from your Subtotals, using cell references. All cells are formatted as Currency showing the $ symbol and with 2 decimal places of precision. Your bar chart correctly shows the Subtotals as bars, has the Budget Category entries as labels, and has an appropriate title and axis labels (3 points for including the chart, 1 point for each additional element). Your pie chart correctly shows the Percentages as pie slices and has an appropriate title (3 points for including the chart, 1 point each for showing the percentages and having a correct title).

Subtotals You have correctly calculated the slope and y-intercept for the data provided, using appropriate Excel functions. Your formulas for Predicted Incomes are correct, using cell references for the slope, y-intercept, and years of education. 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. You have added a trendline to your scatterplot, extending it to 8 years on the left and 24 years on the right.

Subtotals You have identified the correct units for your final quantity, using the units abbreviations (including capitalization) provided in the conversion factors table.

5

You have identified the conversion ratios to use, using correct units abbreviations from the table (including capitalization) and adding an N/A entry if fewer than 3 conversions are needed. Your formulas for the ratios are correct, using appropriate cell references. Your final quantity is calculated correctly and uses cell references. Your Fahrenheit to Celsius conversion formulas are correct and use cell references. The calculations are direct and do not use built-in Excel functions.

Subtotals

Totals Percentage

Scaled out of 100

6

Points Your points possible

Scoring comments

1 10 10 10 10 25 5 2 10 11 7

5

106

0

6 17 19 6 2

50

0

4

7

10 10 8 4

36

0

192

0

100.00%

0.00%

100.00

0.00

8

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

Number of times purchased each month

Mortgage payment electric bill Water bill

Cost per purchase 1 1 1

Total cost

$1,600.00 $200.00 $250.00

$1,600.00 $200.00 $250.00

Subtotal:

$2,050.00

Food and Entertainment Budget Item

Number of times purchased each month

Netflix groceries Eating out

Cost per purchase 1 6 4

Total cost

$12.99 $250.55 $65.25

$12.99 $1503.30 $261.00

Subtotal:

$1777.29

Insurance, Health, and Medical Budget Item Car insurance Health insurance Contacts

Number of times purchased each month

Cost per purchase 1 1 2

$235.00 $574.00 $185.00

Total cost $235.00 $574.00 $370.00

$ $

Subtotal:

Savings and Charitable Giving

$1179.00

$

Budget Item Tithe and offering

Number of times purchased each month

Cost per purchase 4

$

Total cost

$45.00

$180.00

Subtotal:

$180.00

Miscellaneous Budget Item Coffee

Number of times purchased each month 15

Cost per purchase

Total cost

$5.50

$82.50

Subtotal:

$82.50

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

Subtotal

Housing and Utilities Food and Entertainment Insurance, Health, and Medical Savings and Charitable Giving Miscellaneous

$2,050.00 $1777.29 $1179.00 $180.00 $82.50

Budget Total

$5,268.79

Percentage of Total 38.9% 33.7% 22.4% 3.4% 1.6%

4 Below you will insert two charts for this data. First, insert a bar chart that shows each Subtotal amount as a bar and has the Budget Categories as bar labels. 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.

S UB T O TAL $2,500.00 $2,000.00 $1,500.00

$2,050.00 $1777.29

$1,000.00

$1179.00

$500.00 $180.00

$82.50

$0.00 Housing and Food and Insurance, Utilities Entertainment Health, and Medical

Savings and Miscellaneous Charitable Giving

PERCENTAGE OF T OTAL 1

2

3

4

5

3% 2%

22% 39%

34%

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 fi intercept of the best-fit line for the given coordinates. Then, to the right, use the slope and y-intercept to weekly income for all years of education from 8 through 24. Finally, create a chart showing the BLS data a an auto trendline to this chart showing years of education versus predicted average income superimpose forecasting backward to 8 and forward to 24 years. Here, you should format your slope and y-intercept as numbers with 0 decimal places and your average w 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 t this reference for the assignment. Your name from the Monthly Budget sheet:

0

BLS Data Years of Education (X)

Predicted Incomes Based on Best Fit Average Weekly Years of Education Income (X) (Y = m*X + b)

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)

100 200

13

8

$1,000.00

9

$1,100.00

10

$1,200.00

11

$1,300.00

12

$1,400.00

13

$1,500.00

14

$1,600.00

15 16 17 18 19 20 21 22 23 24

$1,700.00 $1,800.00 $1,900.00 $2,000.00 $2,100.00 $2,200.00 $2,300.00 $2,400.00 $2,500.00 $2,600.00

nd the slope and yo calculate the average as a scatterplot, and add d on the BLS data and

Legend If a cell is shaded Blue

weekly incomes as

Green Gold Any other color

ake any steps related to

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

Average Weekly Income (Y) $1.00 $0.90 $0.80 $0.70 $0.60 $0.50 $0.40 $0.30 $0.20 $0.10 $0.00 0

5

10

15

20

25

Average Weekly Income best fit (Y=100x+200 $3,000.00 $2,500.00 $2,000.00 $1,500.00 $1,000.00 $500.00 $0.00 0

5

10

15

20

14

25

30

7

On this sheet, you will consider several conversions related to calculations you might see in a professional co appropriate ratios to yield the given result. Remember that ratios can use either unit over the other, and that y numerator and denominator for intermediate steps. First, examine this conversion factor table; you will use conversion factors from this table in your formulas in p over the First Units, then your multiplier will be the conversion factor itself; on the other hand, if you use a rati multiplier will be 1 divided by the conversion factor. For example, when multiplying by lb/kg, you would multi multiply by 1/D12.

Quantity of

First Units

=

1 1 1 1 1 1 1 1 1 1 1

kilogram (kg) fluid ounce (floz) ounce (oz) kilogram (kg) gram (g) milligram (mg) liter (L) liter (L) teaspoon (tsp) meter (m) day (d)

= = = = = = = = = = =

Conversion Factor 2.20462 29.5735 28.3495 1000 1000 1000 33.8140 0.2642 4.9289 3.2808 24

Second Units pounds (lb) milliliter (mL) gram (g) gram (g) milligram (mg) microgram (mcg) fluid ounces (floz) gallons (gal) milliliter (mL) feet (ft) hours (h)

8 Now, use entries from the conversion table to perform the following conversions. Note that you may use en conversion is possible. For each part, the number of ratios required is shown in the table. Note that your ratios factors above (like =F10) or the reciprocal (like =1/F9), as illustrated in the example. No special formatting is re blue cells, enter the ratio of units that you multiplied by for each conversion. You should use the abbreviations

Example: convert fluid ounces per kilogram to milliliters per pound Initial quantity and units to convert from x First ratio and units x Second ratio and units x Third ratio and units = Final quantity and units to convert to

10

A) Convert milligrams per liter to micrograms per fluid ounce

floz/kg

50

mL/floz

1000

kg/lb

0.0296

x 29.5735

x

x 0.453592909

mcg/mg x

= 134.1432991

mg/L

L/floz

= mL/lb

9 Some conversions use ratios plus another additive term (summand) 15

1478.676483

mcg/floz

Here you'll convert from Fahrenheit t

9 Some conversions use ratios plus another additive term (summand). Here, you ll convert from Fahrenheit t symbolic 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. No special formatting is required for your cells here.

Fahrenheit

Your full name entry must be longer

136.4

Celsius

-16.11111111

Your full name entry must be longer

16

ontext. For each conversion, you'll identify and apply you should order ratios so that units cancel in the

part 8. Note that if you use a ratio of the Second Units o of the First Units over the Second units, then your iply by D12; when multiplying by kg/lb, you would

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

ntries only from the table above, even if a more direct s for the formulas may be either one of the conversion equired for the cells containing your formulas. In the provided above, including capitalization as given.

C) Convert square feet per gallon to square B) Convert grams per hour to kilograms meters per liter (hint: one conversion factor per day is applied twice) 20

g/h

5000

h/d

0.2642

g/kg

0.3048

x 24

ft^2/gal

30

g/L

24

m/ft

0.453592909

m/ft

0.202884202

m/L

66.25932147

x

x 0.001

D) Convert millilit per hour to teasp per d

x

x

x

x 0.3048 = 0.48

x

= kg/d

122.7119259

to Celsius and from Celsius to Fahrenheit, using the following

17

to Celsius and from Celsius to Fahrenheit, using the following

18

ters per kilogram poons per pound day mL/(kg*h) x h/d x lb/kg x mL/tsp

tsp/lb/d

19

20...