ADMN 368v1 Ch. 1 SOLUTIONS at April 24

docx

School

University of British Columbia *

*We aren’t endorsed by this school

Course

368

Subject

Industrial Engineering

Date

Apr 3, 2024

Type

docx

Pages

110

Uploaded by mehaksanay

Report
Chapter 1 PivotTables and PivotCharts The course material shows one way to solve each of the challenges. Often, there are many possible approaches. Challenge 1: Washer and Dryer Data Set Part 1 You may view the finished file at Chapter 1 Resources>Completed Files>Washer and Dryer Data Set PART 1 COMPLETE at [. . .].xlsx. Part A 1. Insert the cursor anywhere in the data. Press Control and “T” simultaneously. Select “My table has headers.” Click OK.
2. Ensure the cursor is placed somewhere in the table (1). Select Table Design from the main menu if needed (2). In the Table Name box, insert “SalesData” (3).
3. Click and drag the cursor to select columns D, E, and F (1). Right-click and select Format Cells (2). Format the three columns as Number with zero decimal places (3) and with 1000s separator (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
The following Excel table results.
Part B 1. Right-click on the “a. Sales Data” worksheet tab and select Move or Copy> (move to end). Name the new worksheet “b. Sort by Make.” Name the table “SortByMake” (steps not shown). Select the sort icon in the Make header (1). Then sort all the data from A to Z (2). Ensure that all the makes are selected (3). Click OK (4).
This Excel table shows Make in alphabetical order (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part C 1. As above, right-click on the Sales Data worksheet tab and select Move or Copy> (move to end). Name the new worksheet “c. Sort by 1 st qu.” (not shown). Name the table “SortByQ1” (not shown). Select the sort icon in the 1 st Quarter Sales header (1), then Sort Largest to Smallest (3). Ensure that all data is selected (3). Click OK (4).
The following table results. The highest 1st quarter sales are represented by Amana dryer model 4995E WE.
Part D 1. Right-click on the Sales Data worksheet tab and select Move or Copy> (move to end). Name the new worksheet “d. Sort by Low 2 nd qu.” (not shown). Name the table “SortByQ2” (not shown). Select the sort icon in the Type header (1), then sort from A to Z (2) but only on the washer data (3). Click OK (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
The following Excel table results.
2. Next, select the sort icon in the 2 nd Quarter Sales header (1). Sort all the data from smallest to largest (2). Select all the data (3). Click OK (4).
The following Excel table results. The lowest 2nd quarter sales amount for a washer is $174,304, represented by GE model 12NNEI.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part E 1. Right-click on the Sales Data worksheet tab and select Move or Copy>(move to end). Name the new worksheet “e. ID Error” (not shown). Name the table “IDError” (not shown). Click in the column C cell (1) to highlight the entire column. Select Home (2) >Conditional Formatting (3) >New Rule… (4).
2. In the dialogue box, select “Format only unique or duplicate values” (1). In the Edit the Rule Description section, select “duplicate” in the drop-down menu (2). Click the Format… button (3).
3. In the Format Cells dialogue box, select the Fill tab (1). Select a colour (2). The example uses light green. The sample colour is displayed in the Sample box (red box). Click OK (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Click OK in the New Formatting Rule dialogue box (1).
5. The highlighted duplicates are shown below. There are two Amana washer models listed with the number SD4726. Save your file in a separate workbook.
Challenge 2: Inventory List Part 2 You may view the finished file at Chapter 1 Resources>Completed Files>Inventory List PART 2 COMPLETE at [. . .].xlsx. Part A 1. First, create an Excel table out of the data set in the Inventory List worksheet. To do this, select Insert (1) >Table (2) from the main menu or place the cursor anywhere in the table. Select Insert>Table, or press Control + T. In the dialogue box, insert the table range (3), check “My table has headers” (4), and click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. The following Excel table is created. Rename the table “a. Highest Part.” Name the table “HighPart” (not shown). Hint: If the column data is not fully displayed, select Home>Format>AutoFit Column Width.
2. Click on the sort icon in the “Sell. Price” header (1) and select Sort Largest to Smallest (2). Click OK (3).
3. The Excel table is re-ordered, sorted from highest to lowest cost (column D). The Norco basket part # AA83Y1 is the part with the highest cost: $148.78 (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part B 1. Right-click on the “a. Highest Part” worksheet tab (1). Select “Move or Copy…” (2)and create a new worksheet in the usual manner, named “b. 10 to 100.”
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. In the new worksheet, name the table “CostOrder” (not shown). Click on the Sort icon of the Cost header (1). Select Number Filters (2) >Between… (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. In the dialogue box, select “is greater than or equal to” (1), and then insert “10” (2). Ensure that the “And” button is checked (red box). Select “is less than or equal to” (3), and then insert “100” (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
If needed, use the Sort icon in the Cost column to sort from largest to smallest. The table is sorted in correct order (red box). Save your work. You may view the finished file at Chapter 1 Resources>Completed Files>Inventory List PART 2 COMPLETE at [. . .].xlsx.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 3: Washer and Dryer Data Set Part 2 Open Chapter 1 Resources>Washer and Dryer Data Set Part 2 at [. . .].xlsx. 1. Place the cursor anywhere in the Excel table of the Data Set worksheet (1). Select Insert (2) >PivotTable (3). In the dialogue box, select the table range (if needed, 4 & 5) and insert the data into a new worksheet (6) named “1 st qu. Sales.” Click OK (7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Name the PivotTable “AppSales” (1). If the PivotTable Name box is not visible, select PivotTable Analyze from the main menu (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. In the PivotTable Fields window, click and drag “Make” to the Rows pane (1), “Type” to the Columns pane (2), and “1 st Quarter Sales” to the ∑ Value pane (3). If necessary, change the ∑ Values field setting to Sum by selecting the sort icon in the ∑ values pane (4), and choose Sum (5). Click OK (6).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Click and drag across columns B, C, and D (1), and then right-click and select Format Cells. Select Accounting format (2), zero decimals (3), and currency symbol $ (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. To show Washer results in the first sales total column (B), select the sort icon for the Column Labels header (1). Sort all data from Z to A (2), selecting all data (3). Click OK (4). This sorts the data horizontally in reverse alphabetical order. In other words, Washer is sorted so that it appears first (column B), and Dryer is moved to column C.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. Sort the Grand Total column from highest dollar sales to lowest. To do this, place the cursor in an active cell in column D and right-click (1). Select Sort (2) >Sort Largest to Smallest (3). 7. To convert the report to Tabular form, click anywhere in the PivotTable (1). Select Design (2) >Report Layout (3) >Show in Tabular Form (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8. The required PivotTable is displayed below. This table shows washer and dryer sales, sorted from highest sales to lowest sales. Save your work. You may view the finished file at Chapter 1 Resources>Completed Files>Washer and Dryer Data Set PART 2 COMPLETE at [. . .].xlsx.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 4: Ed’s Overland Adventures Part 1 Open Chapter 1 Resources>Ed’s Overland Adventures PART 1 at [. . .].xlsx. Part A 1. Insert the cursor in the Excel table (1) and press Control + T (2). In the dialogue box, select “My table has headers” (3). Click OK (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Double-click on the Data Set worksheet. Rename it Sales Data (1). Name the table “SalesData” (2) and click on Table Design (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Place the cursor in the table (1). Select Insert (2) >PivotTable (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. In the Create PivotTable dialogue box, select the “Select a table or range” (1) and “New worksheet” buttons (2). Click OK (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. Name the new worksheet “a. Commis. By Office.” Click in the empty PivotTable box to display the PivotTable Fields window (not shown). From the upper pane of the PivotTable Fields window, click and drag “Office” to the Rows pane (1). Click and drag “Date” to the Columns pane (2). Note that the fields “Quarters” and “Years” fields have been added to the top pane and the Columns pane (red boxes). These fields are automatically calculated by Excel when the Date field has been used. Click and drag “Commission” to the ∑ Values pane (3). Ensure that the Value Field Setting (orange box) is set to Sum of Commission.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. If quarters are displayed, press the minus (“-”) icon to the left of the year (1) to collapse the columns (2022 year is illustrated). 7. To format the data, highlight the sales amounts columns B to E. Right-click (1) and choose Format Cells… (2). Choose the Number tab (3), and then choose Currency (4), zero decimal places (5), and Symbol $ (6). Click OK (7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8. Place the cursor in a number cell in the Grand Total column (1) that is not the header or the total. Right-click and select Sort (2) >Sort Largest to Smallest (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
9. Place the cursor anywhere in the PivotTable (1). Select Design (2) >Report Layout (3) >Show in Tabular Form (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
The completed PivotTable is shown below. Save your file. You may view the finished file at Chapter 1 Resources>Completed Files>Ed’s Overland Adventures PART 1 COMPLETE at [. . .].xlsx.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part B 1. Insert the cursor in the Excel table in the Sales Data worksheet (1). Select Insert (2) >PivotTable (3). Ensure that “SalesData” (red box) is the displayed table. Click the “New Worksheet” button (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Name the new worksheet “b. 4 Highest Staff” (1). From the upper pane of the PivotTable Fields window, click and drag “Staff #” into the Rows pane (2) and “Commission” into the ∑ Values pane (3). Ensure the Value Field Setting is set to Sum of Commissions (orange box). Click and drag “Years” into the Columns pane (4). (If the “Years” and “Quarters” fields have not been automatically included in the upper field pane (red box), click and drag the “Date” field into the Columns pane. Fields “Date,” “Quarters,” and “Years” will then appear. Then, remove “Date” and “Quarter” from the Columns pane.) Click on the Row Labels sort icon in column A (5), and then select Value Filters 6) >Top 10…(7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. In the dialogue box, select the Top (1) 4 (2) Items (3) from the “Sum of Commission” column (4) and click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Select the Column Labels sort icon (1) (column B), de-select (Select All) (2), and choose “2024” (3) in the dialogue box. Click OK (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. Insert the cursor in a number (white) cell of column “2024” (1). Right-click and select Sort (2) “Sort Largest to Smallest” (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. Format the PivotTable by clicking anywhere in the PivotTable (1). From the main menu, select Design (2) >Grand Totals (3) >Off for Rows and Columns (4). 7. Format the PivotTable using the Design menu. Select the “2024” column and format numbers as Currency, $ symbol, zero decimal places (not shown). Select Tabular for the report format (not shown). The top four 2024 commission-earners are shown below, sorted from highest to lowest. In descending order, staff numbers 13, 24, 19, and 17 earned the most commissions.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part C 1. Insert the cursor in the Excel table in the Sales Data worksheet (1). Select Insert (2) >PivotTable (3). Ensure that “SalesData” (red box) is the displayed table. Click the “New Worksheet” button (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Rename the new worksheet “c. # of Trips By Office” (1). From the upper pane of the PivotTable Fields window, click and drag “Office” into the Rows pane (2) and “Years” into the Filters pane (3). Click and drag “Commission” into the ∑ Values pane (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Click on the drop-down menu icon next to Sum of Commission in the ∑ Values pane (1). Select Value Field Settings… (2).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. In the dialogue box, select Average as the Value Field Setting (1). The field name is automatically changed to “Average of Commission” (red box). Click OK (2).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. The pivot table calculations are changed accordingly (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. You need a surrogate for number of trips because these data are not explicitly provided. Recall that each trip is billed on a separate invoice and invoices are individually numbered. Therefore, a count of the number of invoices will act as a surrogate for the number of trips. In the PivotTable Fields window, click and drag “Invoice #” into the ∑ Values pane (1). Click on the drop-down menu icon next to “Sum of Invoice #” (2). Select “Count” in the Value Field Setting dialogue box (3). Change the Custom Name in the dialogue box to “# of Trips” (4) so the PivotTable will be more informative. Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
7. Number of trips is now listed in the PivotTable (black box). Now notice the result when the Years field is placed in the Filters pane rather than in the Columns pane (red box). All years’ results are combined in the # of Trips column in the PivotTable (orange box). This is a slightly different presentation mode than used in part C above, when this field was placed in the Columns pane. (You can drag the Years field from the Filters pane into the Columns pane to see the presentation effect if you wish. If you do, return the Years field to the Filters pane.)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8. Format the PivotTable using the Design menu. Set the Average of Commission column to Currency, $ symbol, zero decimal places (steps not shown). To sort only by the year 2024, select the drop-down icon in the (All) header (1). Select 2024 (2). Click OK (3). 9. Data in both the Average Commission and # of Trips columns are restated in the Pivot Table to show only 2024 results.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
10. Sort the Average of Commission column from largest to smallest. To do this, place the cursor in a white cell in column C (1). Right-click and choose Sort (2) >Sort Largest to Smallest (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
11. The final result is shown below.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part D 1. Insert the cursor in the Excel table in the Sales Data worksheet. Select Insert>PivotTable. Choose the New Worksheet button Click OK. Rename the worksheet “d. # Trips By Co.” (steps not shown). From the upper pane of the PivotTable Fields window, click and drag “Adventure Co.” into the Rows pane (1) and “Years” into the Columns pane (3). (Alternatively, and as shown in part C above, you may place “Years” in the Filter pane.) As above, you need a surrogate for the number of trips, as this information is not provided in the data set. Therefore, click and drag “Invoice #” into the ∑ Values pane (2). Then, click on the drop-down menu icon for Count of Invoice # (4) and select Value Field Settings. In the dialogue box, change the Invoice # value field setting to Count (5), and change the custom name to “# of Trips” (6). Click OK (7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. In the PivotTable Fields window, click and drag Tour Price into the ∑ Values pane (1). Ensure that the Value Field Setting for Tour Price is set to Sum (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Notice that the “∑ Values” field name in the Columns pane (red box) is automatically inserted when there is more than one value inserted in the ∑ Values pane. This provides an additional means to quickly sort the data.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Select the drop-down menu (sort icon) of the Column Labels (1). In the dialogue box, de-select (Select All) (2), and then check “2024” (3). Click OK (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. “Sum of Tour Price” and “Total Sum of Tour Price” columns are not necessary. Place the cursor in the PivotTable (1). Select Design (2) >Grand Totals (3) >On for Columns Only (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
6. Place the cursor in the PivotTable (1). Select Design (2) >Report Layout (3) >Show in Tabular Form (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
7. Format the column Sum of Tour Prices as Currency, $ symbol, zero decimals (step not shown). Place the cursor in one of this column’s number cells (1). Right-click and select Sort (2) >Sort Largest to Smallest (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8. The following PivotTable results.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part E 1. Insert the cursor in the Excel table in the Sales Data worksheet. Select Insert>PivotTable. In the dialogue box, click the New Worksheet radio button. Click OK. (steps not shown) Name the worksheet “e. Top 3 Cos.” (steps not shown). From the upper pane of the PivotTable Fields window, click and drag “Adventure Co.” into the Rows pane (1), “Years” into the Column pane (2), and “Tour Price” into the ∑ Values pane (3). Ensure the operator is set to Sum of Tour Price (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Format the PivotTable. In the example below, Tabular report format is used. Numbers are shown in Currency, $ symbols, zero decimal places (steps not shown). The Grand Total column is not necessary. Insert the cursor in the PivotTable (1). Select Design (2) >Grand Totals (3) >On for Columns Only (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Select the top three companies. To do this, click on the Adventure Co. sort icon (1). Right-click. Select Value Filters (2) with (Select all) checked. Select Top 10… (3). 4. In the dialogue box, select the top (1) 3 (2) companies (3) based on the Sum of the Tour Price column (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. Insert the cursor in one of the cells showing 2024 sales numbers (1). Right-click and select Sort (2) >Sort Largest to Smallest (3). 6. The final output is shown below.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part F 1. Insert the cursor in the Excel table in the Sales Data worksheet. Select Insert>PivotTable. In the dialogue box, click the New Worksheet button. Click OK. Name the worksheet “f. Seasonal Effects” (steps not shown). From the upper pane of the PivotTable Fields window, click and drag the “Years” (1) then “Quarters” (2) fields into the Rows pane. Click and drag the “Office” field into the Filters pane (3). Click and drag the “Commission” field into the ∑ Values pane (4). Ensure the operator is set to Sum or Commission (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. If needed, insert the cursor in the PivotTable (1) and select Design (2) >Subtotals (3) >Do Not Show Subtotals (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. If needed, insert the cursor in the table (1), and select Design (2) >Grand Totals> Off for Rows and Columns.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Display the PivotTable in Tabular report form. Highlight column C (Sum of Commission). Right-click and format the column as Currency, $ symbol, zero decimal places (steps not shown). The final output is shown below. Save your work. Analysis : It is difficult to tell whether seasonality affects sales. There is a large decrease from Quarter 1 to 2 in 2022. There appears to be a small increase from Quarters 2 to 3 in 2022 and 2023, but not in 2024. There seems to be no discernible pattern. It seems only that each quarter’s sales are becoming more consistent over the three years.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 5: Washer and Dryer Data Set Part 3 Part A 1. Open the finished Excel file Chapter 1 Resources>Completed Files>Washer and Dryer Data Set PART 2 COMPLETE at [. . .].xlsx. Save your file in a different folder. Open the 1st qu. Sales worksheet (1). Right-click anywhere in the PivotTable shown in the worksheet (2). From the main menu, select Insert (3) >PivotChart (4). In the dialogue box, select Column (5) >Clustered Column (6). Click OK (7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Click on a field button in the PivotChart (1) and select Hide All Field Buttons on Chart (2).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. The final PivotChart shows total first quarter sales of washers and dryers by the four makes. Remember to save your file.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part B the PivotChart presentation could be improved in several ways. The ones below are some suggestions. You can experiment by clicking on the PivotChart (1) and choosing various options under Design (2) >Add Chart Elements (3), or by viewing other types of PivotCharts.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
1. First, total sales by make for the quarter are not shown. These figures are difficult to estimate from the chart. A stacked column chart would present the information more clearly. To do this, select Insert (1) >PivotChart (2) >Column (3), and then select the stacked column icon (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. The chart formatting could be improved by adding a title. From the main menu, select Design (1) >Add Chart Element (2) >Chart Title (3) >Above Chart (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Double-click on “Chart Title” and change the name to “First Quarter Sales By Make.”
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. The y-axis values could be condensed to single digits, and you could add a “Millions” legend. To do this, click on the y-axis numbers (1). In the Format Axis window that appears, select Axis Options (2). Then select the chart icon (3). From the Display units’ drop-down menu, select “Millions” (5). Tick “Show display units label on chart” (6).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
5. The y-axis amounts are now displayed as single digits (red box). 6. The “Axis Title” boxes are extraneous. Right-click on each box (1 & 3) and select Delete (2).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
7. A legend key could be added. To do this, place the cursor in the PivotChart and select Design (1) >Add Chart Element (2) >Data Table (3) >With Legend Keys (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
8. The revised table is shown below. Save your work. Note the weaknesses in the graphics display. It is difficult to determine the relative dollar amounts of washer and dryer sales when these are stacked. A chart showing combined sales dollars but also comparative sales dollars between types is more useful. The numbers at the bottom of the chart help to clarify this distinction.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 6: Ed’s Overland Adventures Part 2 Part A 1. Insert the cursor in the Excel table in worksheet “a. Commis. by Office” (1). Click the cursor anywhere in the PivotTable (2). From the main menu, select Insert (3) >PivotChart (4). In the dialogue box, select Column (5) >Clustered Column (6). Click OK (7).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Format the PivotChart as you see fit. The chart below hides the field buttons. A chart title and data legend have been added. Y-axis data is presented as dollars and in thousands. Data is sorted by Grand Total from highest to lowest in the PivotTable (left to right in the PivotChart). The PivotTable shows commissions by office for 2022, 2023, 2024, and in total for all three years. The PivotChart omits the combined three-year totals. Analysis: Office #5 has earned the highest total sales commissions over the three-year period, per the Pivot Table ($56,085, red box). From the PivotChart data legend or the PivotTable, the highest total sales commissions by year were 2022: Office #5; 2023: Office #2; 2024: Office #3 (orange boxes). Office #5 commissions decreased in 2023, and then increased slightly in 2024, but not to the 2022 level. Office #3 commissions increased steadily each year. Offices #1 and 2 commissions increased significantly from 2022 to 2023 but decreased slightly in 2024. Offices #4 and 6 commissions declined steadily from 2022–2024.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part B 1. Insert the cursor in the Excel table in worksheet “c. # of Trips by Office.” Select Insert>PivotChart. Insert a Clustered Column chart. Hide the field buttons. Add a chart title and a data legend. Insert a horizontal axis title (steps not shown). Suggested improvements: The PivotChart shows number of 2024 trips and average commission dollars for each office, sorted from highest average commission to lowest. However, the y-axis is displayed in dollars. The actual number of trips should be displayed as integers on the y-axis. The display could be improved by showing the data in two separate graphs with the appropriate measure displayed on the y-axis (dollars or integers).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part C 1. Insert the cursor in the Excel table in worksheet “d. # Trips by Co.” Insert a clustered column chart as the PivotChart type. Hide the field buttons. Add a chart title. Show the y-axis amounts in thousands. The PivotChart below results. Improvements to presentation: Based on the solution provided, the two types of bars in the PivotChart have conflicting formats. For example, # of trips is denominated in integers as in the y-axis. However, the y-axis actually represents dollars. Furthermore, the legend does not need to refer to 2024, as this is stated in the title. Also, the two bars differ significantly in magnitude as judged by their relative heights. The number of trips is not visible.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. To improve the data displayed, it would be better to prepare two separate charts or use a different type of chart. For instance, right-click in the chart (1) and select Copy (2). Click in a cell below the chart and press Control + V (Paste) (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Right-click in the new chart and choose Change Chart Type… (step not shown). In the dialogue box, choose Combo (1) >Clustered Column – Line on Secondary Axis (2). Click OK (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. After formatting, the following PivotChart is displayed.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part D 1. Insert the cursor in the Excel table in worksheet “e. Top 3 Cos.” Select Insert>PivotChart (steps not shown). The solution below uses a clustered column chart. A title and data legend showing the dollar amounts of the top three tour company sales by year have been added. Field buttons are hidden. Interpretation of the results: The top three companies and relative order of total trip sales dollars have not changed much over the three-year period. Pacific Trails has consistently been slightly higher than ZAG Tours, which has consistently been slightly higher than Kayak Adventures. Sales for all three companies have been declining.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part E 1. Insert the cursor in the Excel table in worksheet “f. Seasonal Effects.” Insert a PivotChart in clustered column format (steps not shown). Add a title and hide all field buttons on the chart. Delete the legend on the right-hand side. The PivotChart below shows total commissions by quarters for each year, from 2022 through 2024. Based on relative column heights in the PivotChart, seasonal fluctuations are apparent in 2022, but these gradually dissipate throughout 2023 and 2024.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. A line chart shows these fluctuations more clearly. To display a line chart, place the cursor in the clustered column chart. Copy and paste (steps not shown). Rearrange the new chart position as desired. Right-click in the new chart (1). Select Change Chart Type… (2).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. In the dialogue box, select Line (1) >Line (2). Click OK (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. The following chart results. The quarterly fluctuations from 2022–2024 and their gradual dissipation are more apparent than when shown by the clustered column chart. Save your work.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 7: Inventory List with New Data Part 3 1. Insert the cursor in either the PivotTable or the PivotChart (1). Select Insert (2) >Slicer (3). 2. In the dialogue box, select both the Part (1) and Brand (2) filters. Click OK (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. Two slicers appear. Resize them and rearrange their locations as desired.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. Click on Axle in the Part slicer (1). Then choose the multi-select tool (2) and choose Bearing (3). Click on CCM in the Brand slicer (4). Then choose the multi-select tool (5) and choose Raleigh (6). The PivotTable and PivotChart are displayed as shown below. Save your work.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Challenge 8: Ed’s Overland Adventures Part 4 Part A 1. Place the cursor in the PivotTable (1). Select Insert (2) >Timeline (3). In the dialogue box, check the Date box (4). Click OK (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Click and drag the timeline to reposition it as desired. If needed, select MONTHS in the drop-down menu (1). Use the slider (2) to display August (3) and September (4) months of 2022. Select the AUG button, hold down the Shift key, and select the SEP button. The appropriate data is displayed (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part B 1. Insert the cursor in the PivotTable or the PivotChart. Select Insert>Timeline. In the dialogue box, click the Date selection. Click OK (steps not shown). Reposition the timeline as desired. In the Date timeline, use the drop-down menu icon to change the period to DAYS (1). Move the slider until the June 1, 2024 day is visible (2). Click and drag the border of the box so that all days from June 1–15 are displayed (not shown). Click the June 1 button (3), hold down the Shift key (4), and click the June 15 button to highlight all the dates in this period (5).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Insert the cursor in a white cell in the Average of Commission column of the PivotTable (1) (not a blue-shaded cell). Right-click and select Sort (2) >Sort Smallest to Largest (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
3. The needed data is displayed. Change the chart title to Number of Trips and Average Commissions June 1–15, 2024 (1). Save your work.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Note that slicers and timelines allow only a limited number of filtering options. More extensive filtering needs to be done by inserting the cursor in the PivotTable or PivotChart, right-clicking, and choosing an option under Sort> or Filter> (red box).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Part C 1. Insert the cursor in the PivotTable or the PivotChart (1). Right-click and select Insert (2) >Slicer (3). In the dialogue box, tick the box to the left of “Adventure Co.” (4). Click OK (5). Re-position the slicer as you see fit.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
2. Insert the cursor in the PivotTable or PivotChart (1). Select Insert (2) >Timeline (3). In the dialogue box, check the Date box (4). Click OK (5). 3. Re-position the timeline as you see fit. Select “Intrepid” in the slicer (1). Then choose the multi-select tool (2) and select “Nipiwin Adventures” (3) and “Pacific Trails” (4).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
4. In the Date timeline, select MONTHS from the drop-down menu if needed (1). Move the slider so that the November and December 2024 months are displayed (2). Select the NOV button (3), press the Shift key, and select the DEC button (4). 5. If needed, place the cursor in a value cell of the Sum of Tour Price column in the PivotTable (1). Right-click and select Sort (2) >Sort Largest to Smallest (3).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
The needed data is displayed. Change the chart title as required (1 & 2). Save your work.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Homework 8.pdf
Homework 9.pdf
Homework 10.pdf
Homework 11.pdf
Final Project Checkpoint2.pdf
Homework 3 - Questions (Part 1).pdf
Homework 2 - Questions.pdf
Homework 1 - Questions.pdf
Homework 2 - Questions.pdf
project 7.docx
Homework 3.docx
Wk 4 Program Evaluation - Belt Chrystal.docx