Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the file named Student_MOS19_XL_Expert_PROJ4_Charts.xlsx. Save the file as Last_First_MOS19_XL_Expert_PROJ4_Charts...

1 answer below ยป


Steps to Perform:




















































































Step




Instructions




Points Possible



1



Start Excel. Download and open the file named
Student_MOS19_XL_Expert_PROJ4_Charts.xlsx.
Save the file as
Last_First_MOS19_XL_Expert_PROJ4_Charts.



0



2



On the Summary sheet, add a Moving Average Trendline to the Bookings chart.





4



3



Edit the Sales vs. Bookings chart to show a Combo Column chart and the Bookings data as the secondary axis data.





5



4



Save the Sales vs. Booking chart as a template with the name
Bookings Combo. Navigate to the chart templates and use the Snipping Tool to take a rectangular snip of the My Templates tab and the Bookings combo thumbnail. Copy the snip and paste the snip in A1 of the Template worksheet.





10



5



Use the Fri worksheet to create a PivotTable in a new worksheet. Rename the worksheet
PivotTable. Delete rows 1:2.



4



6



Add the following fields as Rows to the PivotTable; Last, Bookings, Sales, and Location. Revise the PivotTable name to
Bookings.



12



7



Apply Banded Rows and PivotTable Style Light 14 to the PivotTable.



6



8



Insert a Slicer and view the data by Bookings. Move and resize the slicer to the range C1:E21. Apply Slicer Style Dark 2.






8



9



On the Summary sheet, use the data in the range A4:C10 to create a PivotChart on a new worksheet. Rename the sheet
Pivot Chart. Add Day and Bookings to the Axis, and Sales as the Values. Apply the Pivot Chart Style 4 and remove the Legend from the chart. Rename the chart title to
Bookings.



14



10



Use the Thurs worksheet to create a PivotChart & PivotTable. Rename the worksheet
Commission. Add the last name as Rows and Sales as Values. Create a calculated field
Commission
in the table to calculate commission if the sales are greater than or equal to 1500 then the agents receive a commission of 15%. Format the Total Sales data as Accounting.



12



11



Delete empty rows 1:2. Rename A1 as
Agents, B1 as
Total Sales, and C1 as
Total Commission. Change the chart to a Bar chart, Layout 1 and enter the title
Commission. Resize the chart to the range E1:O32. Show data in the chart for Anderson, Burr, and Ericks only.





14



12



On the Group worksheet group the days of the week. Rename the group
Week 50.





5



13



On the Pivot Summary worksheet enter the GETPIVOTDATA function in cell B10 to retrieve the Commission from the table in the Commission worksheet. Use the GETPIVOTDATA function in cell C10 to retrieve the Sales table in the Commission worksheet.





6



14



Ensure the worksheets are in the following order: Pivot Chart, Summary, Group, Wed, Commission, Thurs, PivotTable, Fri, Template, Pivot Summary, Mon, Tues. Save the file and close Excel. Submit the file as directed by the instructor.



0












Total Points




100



mos19xlexpertproj4-charts-11instructions-sansiand.docx renterosmos19xlexpertproj4charts-0t2vwy0k.xlsx
Answered Same DayJul 27, 2021

Answer To: Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the file named...

Mohd answered on Jul 28 2021
152 Votes
PivotChart
    Row Labels    Sum of Sales
    Monday    129425
    322    129425
    Tuesday    253170
    314    253170
    Wednesday    113655
    321    113655
    Thursday    110825
    307    110825
    Friday    195560
    284    195560
    Grand Total    802635
Bookings
Total    
322    314    321    307    284    Monday    Tuesday    Wednesday    Thursday    Friday    129425    253170    113655    110825    195560    
Summary
    Bookings Summary
    Week Ending    -12/14/2020-
    Day    Bookings    Sales
    Monday    322    $ 129,425
    Tuesday    314    253,170
    Wednesday    321    113,655
    Thursday    307    110,825
    Friday    284    195,560
    Total    912    $ 420,040
Bookings
Monday    Tuesday    Wednesday    Thursday    Friday    322    314    321    307    284    
Sales vs. Bookings
Sales    Monday    Tuesday    Wednesday    Thursday    Friday    129425    253170    113655    110825    195560    Bookings    322    314    321    307    284    Day
Sales
Bookings
Group
    Row Labels    Sum of Bookings    Sum of Sales
    Week 50     1548    802635
    Monday    322    129425
    Tuesday    314    253170
    Wednesday    321    113655
    Thursday    307    110825
    Friday    284    195560
    Grand Total    1548    802635
Wed
    Daily Summary
    Wednesday
    Last    First    Bookings    Sales    Location    Comments    Employee #    Phone    Commission?
    Anderson    Tip    8    $ 3,400.00    St. Cloud        9403048    (612) 555-3463
    Beau    Tim    7    $ 2,540.00    Boston        81048830    (218) 555-4055
    Bern    Jules    7    $ 2,450.00    Minneapolis        75661036    (612) 555-4586    Yes
    Blick    Loger    3    $ 1,005.00    Two Harbors        75825555    (218) 555-2345    Yes
    Blings    Sten    4    $ 1,040.00    Aspen Falls        81235205    (612) 555-4958    Yes
    Brown    Joe    2    $ 600.00    Bloomington        20830283    (612) 555-2934
    Boski    Jenifer    2    $ 635.00    Aspen Falls        73501020    (612) 555-3928    Yes
    Brad    Nancy    1    $ 450.00    Mankato        31346341    9548    Yes
    Burr    Arons    0    $ - 0    Rochester    Sick    21349282    2395
    Cheng    Anne    5    $ 1,750.00    Maplewood        23890626    (612) 555-3474    Yes
    Chikov    Toni    13    $ 4,550.00    Edina        2332093    (612) 555-4583    Yes
    Clays    Darla    6    $ 2,100.00    St. Paul        63091818    (612) 555-4096
    Ellor    Nanci    7    $ 2,450.00    Fargo        4593311    (612) 555-9855
    Ericks    Shelby    9    $ 3,150.00    St. Cloud        57349411    (218) 555-9567
    Frick    Luriel    5    $ 1,410.00    Aspen Falls        96333334    (612) 555-3934
    Fuentes    Bernard    2    $ 700.00    St. Paul        50232301    (612) 555-5659    Yes
    Graham    Phil    5    $ 1,750.00    Edina        68235239    (612) 555-9767
    Grimm    Lucille    7    $ 2,450.00    Rochester        58732332    7985    Yes
    Harrys    Jon    5    $ 2,175.00    Blaine        96340342    (612) 555-6735
    Hill    Trent    11    $ 3,850.00    St. Louis Park        36039494    (612) 555-8679
    James    Jen    5    $ 1,750.00    St. Peter        06934949    5967    Yes
    Jordon    James    10    $ 3,500.00    Spring Lake Park        96349693    (612) 555-2346    Yes
    Kaid    Jonathan    10    $ 3,500.00    Brooklyn Center        82392346    (612) 555-4574    Yes
    Kippert    Bryan    1    $ 350.00    Duluth        43234596    (218) 555-8456
    Kulhavy    Marti    11    $ 3,850.00    Minneapolis        46594204    (612) 555-4506    Yes
    Landgo    Lou    0    $ - 0    Fargo    vacation    76304344    (612) 555-4096
    Lindgram    Elsie    11    $ 3,850.00    Bloomington        03969434    (612) 555-2347    Yes
    Lindquist    Shelia    4    $ 1,400.00    Edina        92030303    (612) 555-3456    Yes
    Lyons    Rachel    6    $ 2,100.00    Maplewood        0938233    (612) 555-4564
    Nelson    Wendy    12    $ 4,200.00    Two Harbors        06934484    (218) 555-5687    Yes
    Nordstrom    Terry    9    $ 3,150.00    Duluth        0683034    (218) 555-5409
    Norton    LeAnne    10    $ 3,500.00    St. Paul        96838443    (612) 555-3456    Yes
    Olness    Janelle    3    $ 1,050.00    Fargo        09384209    (612) 555-4592
    Olson    Anne    1    $ 350.00    Brooklyn Center        49850394    (612) 555-3456    Yes
    Parmont    Tim    9    $ 3,150.00    Brainerd        49083634    (612) 555-4534    Yes
    Peterson    Ruth    3    $ 1,050.00    St. Cloud        4894883    (218) 555-7965
    Rodriguez    Maria    0    $ - 0    Hibbing    Sick    7485973    (218) 555-5679    Yes
    Rommero    Antonio    10    $ 3,500.00    Spring Lake Park        95069056    (612) 555-4564
    Samuals    Brian    8    $ 2,800.00    Minneapolis        56878345    (612) 555-4567    Yes
    Smith    John    4    $ 1,400.00    Minneapolis        45983796    (612) 555-2437
    Smith    Richard    1    $ 350.00    Blaine        70953744    (612) 555-4564
    Svens    Lois    4    $ 1,400.00    Mankato        09847551    (612) 555-1833    Yes
    Swanson    Julie    2    $ 700.00    St. Peter        45789561    4075
    Therman    Ruth    10    $ 3,500.00    Bloomington        96394844    (612) 555-4567    Yes
    Thomas    Amy    7    $ 2,450.00    Edina        56454226    (612) 555-2845
    Thompson    Harrison    9    $ 3,150.00    Duluth        03495384    (218) 555-5098    Yes
    Thorgood    Bruce    3    $ 1,050.00    Two Harbors        34527445    (218) 555-3467    Yes
    Timscleft    Nancy    6    $ 2,100.00    Rochester        24545741    8059
    Torkleson    Anna    0    $ - 0    Brainerd    On vacation    84845771    (218) 555-3456
    Trent    Jason    9    $ 3,150.00    Hibbing        39820981    (218) 555-4499    Yes
    Wilson    Roger    5    $ 1,750.00    Brooklyn Center        89468491    (612) 555-3456
    Worgot    Mary    9    $ 3,150.00    Rochester        80954455    (612) 555-4956    Yes
    Wu    Amy    4    $ 1,400.00    St. Paul        37483236    (612) 555-8765    Yes
    Wyatt    Maria    7    $ 2,450.00    Minneapolis        84598657    (612) 555-5678
    Young    Bernice    0    $ - 0    Duluth    Sick    97435456    (218) 555-4303    Yes
    Youngest    Gayle    9    $ 4,150.00    Minneapolis        50983541    (612) 555-6578    Yes
            Daily Total    $ 113,655.00
&D    &F    &A
Commission
    Agents    Total Sales    Total Commission
    Anderson    $ 1,800.00    $ 270.00
    Beau    $ 2,000.00    $ 300.00
    Bern    $ 1,300.00    ERROR:#VALUE!
    Blick    $ 1,005.00    ERROR:#VALUE!
    Blings    $ 1,040.00    ERROR:#VALUE!
    Boski    $ 635.00    ERROR:#VALUE!
    Brad    $ 450.00    ERROR:#VALUE!
    Brown    $ 600.00    ERROR:#VALUE!
    Burr    $ 1,560.00    $ 234.00
    Cheng    $ 1,750.00    $ 262.50
    Chikov    $ 4,550.00    $ 682.50
    Clays    $ 2,100.00    $ 315.00
    Ellor    $ 2,450.00    $ 367.50
    Ericks    $ 3,150.00    $ 472.50
    Frick    $ 1,410.00    ERROR:#VALUE!
    Fuentes    $ 700.00    ERROR:#VALUE!
    Graham    $ 1,750.00    $ 262.50
    Grimm    $ 2,450.00    $ 367.50
    Harrys    $ 2,175.00    $ 326.25
    Hill    $ 3,850.00    $ 577.50
    James    $ 1,750.00    $ 262.50
    Jordon    $ 3,500.00    $ 525.00
    Kaid    $ 3,500.00    $ 525.00
    Kippert    $ 350.00    ERROR:#VALUE!
    Kulhavy    $ 3,850.00    $ 577.50
    Landgo    $ - 0    ERROR:#VALUE!
    Lindgram    $ 3,850.00    $ 577.50
    Lindquist    $ 1,400.00    ERROR:#VALUE!
    Lyons    $ 2,100.00    $ 315.00
    Nelson    $ 4,200.00    $ 630.00
    Nordstrom    $ 3,150.00    $ 472.50
    Norton    $ 3,500.00    $ 525.00
    Olness    $ 1,050.00    ERROR:#VALUE!
    Olson    $ 350.00    ERROR:#VALUE!
    Parmont    $ 3,150.00    $ 472.50
    Peterson    $ 1,050.00    ERROR:#VALUE!
    Rodriguez    $ - 0    ERROR:#VALUE!
    Rommero    $ 3,500.00    $ 525.00
    Samuals    $ 2,800.00    $ 420.00
    Smith    $ 1,750.00    $ 262.50
    Svens    $ 1,400.00    ERROR:#VALUE!
    Swanson    $ 700.00    ERROR:#VALUE!
    Therman    $ 3,500.00    $ 525.00
    Thomas    $ 2,450.00    $ 367.50
    Thompson    $ 3,150.00    $ 472.50
    Thorgood    $ 1,050.00    ERROR:#VALUE!
    Timscleft    $ 2,100.00    $ 315.00
    Torkleson    $ - 0    ERROR:#VALUE!
    Trent    $ 3,150.00    $ 472.50
    Wilson    $ 1,750.00    $ 262.50
    Worgot    $ 3,150.00    $ 472.50
    Wu    $ 1,400.00    ERROR:#VALUE!
    Wyatt    $ 2,350.00    $ 352.50
    Young    $ - 0    ERROR:#VALUE!
    Youngest    $ 3,150.00    $ 472.50
    Grand Total    $ 110,825.00    $ 16,623.75
Total Sales    Anderson    Beau    Bern    Blick    Blings    Boski    Brad    Brown    Burr    Cheng    Chikov    Clays    Ellor    Ericks    Frick    Fuentes    Graham    Grimm    Harrys    Hill    James    Jordon    Kaid    Kippert    Kulhavy    Landgo    Lindgram    Lindquist    Lyons    Nelson    Nordstrom    Norton    Olness    Olson    Parmont    Peterson    Rodriguez    Rommero    Samuals    Smith    Svens    Swanson    Therman    Thomas    Thompson    Thorgood    Timscleft    Torkleson    Trent    Wilson    Worgot    Wu    Wyatt    Young    Youngest    1800    2000    1300    1005    1040    635    450    600    1560    1750    4550    2100    2450    3150    1410    700    1750    2450    2175    3850    1750    3500    3500    350    3850    0    3850    1400    2100    4200    3150    3500    1050    350    3150    1050    0    3500    2800    1750    1400    700    3500    2450    3150    1050    2100    0    3150    1750    3150    1400    2350    0    3150    Total Commission    Anderson    Beau    Bern    Blick    Blings    Boski    Brad    Brown    Burr    Cheng    Chikov    Clays    Ellor    Ericks    Frick    Fuentes    Graham    Grimm    Harrys    Hill    James    Jordon    Kaid    Kippert    Kulhavy    Landgo    Lindgram    Lindquist    Lyons    Nelson    Nordstrom    Norton    Olness    Olson    Parmont    Peterson    Rodriguez    Rommero    Samuals    Smith    Svens    Swanson    Therman    Thomas    Thompson    Thorgood    Timscleft    Torkleson    Trent    Wilson    Worgot    Wu    Wyatt    Young    Youngest    270    300    #N/A    #N/A    #N/A    #N/A    #N/A    #N/A    234    262.5    682.5    315    367.5    472.5    #N/A    #N/A    262.5    367.5    326.25    577.5    262.5    525    525    #N/A    577.5    #N/A    577.5    #N/A    315    630    472.5    525    #N/A    #N/A    472.5    #N/A    #N/A    525    420    262.5    #N/A    #N/A    525    367.5    472.5    #N/A    315    #N/A    472.5    262.5    472.5    #N/A    352.5    #N/A    472.5    
Thurs
    Daily Summary
    Thursday
    Last    First    Bookings    Sales    Location    Comments    Employee #    Phone    Commission?
    Anderson    Tip    3    $ 1,800.00    St. Cloud        9403048    (612) 555-3463
    Beau    Tim    5    $ 2,000.00    Boston        81048830    (218) 555-4055
    Bern    Jules    1    $ 1,300.00    Minneapolis        75661036    (612)...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?