• Home

## Excel

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:

 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.

## 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

 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

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

## 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.

• 2 months ago
• 10

## 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

## 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

## 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

## 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

## 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.

## 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

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 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.
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
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.
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
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!
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
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
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
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

## 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

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:

 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.

## 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
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

## 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.