• Home

Excel

Part 1: Check your dataset and get to know your variables

1. There are 2 tabs in the Excel file which you can access from the bottom left of your screen called “Dataset” and “Codebook”. First scroll through the Dataset and visually examine whether there are any unusual data points or outliers. Then switch over to the Codebook tab and familiarize yourself with how the variables were coded.

2.
(1 point)
The variables in this Dataset that are useful for analysis are
age
,
gender
,
race
,
educ
,
BMI
,
stress
,
yoga
, and
sleep
. For each of them, is it continuous or categorical? Fill in this table with all variables by its correct type. The first row is an example.

Continuous variables

Categorical variables

1. Age

2.

1. Gender

2.

Part 2: Provide some descriptive statistics on your variables

1.
(1 point)
Depending on whether a variable is continuous or categorical, use the appropriate commands in Excel to compute the following statistics. (There are 110 participants in this dataset, so hand-calculating is absolutely NOT recommended.) The first row is an example. You will need to create additional rows for “n (%)” depending on the number categories each variable actually has.

For continuous variables only

For categorical variables only

Variable 1 name: Age

Mean = 35.2 years

Median = 35 years

Standard deviation = 3.14 years

Variable 1 name: Gender

n (%) for 0 or male = 57 (51.8%)

n (%) for 1 or female = 53 (48.2%)

Variable 2 name:

Mean =

Median =

Standard deviation =

Variable 2 name:

n (%)

n (%)

etc.

Insert additional rows as needed.

Don’t know how to insert rows in Word? Read this: Add a cell, row, or column to a table (microsoft.com)

2. The table above is NOT how results should actually be presented. They are just the results from your calculations. In reality, your descriptive statistics results should be filled into a “Table 1” that is professionally formatted.


(2 points)
Using the template provided below, complete the Table 1 with the appropriate category names and the data that you have calculated above. The information for age and gender have been provided as examples. (Note: If there are extra rows that you don’t need, remember to delete the blank rows appropriately.)

Table 1. Demographics and participant characteristics of 110 adults in cross-sectional survey to examine the effects of yoga and sleep patterns on stress.

Demographics and characteristics

Mean (SD) or n (%)

Age in years

35.2 (3.14)

Gender

Male

57 (51.8%)

Female

53 (48.2%)

Race/ethnicity

Education level

BMI

Stress

Yoga

Sleep

Footnote: SD=standard deviation

3.
(2 points)
Using Excel, draw 2 bar graphs, one to show the frequency distribution of yoga and another the frequency distribution of sleep
. Format each of your graph so that it has the appropriate x-axis labels (not just 0, 1, 2, etc.) as well as an appropriate title.

Hint #1: The Excel function to use is “Insert > PivotChart”.

Hint #2: Your graphs should look like the sample below:

Paste your two bar graphs below. Make sure to give each graph an appropriate title, and label all your x-axis categories.


Part 3: Let’s run some inferential statistics

Problem 1 (2 points)
– Say you want to know whether there is a statistically significant difference between males and females when it comes to their BMI levels.

1. Which of the following test would you use to prove whether or not there is a significant difference in mean BMI levels between the genders in this study? (highlight your answer)

A. Independent samples t-test

B. Paired samples/repeated measures t-test

C. Chi-square test

D. Correlation test

2. Assuming you chose the correct test above, the results from your analysis can be presented in a Table 2. The template below has the data/results but no labels. Complete this table by filling in the row and column labels (by replacing the ???). Hint #1: Men on average had higher BMI than women. Hint #2: One of these labels should say “p-value”.

Table 2. BMI levels of study participants by gender.

???

???

???

???

27.7 (4.3)

0.810

???

27.5 (4.7)

Footnote: SD = standard deviation

Problem 2 (2 points)
– Now we come to the thick of the hypothesis. You want to know whether doing yoga makes any difference to participants’ stress level, and if so, does the amount of yoga practices, measured as the number of times per week make a difference?

1. Which of the following test would you use to prove whether or not there is a significant association between the frequency of doing yoga and stress level in this study? (highlight your answer)

A. Independent samples t-test

B. Paired samples/repeated measures t-test

C. Chi-square test

D. Correlation test

2. Assuming you chose the correct test above, the results from your analysis can now be presented in a Table 3. Again, the template below has the data but no labels. Complete this table by filling in the row and column labels (by replacing the ???). Hint #1: We usually present categories from low to high. Hint #2: One of these labels should say “p-value”.

Table 3. Association between frequency of doing yoga and stress level.

???

???

???

Frequency of doing yoga

<0.000

???

5 (8.3%)

17 (34.0%)

???

0 (0.0%)

20 (40.0%)

???

13 (21.7%)

2 (4.0%)

???

30 (50.0%)

6 (12.0%)

???

12 (20.0%)

5 (10.0%)

Frequency distribution of education

Total Less than high school High school g raduate/GED Some college College graduate Graduate or professional degree 21 23 27 21 18

Excel

Grader – Instructions Excel 2016 Project

GO16_XL_CH04_GRADER_4E_HW – Condo Loan 1.1

Project Description:

In this project, you will use a PMT function to calculate a value and then use it in a two-variable data table. You will create named ranges and use them in formulas.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download, save, and open the workbook named go16_xl_ch04_grader_4e_hw.xlsx.

0

2

Display the Condo Purchase worksheet. In cell B5, insert the PMT function using the data from the range B2:B5—be sure to divide the interest rate by 12 multiply the years by 12 and display the payment as a positive number. The result, $6,598.44, is larger than the maximum desired payment of $6,250.

10

3

Use Goal Seek to change the amount of the loan so that the payment is $6,250. Then, in cell A7, type Option #1 Reduce the Loan

To cell A1, apply the Title cell style, and then Copy the format from cell A1 to cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A8. In cell B2, type 615000 to restore the original loan amount.

7

4

Use Goal Seek to change the period of the loan (of the original data) so that the payment is $6,250. Then, in cell A13, type Option #2 Increase Years

Format the cell the same as cell A7. Copy the range A2:B5, and then Paste the Values & Number Formatting to cell A14. Display the value in cell B15 with two decimal places, and then in cell B3, type 10 to restore the original value. Save your workbook.

4

5

Display the Payment Table worksheet, in the range A2:B4, enter the following row titles and data.

Amount of Loan $615,000
Period (months) 120
Interest Rate (per year) 5.25%

5

6

In cell C8, type 60—the number of months in a 5-year loan. In D8, type 120—the number of months in a 10-year loan. Fill the series through cell H8. Apply Bold and Center to the range C8:H8.

10

7

Beginning in cell B9, enter varying interest rates in decrements of 0.5% beginning with 7.5% and ending with 4.0% If necessary, format all the interest rates with two decimal places, and then apply Bold and Center to the range B9:B16.

10

8

In cell B8, enter a PMT function using the information in cells B2:B4. Be sure that you convert the interest rate to a monthly rate and that the result displays as a positive number.

5

9

Create a Data table in the range B8:H16 using the information in cells B2:B4 in which the Row input cell is the Period and the Column input cell is the Interest rate. Copy the format from cell B8 to the results in the data table. Format cell D16 with the Note cell style as the payment option that is close to but less than $6,250 per month.

10

10

Display the Advertising Costs by Quarter worksheet, and then apply the Currency [0] cell style to the range B6:E6 and to the range B18:E18. Apply the Comma [0] cell style to the range B7:E17, and then apply the Total cell style to the range B18:E18.

9

11

Name the following ranges: B6:E10 Newspaper_Costs B11:E14 Digital_Costs B15:E16 Magazine_Costs B17:E17 Billboard_Costs

Insert a new row 15. In cell A15, type Business Podcasts In cell B15, type 12500 In cell C15, type 11525 In cell D15, type 14455 In cell E15, type 13009

7

12

Display the Name Manager, click Digital_Costs, and then change cell E14 to E15. Select the Billboard_Costs and then Edit the name to Outdoor_Costs Save your workbook.

7

13

Display the Annual Advertising Costs sheet. In cell B5, type =sum(N and sum the values using the appropriate range name in the displayed list of functions. Repeat for the other named ranges. From the Formulas tab, in the Function Library group, use AutoSum to sum all the costs. Apply Currency [0], Comma [0], and Total cell styles to the appropriate cells.

10

14

In the sheet tab row, right-click any sheet tab, and then click Select All Sheets. Display the Page Setup dialog box, click the Margins tab, and then under Center on page, click Horizontally. Click the Header/Footer tab, click Custom Footer, and then in the Left section, insert the File Name. Click OK two times. Ungroup the sheets, display the Payment Table sheet. Set the Orientation of this sheet to Landscape and set the Scale to 85%.

5

15

Display Backstage view, click Show All Properties. On the list of Properties, in the Tags box, type payment table, advertising costs In the Subject box, type your course name and section #. Under Related People, be sure that your name displays—edit if necessary. On the left, click Print. Under Settings, click the Print Active Sheets arrow, and then click Print Entire Workbook. At the bottom of the window, click Next Page to scroll through the worksheets. On the left, click Save.

1

16

Ensure that the worksheets are correctly named and placed in the following order in the workbook: Condo Purchase, Payment Table, Advertising Costs by Quarter, Annual Advertising Costs. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0

Total Points

100

Created On: 07/05/2019 1 GO16_XL_CH04_GRADER_4E_HW – Condo Loan 1.1

Excel

 Please prepare by first watching all the videos for better understanding.

These videos are part of the Excel Statistics Essential Training module offered through LinkedIn Learning. These videos (each only a few minutes long) will give you a refresher of concepts you should already know, such as variable types, measures of central tendency (mean, median, mode), and measures of variability (standard deviation, IQR, etc.). You will learn how these basic concepts can be easily calculated using Excel.

Once you are familiar with Excel and its calculation and graphing functions, you can go ahead and proceed with the assignment worksheet attach.

Excel

Condo Purchase

Jesse Jewelers Condo Purchase
Amount of Loan $615,000
Period (years) 10
Interest Rate (per year) 5.25%
Payment (per month)

Payment Table

Loan Options for Condo Purchase: Rates versus Months
Payment Options
Number of Monthly Payments
Rates

Advertising Costs by Quarter

Jesse Jewelers
Advertising Costs by Quarter
All U.S. Stores
1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Local Newspapers 14471 13809 13809 2006
Regional Newspapers 13735 13031 15365 1692
National Newspapers 12489 12674 12927 13042
Specialty Newspapers 1648 3188 3628 1202
Online Newspapers 13827 12452 12451 14821
Internet 13342 13443 13555 14545
Email 14549 15525 16740 12448
TV 18821 20403 21211 21958
Travel Podcasts 12342 13475 15794 15948
Women’s Magazines 12308 21101 20034 19105
Men’s Magazines 18637 188357 16662 21118
Billboard Costs 12294 13418 14461 13473
Total by Quarter 158462 340877 176637 151358

Annual Advertising Costs

Jesse Jewelers
Advertising Costs
Annual Costs for All U.S. Stores
Newspaper_Costs
Digital_Costs
Magazine_Costs
Outdoor_Costs
Total

Excel

Job Hires by Location

Dallas-Fort Worth Job Fair
Successful Job Hires by Location
2012 2013 2014 2015 2016
Dallas 560 527 660 539 666
Fort Worth 288 282 311 293 307
Arlington 131 130 138 152 150
Plano 108 125 133 146 142
Irving 86 108 83 90 96
Carrollton 71 74 76 73 84
Denton 83 86 96 84 94
Total Attendance 1,327 1,332 1,497 1,377 1,539

Excel

Sheet1

Revenues 2017 2018 2019 2020 2021
Q1 $ 68,000 $ 80,400 $ 81,750 $ 91,050 $ 94,000
Q2 77,800 81,750 90,500 97,600 95,500
Q3 83,575 90,675 93,750 100,500 99,350
Q4 85,850 90,750 91,775 100,500 102,500
Total $ 315,225 $ 343,575 $ 357,775 $ 389,650 $ 391,350

Excel

Referee Requests

Requests for Referee Assignment
First Name Last Name Sports Event Program Event # Opponent Day Date Time Site Campus
Danny Litowitz Tennis Match TENNIS 76243 Sunset Tuesday 4/8 4:00 Davidson Courts Main
Vanessa Perrigin Tennis Match TENNIS 74467 Eastmont Friday 5/4 4:00 Davidson Courts Main
Kimberly Fisher Swimming Meet SWIMMING 75008 Rogers Monday 5/24 4:30 Cameron Pool Valley
Linda Diaz Swimming Meet SWIMMING 74417 Brewster Friday 6/12 5:00 Cameron Pool Valley

Excel

Sporting Event

Event Program Event # Opponent Day Date Time Site Campus Coach Assistant Coach Contact Number Referee
Baseball Game BASEBALL 74376 Lake City Monday 5/24 4:30 Freedom Field West Earl Neff Sam Walsh (703) 555-0612 Harry Waters
Baseball Game BASEBALL 74436 Sunset Friday 5/1 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0218 Staff
Baseball Game BASEBALL 74438 Skyline Monday 4/10 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0150 Carlos Martinez
Baseball Game BASEBALL 74440 Newport Thursday 4/15 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0157 Carlos Martinez
Baseball Game BASEBALL 74441 Madison Friday 4/17 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0168 Staff
Baseball Game BASEBALL 74400 Eagleton Friday 5/4 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0234 Carlos Martinez
Baseball Game BASEBALL 75012 Belmont Wednesday 5/10 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0331 Harry Waters
Baseball Game BASEBALL 75476 Liberty Monday 5/9 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0211 Carlos Martinez
Baseball Game BASEBALL 75477 Grand View Thursday 5/22 4:30 Freedom Field West Earl Neff Sam Walsh (703) 555-0126 Harry Waters
Baseball Game BASEBALL 75605 West Ridge Friday 5/15 4:30 Freedom Field West Earl Neff Sam Walsh (703) 555-0215 Staff
Baseball Game BASEBALL 76245 Lewiston Tuesday 4/8 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0154 Carlos Martinez
Baseball Game BASEBALL 76453 Eastmont Monday 5/14 4:30 Freedom Field West Earl Neff Sam Walsh (703) 555-0063 Staff
Baseball Game BASEBALL 76456 Interlake Tuesday 5/20 4:30 West Earl Neff Sam Walsh (703) 555-0346 Harry Waters
Baseball Game BASEBALL 76457 Brewster Monday 5/5 4:00 Freedom Field West Earl Neff Sam Walsh (703) 555-0222 Carlos Martinez
Golf Tournament GOLF 70276 Glenhaven Monday 5/5 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0143 Staff
Golf Tournament GOLF 71575 Lewiston Friday 5/1 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0141 Amanda Kurtz
Golf Tournament GOLF 71816 Brewster Tuesday 5/20 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0148 Amanda Kurtz
Golf Tournament GOLF 74413 Bellevue Friday 5/4 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0142 Staff
Golf Tournament GOLF 75271 Rogers Friday 4/17 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0140 Amanda Kurtz
Golf Tournament GOLF 75451 Liberty Monday 5/14 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0146 Staff
Golf Tournament GOLF 75452 Skyline Wednesday 5/10 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0145 Staff
Golf Tournament GOLF 75457 Newport Friday 5/15 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0147 Amanda Kurtz
Golf Tournament GOLF 76600 Patterson Monday 5/9 4:00 Valley Golf Course Valley Golf Course Randy Houston Jodi Gebbers (703) 555-0144 Staff
Swim Meet SWIMMING 74417 Brewster Friday 6/12 5:00 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0386 Staff
Swim Meet SWIMMING 74424 Lewiston Wednesday 5/29 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0584 Linda Diaz
Swim Meet SWIMMING 74427 Madison Monday 6/9 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0988 Linda Diaz
Swim Meet SWIMMING 74431 Sunset Thursday 12/31 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0715 Linda Diaz
Swim Meet SWIMMING 74433 Eagleton Friday 6/11 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0819 Linda Diaz
Swim Meet SWIMMING 75008 Rogers Monday 5/24 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0745 Staff
Swim Meet SWIMMING 75010 Grand View Wednesday 5/10 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0772 Kimberly Fisher
Swim Meet SWIMMING 75285 Skyline Tuesday 5/31 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0163 Linda Diaz
Swim Meet SWIMMING 75474 Interlake Monday 5/9 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0722 Kimberly Fisher
Swim Meet SWIMMING 75477 Woodway Thursday 5/22 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0512 Kimberly Fisher
Swim Meet SWIMMING 75603 Ferris Friday 5/15 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0123 Staff
Swim Meet SWIMMING 76244 Newport Tuesday 6/1 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0129 Linda Diaz
Swim Meet SWIMMING 76451 Lake City Monday 5/14 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0779 Kimberly Fisher
Swim Meet SWIMMING 76454 Park Tuesday 5/20 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0654 Staff
Swim Meet SWIMMING 76457 West Ridge Monday 5/5 4:30 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0229 Kimberly Fisher
Tennis Match TENNIS 73251 Belmont Friday 5/1 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0167 Vanessa Perrigin
Tennis Match TENNIS 74423 Newport Wednesday 4/5 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0111 Danny Litowitz
Tennis Match TENNIS 74426 Eagleton Monday 4/10 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0252 Vanessa Perrigin
Tennis Match TENNIS 74430 Brewster Thursday 4/15 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0155 Vanessa Perrigin
Tennis Match TENNIS 74432 Liberty Friday 4/17 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0159 Vanessa Perrigin
Tennis Match TENNIS 74467 Eastmont Friday 5/4 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0219 Staff
Tennis Match TENNIS 75007 Skyline Monday 4/2 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0018 Danny Litowitz
Tennis Match TENNIS 75274 Madison Tuesday 4/7 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0122 Danny Litowitz
Tennis Match TENNIS 76243 Sunset Tuesday 4/8 4:00 Davidson Courts Main Tim Youngston Michelle Chu (703) 555-0721 Staff
Track Meet TRACK 70261 Skyline Friday 6/11 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0005 Priscilla Torres
Track Meet TRACK 70685 Park Tuesday 5/31 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0123 Priscilla Torres
Track Meet TRACK 73268 Lewiston Thursday 12/31 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0506 Staff
Track Meet TRACK 74412 Newport Friday 6/12 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0485 Priscilla Torres
Track Meet TRACK 74471 Madison Friday 6/13 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0486 Staff
Track Meet TRACK 75287 Rogers Monday 6/9 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0313 Priscilla Torres
Track Meet TRACK 75287 Eagleton Monday 6/20 5:00 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0988 Glenn Varrelman
Track Meet TRACK 75472 Sunset Monday 6/26 4:30 Williams Memorial Track West Larry Poirier Linda Grace (703) 555-0987 Glenn Varrelman
Track Meet TRACK 76442 Ferris Wednesday 5/29 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0202 Staff
Track Meet TRACK 76563 Woodway Tuesday 6/1 4:30 Williams Memorial Track Main Larry Poirier Linda Grace (703) 555-0710 Priscilla Torres
Volleyball Game VOLLEYBALL 70266 Brewster Wednesday 6/22 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0246 Robert Hibbs
Volleyball Game VOLLEYBALL 70267 Liberty Monday 6/25 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0334 Robert Hibbs
Volleyball Game VOLLEYBALL 70271 West Ridge Thursday 6/30 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0096 Robert Hibbs
Volleyball Game VOLLEYBALL 70273 Ferris Tuesday 4/8 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0137 Mark Kilpatrick
Volleyball Game VOLLEYBALL 70274 Park Monday 4/10 5:00 Main Lynn Inaba Stephanie Anderson (703) 555-0138 Staff
Volleyball Game VOLLEYBALL 74435 Belmont Friday 6/27 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0223 Staff
Volleyball Game VOLLEYBALL 74787 Interlake Monday 4/2 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0135 Robert Hibbs
Volleyball Game VOLLEYBALL 75270 Eastmont Tuesday 6/29 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0689 Staff
Volleyball Game VOLLEYBALL 75602 Lake City Tuesday 4/7 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0136 Mark Kilpatrick
Volleyball Game VOLLEYBALL 76443 Woodway Thursday 4/15 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0139 Mark Kilpatrick
Volleyball Game VOLLEYBALL 76460 Grand View Wednesday 4/5 5:00 East Gym Main Lynn Inaba Stephanie Anderson (703) 555-0133 Mark Kilpatrick
Water Polo Match WATER POLO 74437 Woodway Wednesday 4/5 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0974 Whitney Washington
Water Polo Match WATER POLO 74468 Liberty Friday 6/13 6:00 Cameron Pool Park Sally Marques Tracey Stevens (703) 555-0859 Staff
Water Polo Match WATER POLO 75007 Park Monday 4/2 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0877 Whitney Washington
Water Polo Match WATER POLO 75011 West Ridge Wednesday 6/22 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0888 Staff
Water Polo Match WATER POLO 75286 Rogers Tuesday 4/7 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0144 Whitney Washington
Water Polo Match WATER POLO 75475 Eastmont Monday 6/20 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0651 Henry Woo
Water Polo Match WATER POLO 75478 Ferris Thursday 6/30 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0920 Whitney Washington
Water Polo Match WATER POLO 75604 Grand View Friday 6/27 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0491 Henry Woo
Water Polo Match WATER POLO 76452 Interlake Monday 6/25 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0390 Henry Woo
Water Polo Match WATER POLO 76455 Lake City Tuesday 6/29 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0510 Henry Woo
Water Polo Match WATER POLO 76458 Belmont Monday 6/26 5:00 Cameron Pool Park Victoria D’Amato Jeremy Lee (703) 555-0868 Henry Woo

Excel

Grader – Instructions Excel 2016 Project

GO16_XL_CH07_GRADER_7E_HW – Concessions Revenue 1.2

Project Description:

In the following Mastering Excel project, you will help Lindsay Johnson, the Director of Parks and Recreation, create and modify a PivotTable report and PivotChart report to analyze revenue from park concessions such as food, boat rentals, and golf fees.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. From your student files, open go16_xl_ch07_grader_7e_hw.xlsx and Save the file in your Excel Chapter 7 folder as Lastname_Firstname_7E_Concessions_Revenue

0

2

Click cell A2. Insert the last Recommended PivotTable—Sum of Amount by Park. Add the Month field to the Rows area. Place the Item field in the Columns area. Close the PivotTable Fields pane. Note, Mac users create a pivot chart with the Park and Month fields (in that order) in the Rows area, the Item field in the Column area, and the Amount field in the Values area. CLose the PivotTable Fields pane.

12

3

Insert a slicer for the Park field and a Slicer for the Item field. Move the slicers so that they are to the right of the PivotTable. Apply Slicer Style Light 4 to the Park slicer and Slicer Style Light 6 to the Item slicer.

12

4

Filter first by Memorial Park, and then by Golf Fees. Change the Number Format to the Number category and display zero decimal places and the 1000 Separator. Move the Park slicer so that its upper left corner is in cell E3. Move the Item slicer so that its upper left corner is in cell H3.

10

5

Make a copy of this worksheet and Rename the copied worksheet Memorial Golf Fees. Note, Mac users, after copying and renaming the sheet, on the Memorial Golf Fees sheet, on the ribbon, click Filter Connections. Remove all check marks.

10

6

Rename Sheet1 Concessions Revenue and then clear the filters and remove the slicers. In cell A1, type Third Quarter Park Concession Revenue and apply the Title cell style.

12

7

Display the Field List, and then remove the Month field from the Rows area to display the Grand totals for each park and for each item. Close the PivotTable Fields pane.

10

8

Insert a PivotChart using the Stacked Column chart type. Move the chart to a new worksheet named Concessions Chart

Note, Mac users, select the range A4:G9 and create a traditional Stacked Collumn chart. Move the chart to a new sheet with the name Concessions Chart

7

9

Apply the Layout 3 chart layout and Chart Style 5. As the Chart Title, type 3rd Quarter Concession Revenue and then insert a custom footer with the file name field in the left section. Hide all of the field buttons on the chart.

10

10

Hide the Park Concessions worksheet. Select all worksheets, and then insert a footer with the file name field in the left section. Change the Orientation to Landscape, set the Width to 1 page, and center the sheets Horizontally.

15

11

Click the File tab and then click Show All Properties. As the Tags, type concession revenue and as the Subject, type your course name and section #. Under Related People, be sure that your name displays as Author. Save your workbook. Note, Mac users click the File menu, and then click Properties. Change the keywords to concession revenue.

2

12

Save and close the file, and then submit for grading.

0

Total Points

100


Created On: 07/05/2019 1 GO16_XL_CH07_GRADER_7E_HW – Concessions Revenue 1.2

Excel

Park Concessions

Revenue from Park Concessions
Month Park Item Amount
July Five Points Park Food $ 34,722
July Hidden Hill Park Food 22,376
July Kennedy Park Food 8,428
July Knight’s Bridge Park Food 18,821
July Memorial Park Food 25,856
July Sunset Park Food 6,104
July Five Points Park Batting Cages
July Hidden Hill Park Batting Cages
July Kennedy Park Batting Cages
July Knight’s Bridge Park Batting Cages 3,722
July Memorial Park Batting Cages 3,640
July Sunset Park Batting Cages 2,717
July Five Points Park Bike Rentals 2,670
July Hidden Hill Park Bike Rentals 1,860
July Kennedy Park Bike Rentals 780
July Knight’s Bridge Park Bike Rentals 930
July Memorial Park Bike Rentals 1,560
July Sunset Park Bike Rentals 630
July Five Points Park Boat Rentals 7,841
July Hidden Hill Park Boat Rentals
July Kennedy Park Boat Rentals
July Knight’s Bridge Park Boat Rentals
July Memorial Park Boat Rentals
July Sunset Park Boat Rentals
July Five Points Park Field Rentals
July Hidden Hill Park Field Rentals
July Kennedy Park Field Rentals
July Knight’s Bridge Park Field Rentals 11,640
July Memorial Park Field Rentals 13,500
July Sunset Park Field Rentals 6,600
July Five Points Park Golf Fees 38,376
July Hidden Hill Park Golf Fees 18,816
July Kennedy Park Golf Fees
July Knight’s Bridge Park Golf Fees
July Memorial Park Golf Fees 24,550
July Sunset Park Golf Fees
August Five Points Park Food 33,049
August Hidden Hill Park Food 24,310
August Kennedy Park Food 9,920
August Knight’s Bridge Park Food 21,581
August Memorial Park Food 28,985
August Sunset Park Food 7,970
August Five Points Park Batting Cages
August Hidden Hill Park Batting Cages
August Kennedy Park Batting Cages
August Knight’s Bridge Park Batting Cages 3,642
August Memorial Park Batting Cages 3,864
August Sunset Park Batting Cages 2,430
August Five Points Park Bike Rentals 2,670
August Hidden Hill Park Bike Rentals 1,620
August Kennedy Park Bike Rentals 690
August Knight’s Bridge Park Bike Rentals 870
August Memorial Park Bike Rentals 1,680
August Sunset Park Bike Rentals 630
August Five Points Park Boat Rentals 7,530
August Hidden Hill Park Boat Rentals
August Kennedy Park Boat Rentals
August Knight’s Bridge Park Boat Rentals
August Memorial Park Boat Rentals
August Sunset Park Boat Rentals
August Five Points Park Field Rentals
August Hidden Hill Park Field Rentals
August Kennedy Park Field Rentals
August Knight’s Bridge Park Field Rentals 11,400
August Memorial Park Field Rentals 13,860
August Sunset Park Field Rentals 6,294
August Five Points Park Golf Fees 47,984
August Hidden Hill Park Golf Fees 22,204
August Kennedy Park Golf Fees
August Knight’s Bridge Park Golf Fees
August Memorial Park Golf Fees 24,785
August Sunset Park Golf Fees
September Five Points Park Food 42,670
September Hidden Hill Park Food 22,304
September Kennedy Park Food 8,970
September Knight’s Bridge Park Food 18,155
September Memorial Park Food 24,252
September Sunset Park Food 6,065
September Five Points Park Batting Cages
September Hidden Hill Park Batting Cages
September Kennedy Park Batting Cages
September Knight’s Bridge Park Batting Cages 3,917
September Memorial Park Batting Cages 3,750
September Sunset Park Batting Cages 2,984
September Five Points Park Bike Rentals 2,809
September Hidden Hill Park Bike Rentals 1,625
September Kennedy Park Bike Rentals 540
September Knight’s Bridge Park Bike Rentals 674
September Memorial Park Bike Rentals 1,488
September Sunset Park Bike Rentals 656
September Five Points Park Boat Rentals 7,856
September Hidden Hill Park Boat Rentals
September Kennedy Park Boat Rentals
September Knight’s Bridge Park Boat Rentals
September Memorial Park Boat Rentals
September Sunset Park Boat Rentals
September Five Points Park Field Rentals
September Hidden Hill Park Field Rentals
September Kennedy Park Field Rentals
September Knight’s Bridge Park Field Rentals 10,870
September Memorial Park Field Rentals 13,338
September Sunset Park Field Rentals 6,689
September Five Points Park Golf Fees 47,569
September Hidden Hill Park Golf Fees 19,681
September Kennedy Park Golf Fees
September Knight’s Bridge Park Golf Fees
September Memorial Park Golf Fees 25,398
September Sunset Park Golf Fees

Excel

this assigment have to be done in Excel. I have uploaded two documents. The first one are the instructions and the second one is the format with Excel that I have to complete with the instructions mentioned. 

Excel

Given below is a list of key skills that you learned in the Datacamp courses.  Write a brief explanation of each of these skills listed below. Explain how each of the following skills helps you transform raw data into meaningful information for users.

  1. Use Conditional Functions
  2. Do Conditional Formatting
  3. Do Data Validation
  4. Filter Data
  5. Create a Pivot Table
  6. Create a Chart
  7. Create a Pivot Table and Chart
  8. Use VLOOKUP

Rubric

Writing Assignment 1 & 2 RubricWriting Assignment 1 & 2 RubricCriteriaRatingsPtsThis criterion is linked to a Learning OutcomeContent5 ptsExemplaryContent is written clearly and concisely with key points and insights about the different skills they’ve learned clearly stated3 ptsAcceptableContent is written somewhat clearly and lists some key points about the skills learned. Insights may be lacking depth and clarity1 ptsUnacceptableContent is disorganized, fails to explain or give insights to the skills learned.5 pts
This criterion is linked to a Learning OutcomeWritingGrammar, Spelling, Organization3 ptsExemplaryWritten responses are free of grammatical, spelling or punctuation errors.2 ptsAcceptableWritten responses include some grammatical, spelling or punctuation errors that distract the reader1 ptsUnacceptableWritten responses contain numerous grammatical, spelling or punctuation errors3 pts
Total Points: 8

Excel

Graded Summary Report

Illustrated Excel 2019 | Module 8: SAM Project 1a
Ruby Rawls
SUBMISSION #1 | SCORE IS: 18 OUT OF 100 GE ver. 12.0.0-rc0000
1. Sandra Russo is an account manager at M&K Merchants Bank, where she consults with businesses of all sizes to help them be successful and profitable. Sandra is using an Excel workbook to analyze the financial performance of Home Chef, the market leader in producing midrange kitchen utensils. She asks for your help in creating PivotTables and PivotCharts to provide an overview of Home Chef’s customers, regional sales, pricing, and product line.
Go to the Orders worksheet, which contains a table of data named Orders.
Add a new order as the last record in the Orders table using the data shown in Table 1.
0/5
Add a record to a table.
In the Orders worksheet, the range A4:J31 should be formatted as a table.
2. Sandra wants to create a separate table displaying the products each customer ordered and the total price.
To provide this information, create and format a PivotTable as follows:
a. Insert a PivotTable on a new worksheet based on the Orders table.
b. Use Orders and Price Pivot as the name of the worksheet.
c. Add the Customer field to the Rows area of the PivotTable.
d. Add the Product field to the Columns area of the PivotTable.
e. Add the Total field to the Values area, where it appears as Sum of Total.
f. Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 0 decimal places and the $ symbol.
3/5
Create a PivotTable.
Add rows to a PivotTable.
Add fields as columns in a PivotTable.
In the Orders and Price Pivot worksheet, the Product field should be added to the Columns area of the PivotTable.
Add values to a PivotTable.
Modify the number format of a PivotTable field.
In the PivotTable on the Orders and Price Pivot worksheet, the Sum of Total field in the Values section should be formatted using the Accounting number format with zero decimal places and the $ symbol.
3. Change the appearance of the PivotTable as follows to make it more attractive:
a. Apply Dark Teal, Pivot Style Dark 4 (1st row, 4th column in the PivotTable Styles gallery) to the PivotTable to use a design similar to the one applied to other PivotTables in the workbook.
b. Hide the field headers in the PivotTable to make it more compact.
0/5
Apply a PivotTable style.
In the Orders and Price Pivot worksheet, the PivotTable should be formatted using the Dark Teal, Pivot Style Dark 4 PivotTable style.
Hide field headers in a PivotTable.
In the Orders and Price Pivot worksheet, the PivotTable should be formatted to hide field headers.
4. Return to the Orders worksheet. Sandra wants to display the order totals by region. Insert a recommended PivotTable based on the Orders table as follows:
a. Insert the Sum of Total by Region recommended PivotTable.
b. Use Totals by Region Pivot as the name of the new worksheet.
c. Change the number format of the Sum of Total field to Accounting with 2 decimal places and the $ symbol.
0/5
Create a PivotTable.
In the Totals by Region Pivot worksheet, the Sum of Total by Region recommended PivotTable has not been added.
Modify the number format of a PivotTable field.
In the PivotTable on the Totals by Region Pivot worksheet, the Total field in the Values section should be formatted using the Accounting number format with two decimal places and the $ symbol.
5. Sandra decides a chart would help to analyze the order data. On the Totals by Region Pivot worksheet, insert a Stacked Column PivotChart. Resize and position the PivotChart so that its upper-left corner is within cell D3 and its lower-right corner is within cell K17. 0/5
Create a PivotChart.
The Totals by Region Pivot worksheet should contain a stacked column PivotChart based on the PivotTable beginning in cell A3.
Resize a PivotChart.
The Totals by Region Pivot worksheet should contain a stacked column PivotChart based on the PivotTable beginning in cell A3.
6. Home Chef sales representatives earn a 10 percent dividend on each order. Add the dividend data to the PivotChart as follows so that Sandra can compare the totals and dividends:
a. Add a calculated field to the PivotChart.
b. Use Dividends as the name of the calculated field (Hint: The Dividends field will appear as Sum of Dividends).
c. The formula should multiply the Total field value by 0.1 to calculate the commission amount.
0/5
Add a calculated field to a PivotTable.
In the Totals by Region Pivot worksheet, the PivotTable should contain a calculated field that multiplies the Total field value by 0.1.
7. Go to the Products worksheet, which contains a PivotTable named Products. Modify the Products PivotTable so that it is easier to interpret as follows:
a. Remove the Customer field from the Rows area to focus on product and region data.
b. Change the Report Layout of the PivotTable to Compact Form.
c. Turn off the Grand Totals for rows and columns.
d. Show all Subtotals at the top of the group.
3/5
Add rows to a PivotTable.
Change the layout of a PivotTable.
Remove Grand Totals from a PivotTable layout.
In the Products worksheet, the PivotTable should be formatted to hide Grand Totals for rows and columns.
Update the layout of a PivotTable with subtotals.
In the Products worksheet, the PivotTable should display subtotals at the top of the group.
8. Refresh the data source so that it displays accurate data, including the new record you added to the Orders table. 5/5
Refresh Pivot Table data.
9. Sandra wants to display the average discount for each product rather than the sum of discount values in the Products PivotTable.
Modify the Products PivotTable as follows:
a. Change the Value Field Settings for the Sum of Discount values to determine the Average discounts.
b. Use Average Discount as the custom field name, which appears as the column heading.
c. Apply the Percentage number format with 1 decimal place.
2/6
Summarize field values in a PivotTable.
In the Products worksheet, the Discount field in the Values area of the PivotTable should be summarized using the Average function.
Update the name of a PivotTable field.
Modify the number format of a PivotTable field.
In the Products worksheet, the Average Discount field in the Values section of the PivotTable should be formatted using the Percentage number format with one decimal place.
10. Sandra wants to change the format of Average Discounted Price values, which are difficult to interpret.
Modify the Average Discounted Price values by applying the Currency number format with 2 decimal places and the $ symbol so that these values are easier to understand.
0/6
Modify the number format of a PivotTable field.
In the Products worksheet, the Average Discounted Price field in the Values section of the PivotTable should be formatted using the Currency number format with two decimal places and the $ symbol.
11. Go to the Monthly Sales worksheet, which contains a PivotTable named MonthlySales. Sandra wants to provide another way to visualize this data.
Create a PivotChart as follows:
a. Create a Clustered Column PivotChart based on the MonthlySales PivotTable.
b. Move and resize the PivotChart so that its upper-left corner is within cell A20 and its lower-right corner is within cell G38.
c. Add the title Monthly Sales to the PivotChart in the above position.
d. Apply Layout 3 to the PivotChart to display the legend at the bottom of the chart area.
0/6
Create a PivotChart.
The workbook should contain a worksheet named “Monthly Sales”.
Resize a PivotChart.
The workbook should contain a worksheet named “Monthly Sales”.
Add a title to a PivotChart.
The workbook should contain a worksheet named “Monthly Sales”.
Change the layout of a PivotChart.
The workbook should contain a worksheet named “Monthly Sales”.
12. Add a slicer to the PivotChart as follows to make it easy for Sandra to filter the data:
a. Add a slicer to the PivotChart based on the Region field.
b. Position the slicer so that its upper-left corner is within cell H20 and its lower-right corner is within cell J33.
0/6
Add a slicer to a PivotChart.
The workbook should contain a worksheet named “Monthly Sales”.
Resize a slicer.
The workbook should contain a worksheet named “Monthly Sales”.
13. Sandra thinks the Midwest quantity amount for September seems high. Examine the details of this amount as follows:
a. Drill down into the Midwest quantity amount for September to display the details of the sales on a new worksheet. (Hint: Double-click the amount.)
b. Use Sep Midwest as the name of the new worksheet.
0/6
Drill down into a PivotTable.
The workbook should contain a worksheet named “Monthly Sales”.
14. Go to the Region PivotChart worksheet, which shows the May product orders in the Pacific region based on the data in the Regions worksheet. Sandra wants to compare all the product order amounts, not just those for May.
Modify and format the PivotChart as follows to show all months:
a. Refresh the PivotChart to make sure it contains updated order information.
b. Change the PivotChart filter to compare data for all months. [Mac Hint: Deselect the date filter to perform this step.]
c. Apply Style 10 to the PivotChart to display the data more clearly.
2/6
Refresh PivotChart data.
Filter a PivotChart.
In the Region PivotChart worksheet, the PivotChart should be filtered to show data for all months using the Date field.
Change the PivotChart style.
In the Region PivotChart worksheet, the clustered column PivotChart should be formatted using the Style 10 chart style.
15. Go to the Discounted Price worksheet. Sandra wants this PivotTable to focus on customers, not order numbers.
Reorder the fields in the Rows area so that the PivotTable displays data first by Customer and then by Order Number.
0/6
Reorder rows in a PivotTable.
In the Discounted Price worksheet, the Customer field should be positioned before the Order Number field in the Rows area of the PivotTable.
16. Sandra is interested in the performance of the Pacific region during the months of September and October, which were the months of the most sales for kitchen utensils.
Filter the PivotTable as follows to show the data that Sandra requests:
a. Use the Region slicer to filter the PivotTable and display Pacific region orders only.
b. Add a second slicer based on the Date field.
c. Move the Date slicer below the Region slicer so that its upper-left corner is within cell J18 and the lower-right corner is within cell K28.
d. Use the Date slicer to filter the PivotTable and display September and October orders only.
2/6
Filter a PivotTable using a slicer.
Add a slicer to a PivotTable.
In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable.
Resize a slicer.
In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable.
Filter a PivotTable using a slicer.
In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable.
17. Go to the Product Pricing worksheet. For each product and customer, Sandra wants to compare the unit price with the average discounted price, starting with products that have the most orders. She also wants to display the product pricing data by month.
To provide this information, modify the Product Pricing PivotTable as follows:
a. Sort the PivotTable based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom.
b. Add the Discounted Price field to the bottom of the Values area of the PivotTable.
c. Change the Value Field Settings for the Sum of Discounted Price values to determine the Average discounted prices.
d. Change the number format of this field to Accounting with 2 decimal places and the $ symbol.
e. Add the Date field to the Filters area of the PivotTable.
f. Filter the Product Pricing PivotTable to display January order data only.
1/6
Sort a PivotTable.
In the Product Pricing worksheet, the PivotTable beginning in cell A5 should be sorted from largest to smallest and top to bottom.
Add values to a PivotTable.
Summarize field values in a PivotTable.
In the Product Pricing worksheet, the Discounted Price field in the Values area of the PivotTable should be summarized using the Average function.
Modify the number format of a PivotTable field.
In the Product Pricing worksheet, the Discounted Price field in the Values area of the PivotTable should be formatted using the Accounting number format with two decimal places.
Add a filter to a PivotTable.
In the Product Pricing worksheet, the Date field should be added to the Filters area of the PivotTable.
Filter a PivotTable using a filter.
In the Product Pricing worksheet, the Date field should be added to the Filters area of the PivotTable.
18. Return to the Orders worksheet. Sandra wants to display the quantity of the orders in the Southeast region.
Complete the Quantity per Region section as follows:
a. In cell M8, enter a formula that uses the GETPIVOTDATA function.
b. Using “Quantity” as the data field, extract data from the PivotTable starting in cell A5 on the Monthly Sales worksheet. Use an absolute reference to the cell.
c. Select the grand total quantity amount for the Southeast region by using “Region” as the field1 argument and “Southeast” as the item1 argument.
0/6
Create a formula using a function.
In the Orders worksheet, the formula in cell M8 should use the text “Quantity” as the data_field argument.

Documentation

Illustrated Excel 2019 | Module 8: SAM Project 1a
M&K Merchants Bank
ANALYZE DATA WITH PIVOTTABLES
Author: Ruby Rawls
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Orders and Price Pivot


Grading Engine: Grading Error:
Step 11: The workbook should contain a worksheet named “Monthly Sales”.
Step 11: The workbook should contain a worksheet named “Monthly Sales”.
Step 11: The workbook should contain a worksheet named “Monthly Sales”.
Step 11: The workbook should contain a worksheet named “Monthly Sales”.
Step 12: The workbook should contain a worksheet named “Monthly Sales”.
Step 12: The workbook should contain a worksheet named “Monthly Sales”.
Step 13: The workbook should contain a worksheet named “Monthly Sales”.
Customer Product Sum of Total
CS1 Ladles $ 329.18
Wooden Spoons $ 166.50
CS1 Total $ 495.68
CS10 Tongs $ 151.20
CS10 Total $ 151.20
CS11 Skimmers $ 192.56
CS11 Total $ 192.56
CS12 Wooden Spoons $ 667.85
CS12 Total $ 667.85
CS2 Skimmers $ 566.33
Spatulas $ 115.63
Tongs $ 168.00
CS2 Total $ 849.96
CS3 $ 432.00
CS4 Ladles $ 259.88
Skimmers $ 177.75
Wooden Spoons $ 249.75
CS4 Total $ 687.38
CS5 Skimmers $ 94.80
Spatulas $ 320.63
Whisks $ 112.80
CS5 Total $ 528.23
CS6 Spatulas $ 59.38
Tongs $ 249.38
CS6 Total $ 308.75
CS7 Skimmers $ 281.44
Spatulas $ 118.75
CS7 Total $ 400.19
CS8 Spatulas $ 182.81
CS8 Total $ 182.81
CS9 Tongs $ 34.13
CS9 Total $ 34.13
CS14 Whisks $ 112.80
CS14 Total $ 112.80
Grand Total $ 5,043.52

Totals by Region Pivot

Sum of Total dividends
$ 5,043.52 $ 504.35

Total Total 5043.5187500000002

Orders

Cooking Utensil Orders
Customer
Grading Engine: Grading Error:
Step 1: In the Orders worksheet, the range A4:J31 should be formatted as a table.
Region Order Number Date Product Quantity Unit Price Discount Discounted Price Total Quantity per Region
CS1 Pacific 1101 1/1/21 Ladles 13 $19.25 5.0% $18.29 $237.74 Midwest ERROR:#REF!
CS5 Pacific 1742 1/10/21 Spatulas 15 $12.50 5.0% $11.88 $178.13 Atlantic ERROR:#REF!
CS2 Atlantic 1123 2/1/21 Skimmers 16 $19.75 7.5% $18.27 $292.30 Pacific ERROR:#REF!
CS2 Atlantic 1764 2/14/21 Spatulas 10 $12.50 7.5% $11.56 $115.63 Southeast ERROR:#REF!
Grading Engine: Grading Error:
Step 18: In the Orders worksheet, the formula in cell M8 should use the text “Quantity” as the data_field argument.
CS3 Southeast 1145 3/1/21 Spatulas 15 $12.50 4.0% $12.00 $180.00 Southwest 45
CS7 Southwest 1786 3/15/21 Skimmers 15 $19.75 5.0% $18.76 $281.44
CS4 Midwest 1167 4/3/21 Skimmers 10 $19.75 10.0% $17.78 $177.75
CS12 Midwest 1308 4/7/21 Wooden Spoons 18 $18.50 5.0% $17.58 $316.35
CS5 Pacific 1189 5/6/21 Whisks 10 $11.75 4.0% $11.28 $112.80
CS5 Pacific 1320 5/19/21 Skimmers 5 $19.75 4.0% $18.96 $94.80
CS6 Atlantic 1102 5/20/21 Spatulas 5 $12.50 5.0% $11.88 $59.38
CS6 Atlantic 1353 5/24/21 Tongs 15 $17.50 5.0% $16.63 $249.38
CS9 Atlantic 1344 7/2/21 Tongs 2 $17.50 2.5% $17.06 $34.13
CS10 Midwest 1365 7/5/21 Tongs 9 $17.50 4.0% $16.80 $151.20
CS7 Southeast 1156 7/18/21 Spatulas 10 $12.50 5.0% $11.88 $118.75
CS3 Southwest 1377 8/2/21 Tongs 15 $17.50 4.0% $16.80 $252.00
CS4 Midwest 1148 9/1/21 Ladles 15 $19.25 10.0% $17.33 $259.88
CS12 Midwest 1399 9/10/21 Wooden Spoons 20 $18.50 5.0% $17.58 $351.50
CS4 Southwest 1380 9/20/21 Wooden Spoons 15 $18.50 10.0% $16.65 $249.75
CS1 Pacific 1161 9/25/21 Ladles 5 $19.25 5.0% $18.29 $91.44
CS2 Atlantic 1182 10/17/21 Skimmers 15 $19.75 7.5% $18.27 $274.03
CS1 Pacific 1402 10/18/21 Wooden Spoons 10 $18.50 10.0% $16.65 $166.50
CS2 Atlantic 1403 10/19/21 Tongs 10 $17.50 4.0% $16.80 $168.00
CS5 Pacific 1404 10/20/21 Spatulas 12 $12.50 5.0% $11.88 $142.50
CS14 Pacific 1405 10/21/21 Whisks 10 $11.75 4.0% $11.28 $112.80
CS11 Southeast 1203 11/12/21 Skimmers 10 $19.75 2.5% $19.26 $192.56
CS8 Midwest 1524 12/16/21 Spatulas 15 $12.50 2.5% $12.19 $182.81

=GETPIVOTDATA(“Quantity”,’Sep Midwest ‘!$A$5,”Region”,”Southwest”)

Products

Row Labels Average Quantity Average Discount Average Discounted Price
Ladles
Pacific 9 10.00% $ 18.29
Midwest 15 10.00% $ 17.33
Spatulas
Midwest 15 2.50% $ 12.19
Southeast 13 9.00% $ 11.94
Pacific 14 10.00% $ 11.88
Atlantic 8 12.50% $ 11.72
Skimmers
Southeast 10 2.50% $ 19.26
Pacific 5 4.00% $ 18.96
Southwest 15 5.00% $ 18.76
Atlantic 16 15.00% $ 18.27
Midwest 10 10.00% $ 17.78
Wooden Spoons
Midwest 19 10.00% $ 17.58
Pacific 10 10.00% $ 16.65
Southwest 15 10.00% $ 16.65
Whisks
Pacific 10 8.00% $ 11.28
Tongs
Atlantic 9 11.50% $ 16.83
Midwest 9 4.00% $ 16.80
Southwest 15 4.00% $ 16.80
Grand Total 12 148.00% $ 15.69

Sep Midwest

Sum of Quantity
Southwest Grand Total
Mar 15 15
Aug 15 15
Sep 15 15
Grand Total 45 45

Southwest Mar Aug Sep 15 15 15

Monthly Sales

Region PivotChart

Total Ladles Spatulas Wooden Spoons Whisks Pacific 91.4375 142.5 166.5 112.8

Regions

Date (Multiple Items)
Total Order Amounts
Pacific $ 513.24
Ladles $ 91.44
Spatulas $ 142.50
Wooden Spoons $ 166.50
Whisks $ 112.80
Grand Total $ 513.24

Discounted Price

Average of Discounted Price Column Labels
Row Labels Ladles Spatulas Skimmers Wooden Spoons Whisks Grand Total
1742 11.88 11.88
CS5 11.88 11.88
1405 11.28 11.28
CS14 11.28 11.28
1404 11.88 11.88
CS5 11.88 11.88
1402 16.65 16.65
CS1 16.65 16.65
1320 18.96 18.96
CS5 18.96 18.96
1189 11.28 11.28
CS5 11.28 11.28
1161 18.29 18.29
CS1 18.29 18.29
1101 18.29 18.29
CS1 18.29 18.29
Grand Total 18.29 11.88 18.96 16.65 11.28 14.81

Product Pricing

Product Pricing PivotTable
Row Labels Sum of Quantity Average of Unit Price Sum of Discounted Price
Ladles
Grading Engine: Grading Error:
Step 17: In the Product Pricing worksheet, the PivotTable beginning in cell A5 should be sorted from largest to smallest and top to bottom.
13 $ 19.25 $18.29
CS1 13 $ 19.25 $18.29
Jan 13 $ 19.25 $18.29
Spatulas 15 $ 12.50 $11.88
CS5 15 $ 12.50 $11.88
Jan 15 $ 12.50 $11.88
Grand Total 28 $ 15.88 $30.16

Excel

Documentation

Illustrated Excel 2019 | Module 7: SAM Project 1a
Chastain Group
EXCHANGE DATA WITH OTHER PROGRAMS
Author: Ruby Rawls
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Merger Options

Merger Options

Hola

BrandUp

D&M

Revenue Comparison

365 Marketing D&M Group
Revenues 2017 2018 2019 2020 2021 Revenues 2017 2018 2019 2020 2021
Q1 $ 68,000 $ 80,400 $ 81,750 $ 91,050 $ 94,000 Q1 $ 41,000 $ 53,300 $ 54,050 $ 64,150 $ 63,900
Q2 77,800 81,750 90,500 95,500 95,500 Q2 50,200 50,750 63,600 68,100 67,800
Q3 83,575 90,675 93,750 100,500 99,350 Q3 56,275 56,675 62,750 73,200 70,150
Q4 85,850 90,750 91,775 100,500 102,500 Q4 63,150 61,075 63,675 78,800 77,300
Total $ 319,225 $ 344,575 $ 363,775 $ 392,550 $ 401,350 Total $ 210,625 $ 221,800 $ 244,075 $ 284,250 $ 279,150
BrandUp Hola
Revenues 2017 2018 2019 2020 2021 Revenues 2017 2018 2019 2020 2021
Q1 $ 53,000 $ 75,400 $ 76,750 $ 80,050 $ 82,300 Q1 $ 38,500 $ 38,400 $ 39,950 $ 41,020 $ 44,000
Q2 68,700 70,750 80,100 89,200 81,200 Q2 47,220 47,750 48,500 48,750 48,800
Q3 68,575 70,675 73,750 77,820 70,950 Q3 50,175 50,675 53,750 50,775 51,350
Q4 68,850 71,750 77,715 79,300 82,500 Q4 59,850 60,050 60,775 60,990 82,500
Total $ 259,125 $ 288,575 $ 308,315 $ 326,370 $ 316,950 Total $ 195,745 $ 196,875 $ 202,975 $ 201,535 $ 226,650

Excel

Grader – Instructions Excel 2016 Project

GO16_XL_CH05_GRADER_5E_HW – Sports Schedule 1.1

Project Description:

In this project, you will manage a large worksheet and work on two workbooks at the same time.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download, save, and open the workbook named go16_xl_ch05_grader_5e_hw.xlsx. Save it in your Excel Chapter 5 folder as Lastname_Firstname_5E_Sports_Schedule.

0

2

Go to cell M82, and then insert a COUNTIF function to count the number of Staff referees. In cell K82, type Events with Unassigned Referees.

10

3

Move to cell A1, and then open downloaded the file e05E_Referee_Requests.xlsx. Switch windows, and then make your Lastname_Firstname_5E_Sports_Schedule.xlsx file the active window. Arrange the two files horizontally.

0

4

In the Lastname_Firstname_5E_Sports_Schedule.xlsx workbook, go to cell A82, split the window horizontally, and then click in any cell above the split in column C. Use Find to locate the first Event # request from the e05E_Referee_Requests.xlsx worksheet, and then type Danny Litowitz in the appropriate cell in the Lastname_Firstname_5E_Sports_Schedule.xlsx workbook to assign him as the Referee.

10

5

Use the Find command to locate the remaining three Event #s listed in the e05E_Referee_Requests.xlsx worksheet, and then enter the appropriate referee for each sports event in column M of your Lastname_Firstname_5E_Sports_Schedule.xlsx worksheet. Close the Find and Replace dialog box, and then close the e05E_Referee_Requests.xlsx workbook. Maximize your Lastname_Firstname_5E_Sports_Schedule.xlsx worksheet, and then remove the Split.

10

6

Insert a new blank row 1. In cell A1, type Schedule of Sports Events with Unassigned Referees and then Merge & Center the title across the range A1:M1. Apply the Title cell style.

10

7

Create a New Table Style named Sports Schedule and format the First Row Stripe with a Fill—in the fifth column, the third color. For the Header Row, in the first column of colors click the second color. Set the style you created as the default table style for this workbook.

10

8

Select the range A2:M81 and apply the Custom table style—Sports Schedule.

10

9

AutoFit columns A:M. Set the Orientation to Landscape, set the Width to 1 page, and center the worksheet Horizontally. Insert a footer in the left section that includes the file name, and in the right section, insert a page number.

10

10

In Page Break Preview, break Page 1 after row 49 to end Page 1 with TENNIS and begin Page 2 with TRACK. Return to Normal view. Set Print Titles to repeat row 2 at the top of each page.

10

11

Insert a Hyperlink in cell J2 to the downloaded e05E_Sports_Coaches.xlsx workbook.

10

12

Edit the ScreenTip to read Click here for contact information and then test your hyperlink. Close the e05E_Sports_Coaches.xlsx workbook.

10

13

Save your Lastname_Firstname_5E_Sports_Schedule.xlsx workbook and then close Excel. Submit the workbook as directed.

0

Total Points

100


Created On: 07/05/2019 1 GO16_XL_CH05_GRADER_5E_HW – Sports Schedule 1.1

Excel

This assigment have to be done in Excel. I have uploaded two documents. The first one are the instructions and the second one is the format with Excel that I have to complete with the instructions mentioned. 

Excel

Documentation

Illustrated Excel 2019 | Module 8: SAM Project 1a
M&K Merchants Bank
ANALYZE DATA WITH PIVOTTABLES
Author: Ruby Rawls
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Orders

Cooking Utensil Orders
Customer Region Order Number Date Product Quantity Unit Price Discount Discounted Price Total Quantity per Region
CS1 Pacific 1101 1/1/21 Ladles 13 $19.25 5.0% $18.29 $237.74 Midwest 87
CS5 Pacific 1742 1/10/21 Spatulas 15 $12.50 5.0% $11.88 $178.13 Atlantic 63
CS2 Atlantic 1123 2/1/21 Skimmers 16 $19.75 7.5% $18.27 $292.30 Pacific 48
CS2 Atlantic 1764 2/14/21 Spatulas 10 $12.50 7.5% $11.56 $115.63 Southeast
CS3 Southeast 1145 3/1/21 Spatulas 15 $12.50 4.0% $12.00 $180.00 Southwest 45
CS7 Southwest 1786 3/15/21 Skimmers 15 $19.75 5.0% $18.76 $281.44
CS4 Midwest 1167 4/3/21 Skimmers 10 $19.75 10.0% $17.78 $177.75
CS12 Midwest 1308 4/7/21 Wooden Spoons 18 $18.50 5.0% $17.58 $316.35
CS5 Pacific 1189 5/6/21 Whisks 10 $11.75 4.0% $11.28 $112.80
CS5 Pacific 1320 5/19/21 Skimmers 5 $19.75 4.0% $18.96 $94.80
CS6 Atlantic 1102 5/20/21 Spatulas 5 $12.50 5.0% $11.88 $59.38
CS6 Atlantic 1353 5/24/21 Tongs 15 $17.50 5.0% $16.63 $249.38
CS9 Atlantic 1344 7/2/21 Tongs 2 $17.50 2.5% $17.06 $34.13
CS10 Midwest 1365 7/5/21 Tongs 9 $17.50 4.0% $16.80 $151.20
CS7 Southeast 1156 7/18/21 Spatulas 10 $12.50 5.0% $11.88 $118.75
CS3 Southwest 1377 8/2/21 Tongs 15 $17.50 4.0% $16.80 $252.00
CS4 Midwest 1148 9/1/21 Ladles 15 $19.25 10.0% $17.33 $259.88
CS12 Midwest 1399 9/10/21 Wooden Spoons 20 $18.50 5.0% $17.58 $351.50
CS4 Southwest 1380 9/20/21 Wooden Spoons 15 $18.50 10.0% $16.65 $249.75
CS1 Pacific 1161 9/25/21 Ladles 5 $19.25 5.0% $18.29 $91.44
CS2 Atlantic 1182 10/17/21 Skimmers 15 $19.75 7.5% $18.27 $274.03
CS1 Pacific 1402 10/18/21 Wooden Spoons 10 $18.50 10.0% $16.65 $166.50
CS2 Atlantic 1403 10/19/21 Tongs 10 $17.50 4.0% $16.80 $168.00
CS5 Pacific 1404 10/20/21 Spatulas 12 $12.50 5.0% $11.88 $142.50
CS14 Pacific 1405 10/21/21 Whisks 10 $11.75 4.0% $11.28 $112.80
CS11 Southeast 1203 11/12/21 Skimmers 10 $19.75 2.5% $19.26 $192.56
CS8 Midwest 1524 12/16/21 Spatulas 15 $12.50 2.5% $12.19 $182.81

Products

Product Region Customer Average Quantity Sum of Discount Average Discounted Price
Ladles Midwest CS4 15 0.1 17.325
Pacific CS1 9 0.1 18.2875
Spatulas Midwest CS8 15 0.025 12.1875
Southeast CS3 15 0.04 12
CS7 10 0.05 11.875
Pacific CS5 15 0.05 11.875
Atlantic CS2 10 0.075 11.5625
CS6 5 0.05 11.875
Skimmers Midwest CS4 10 0.1 17.775
Southwest CS7 15 0.05 18.7625
Southeast CS11 10 0.025 19.25625
Pacific CS5 5 0.04 18.96
Atlantic CS2 16 0.15 18.26875
Wooden Spoons Midwest CS12 19 0.1 17.575
Southwest CS4 15 0.1 16.65
Whisks Pacific CS5 10 0.04 11.28
Tongs Midwest CS10 9 0.04 16.8
Southwest CS3 15 0.04 16.8
Atlantic CS6 15 0.05 16.625
CS9 2 0.025 17.0625
Grand Total 12 1.25 15.9536413043

Monthly Sales

Sum of Quantity
Midwest Southwest Southeast Pacific Atlantic Grand Total
Jan 28 28
Feb 26 26
Mar 15 15 30
Apr 28 28
May 15 15
Jun 9 22 31
Jul 15 10 25
Aug 35 15 50
Sep 5 5
Oct 15 15
Nov 10 10
Dec 15 15
Grand Total 87 45 35 48 63 278

Region PivotChart

Product Orders per Region

Total

Skimmers Whisks Pacific 94.800000000000011 112.8

Regions

Date May
Total Order Amounts
Pacific $ 207.60
Skimmers $ 94.80
Whisks $ 112.80
Grand Total $ 207.60

Discounted Price

Average of Discounted Price Column Labels
Row Labels Ladles Spatulas Skimmers Wooden Spoons Whisks Tongs Grand Total
1101 18.29 18.29
CS1 18.29 18.29
1742 11.88 11.88
CS5 11.88 11.88
1123 18.27 18.27
CS2 18.27 18.27
1764 11.56 11.56
CS2 11.56 11.56
1145 12.00 12.00
CS3 12.00 12.00
1786 18.76 18.76
CS7 18.76 18.76
1167 17.78 17.78
CS4 17.78 17.78
1308 17.58 17.58
CS12 17.58 17.58
1189 11.28 11.28
CS5 11.28 11.28
1320 18.96 18.96
CS5 18.96 18.96
1102 11.88 11.88
CS6 11.88 11.88
1353 16.63 16.63
CS6 16.63 16.63
1344 17.06 17.06
CS9 17.06 17.06
1365 16.80 16.80
CS10 16.80 16.80
1156 11.88 11.88
CS7 11.88 11.88
1377 16.80 16.80
CS3 16.80 16.80
1148 17.33 17.33
CS4 17.33 17.33
1399 17.58 17.58
CS12 17.58 17.58
1380 16.65 16.65
CS4 16.65 16.65
1161 18.29 18.29
CS1 18.29 18.29
1182 18.27 18.27
CS2 18.27 18.27
1203 19.26 19.26
CS11 19.26 19.26
1524 12.19 12.19
CS8 12.19 12.19
Grand Total 17.97 11.90 18.55 17.27 11.28 16.82 15.95

Product Pricing

Product Pricing PivotTable
Row Labels Sum of Quantity Average of Unit Price
Ladles 33 $ 19.25
CS1 18 $ 19.25
CS4 15 $ 19.25
Spatulas 70 $ 12.50
CS5 15 $ 12.50
CS2 10 $ 12.50
CS3 15 $ 12.50
CS7 10 $ 12.50
CS6 5 $ 12.50
CS8 15 $ 12.50
Skimmers 71 $ 19.75
CS5 5 $ 19.75
CS2 31 $ 19.75
CS7 15 $ 19.75
CS4 10 $ 19.75
CS11 10 $ 19.75
Wooden Spoons 53 $ 18.50
CS4 15 $ 18.50
CS12 38 $ 18.50
Whisks 10 $ 11.75
CS5 10 $ 11.75
Tongs 41 $ 17.50
CS3 15 $ 17.50
CS6 15 $ 17.50
CS9 2 $ 17.50
CS10 9 $ 17.50
Grand Total 278 $ 16.89

Excel

Grader – Instructions Excel 2016 Project

GO16_XL_CH06_GRADER_6E_HW – Hires 1.2

Project Description:

In the following project, you will assist Linda Wong, Employer Relations Manager, in tracking the number of people who get hired by an employer at each fair. You will create and modify a chart to display the number of people hired at the fairs in the past five years, create a diagram of the communities served, and create an organizational chart for staff at the Job Fair.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the Excel workbook named go16_xl_ch06_grader_6e_hw.xlsx.

0

2

Using the data in the range B4:F10, insert Sparklines using the Line format. Place the sparklines in the range adjacent to the 2016 column, show the High Point and Last Point, and then apply Sparkline Style Accent 4, Darker 25%. Note, depending on the version of Office you are using, the style name may be Dark Purple, Sparkline Style Accent 4, Darker 25%.

10

3

Type Trend in the cell above the sparklines.

3

4

Using the data for the years and for each location (not the totals) and the Recommended Charts command, create a Clustered Column chart on a separate chart sheet named Hires by Location Chart.

8

5

From the Change Chart Type dialog box, on the All Charts tab, if necessary, on the left click Column, and then on the right, at the top of the dialog box, click the fourth chart icon—3-D Clustered Column, and then click OK.

8

6

Apply Chart Style 5 to the chart, and add a chart title above the chart with the text Job Hires by Location. Set the title’s font size to 28.

6

7

Format the Chart Area with a solid fill using Olive Green, Accent 3, Lighter 80%. Format the Plot Area with a solid fill two shades darker—Olive Green, Accent 3, Lighter 40%.

6

8

Format the floor and the side wall with a solid color using Aqua, Accent 5, Darker 50% and 60% transparency. Add the chart element Axis Title to the vertical axis with the text Number of Attendees Hired and change the font size to 16.

7

9

On the Job Hires by Location worksheet, using the data for Dallas, insert a Line with Markers line chart. Resize the chart to position the upper left corner within cell A13 and the lower right corner within cell G26.

10

10

Change the chart title to Successful Job Hires in Dallas and set the title’s font size to 16. Format the Vertical (Value) Axis so that the Minimum value is 400 and the Major unit is 25.

6

11

Add a Linear Trendline. Format the Line Color of the trendline with Orange, Accent 6 and set the Width of the line to 2 pt.

6

12

Insert a new sheet and name it List Chart. In cell A1, type Three Largest Communities We Serve

Merge and center this title across the range A1:G1 and apply the Title cell style.

5

13

Insert a SmartArt graphic using the Vertical Box List. In the three boxes, type, in order, Dallas, Fort Worth, and Carrollton.

3

14

Resize the graphic to position the upper left corner in cell A3 and the lower right corner in cell G16. Apply the Inset style and change the colors to Colorful Range – Accent Colors 4 to 5.

2

15

Insert a new sheet and name it Organization Chart. In cell A1, type Job Fair Booth Installation Services Structure

Merge and center this title across the range A1:H1 and apply the Title cell style.

7

16

Insert a SmartArt graphic using Horizontal Multi-Level Hierarchy. In the vertical box, type Booth Manager and in the three remaining boxes, type Safety Inspectors, Electricians, and Carpenters. Resize the graphic to position the upper left corner within cell A4 and the lower right corner within cell H16.

2

17

Apply the Subtle Effect style and change the colors to Colorful Range – Accent Colors 4 to 5.

3

18

Display the Hires by Location chart sheet. From the Page Layout tab, use the Dialog Launcher to display the Page Setup dialog box. Insert a Custom Footer with the File Name field in the left section.

4

19

Display the Job Hires by Location sheet. Hold down CTRL and select the remaining two worksheets to group the three sheets. Insert a Custom Footer with the File Name field in the left section. Center the sheets horizontally.

4

20

Ungroup the worksheets. Ensure the worksheets are in this order: Hires by Location (chart), Job Hires by Location, List Chart, Organization Chart. Close the workbook. Exit Excel. Submit the workbook as directed.

0

Total Points

100


Created On: 07/05/2019 1 GO16_XL_CH06_GRADER_6E_HW – Hires 1.2

excel


Case Study – Tablet Computer Profitability

A tablet computer manufacturer has three models in its product line:

The Mini model costs $375 to produce and sells for $499. 60,000 were sold.

The Standard model costs $390 to produce and sells for $699. 30,000 were sold.

The Pro model costs $435 to produce and sells for $899. 15,000 were sold.

The company plans to spend $300,000 to advertise the Mini, $500,000 to advertise the Standard, and $400,000 to advertise the Pro. These costs will be incurred regardless of how many units are sold.

Using Excel, put the above information into a spreadsheet, and develop a financial model which does the following:

Using selling prices and production costs, calculates per-unit gross margin (profit) dollars and percent of selling price.

Uses selling prices and units sold to calculate total revenue from each model and the total for all three models

Calculates total production costs from each model and the total for all three models

Calculates gross profit of each model and the total for all three models

Calculates net profit of each model and the total for all three models by deducting advertising expenses

Displays the net profit of the three models in a chart which conveys the relative profit contribution of each model.