EX 536 Excel Chapter 8 Working with Trendlines, Pivot Table Reports, PivotChart Reports, and Slicers
Analyze, Correct, Improve continued
vertical
1. Correct Change the bar of pie chart on the Chart sheet to a clustered column chart. Use the
Select Data button (CHART TOOLS DESIGN tab I Data group) to correct the data range
the chart is based upon so that it contains only quarterly information. Change the chart layout
to Layout 5 and apply chart Style 5. Change the chart title text to match that in cells A1:A2 of
the Revenue Totals worksheet. Delete the axis titles and data labels, and set the primary
axis to show values. Add a linear trendline to the chart. Extend the trendline by two quarters,
and display the R-squared value on the chart.
2. Improve On the Revenue Totals worksheet, apply a fill color of 'Plum, Accent 1, Darker 25%
and a text color of White, Background l' to cells A1:A2. Format cells A1:A2 with the Title cell
style. Format cells A3:F3 with the Heading 3 style, and cells A7:F7 with the Total cell style.
Bold cells A4:A7 and resize column A to fit. Apply a currency format with a currency symbol
and no decimal places to ranges B4:F4 and 37:F7, and a currency format with no symbol and
no decimal places to ranges B5:F6.
Move the chart to the Revenue Totals worksheet, and resize it so that it fills the range
A8:F27.
If requested by your instructor, add a footer with your name and phone number in the
center section.
Save the workbook. Submit the revised document in the format specified by your
instructor.
3. In this exercise, you added a forecast period to your trendline. How confident would you
be
using the forecast generated?
In the Labs
Design and/or create a workbook using the guidelines, concepts, and skills presented
in this chapter. Labs 1 and 2, which increase in difficulty, require you to create solutions
based on what you learned in the chapter; Lab 3 requires you to create a solution, which
uses cloud and web technologies, by learning and investigating on your own from
general guidance.
Lab 1: Creating a PivotTable Report
and PivotChart Report
Note: To omplete this assignment, you will be required to use the Data Files for Students. See
the inside back cover of this book for instructions on downloading the Data Files for Students, or
contact your instructor for information about accessing the required files.
Problem: You work for Altar Holdings and help the financial director prepare and analyze
revenue and expense reports. He has asked you to create two PivotTables and corresponding
PivotCharts based on sales data. One Pivot Table and PivotChart summarize the sales by Supplier
(Figure 8-92a). The other Pivot Table and PivotChart summarize the Digital Products sales by
month for the top supplier (Figure 8-92b).
Working with Trendlines, PivotTable Reports, PivotChart Reports, and Slicers Excel Cha; ter 8 EX 537
Lan 1 Ahar Holdings Complete - Excel
PAGE LAYOUT
FORMULAS DATA REVIEW
Sigo
HOME
SNSERE
Formula Bat
Q
ME
Grdines Headings
Zoom 100% Zoom to
Selectie W
W Po
Switch
Windows
book
Show
Zoom
STUDENT ASSIGNMENTS Excel Chapter 8
D
E
H
Pivot Table Fields
Samci Sales
Supplier
Store
lendrids toy strmporium Livingston cemaal te Wholesale chib Wand 1 otat
Choose fields to add to repart
Month
Suppher
Category
Store
7 Sales
MORE TABLES
5€
16
17 Grand Total
18
$300.000
375.343
$172,019
$270,444 $238,940
$322,273
$1,079,019
Drag fields between areas below
COLUMNS
$250,000
Y FILTERS
Store
20
21
22
23
$200 000
$150,000
$100,000
Hendricks
ROWS
Σ VALUES
Sum of Sales
$50,000
Ivy Ts Emporium
Livingston
Omarell's
Supplies
50
Apparel
Imports
ishings
IT:
uppliers
Home
tal Shelf
orkwear
30.G's Wholesale Club
10
act
Deter Layout Update
Stockist
Sales by Supplier
Digital Product Sales by Month
Sales Results
100%
PEADY
(a) Sales by Supplier
?
Lab 8-1 Altar Holdings Complete - Excel
Sign in
a
FORMULAS
DATA
PAGE LAYOUT
REVIEW
VIEW
FILE
HOME
INSERT
Split
Hide
View Side by Side
DI Synchronous Scrolling
Reset Window Position
Ruler
Formula Bar a
Gridlines Headings
Zoom 100% Zoom to New Arrange Freeze
Selection Window All Panes
Show
Zoom
Macros
Switch
Windows
Unhide
<
Normal Page Break Page Custom
Preview Layout Views
Workbook Views
Window
Macros
D30
K
J
Н.
$31 298 $25,000
$32,011 $20,000
R = 0.3019
A
B
С C
D
E
F
G
Category
Digital Products
.
O.G.'s Wholesale Club
3 Sum of Sales Store
$35,000
4 Month
0.G.'s Wholesale Club Grand Total
5 1
$26,949
$26,949 $30,000
6 2
$27.640 $27,640
7 3
$31,298
84
$32.011
$24,097 $24,087
95
$23,918 $23,918 $15,000
10 6
$21,058 521,058
11 7
$10,000
$28.158 $28.158
129
$32,438 $32,438 $5,000
13 10
$17,333 $17,333
14 11
SO
$15.639 $15.639
15 12
1 2 3 4 5 6
$280,529
7 9 10 11
$280,529
12
16 Grand Total
17
18
19
20
21
22
23
24
25
26
27
Digital Product Sales by Month
Sales by Supplier
Sales Results
READY
(b) Digital Products Sales by Month
Figure 8-92
x 538 Excel Chapter 8 Working with Trendlines, Pivot Table Reports, PivotChart Reports, and Slicers
in the Labs continued
Instructions: Perform the following tasks:
1. Open the workbook Lab 8-1 Altar Holdings from the Data Files for Students and then save the
workbook using the file name, Lab 8-1 Altar Holdings Complete.
2. Using the data in the Sales Results worksheet, create the Pivot Table and associated PivotChart
shown in Figure 8-92a in a separate sheet in the workbook. Name the worksheet Sales by
Supplier.
3. If necessary, add two blank lines above the Pivot Table and then change the contents of cell
A4 to Supplier and cell B3 to Store. Apply the 'Pivot Style Dark 21' style to the Pivot Table.
Format the values as currency values with a dollar sign and no decimal places. Apply the chart
Style 14 to the PivotChart. Resize the PivotChart to cover the range A18:G35 and then hide
the field buttons.
4. Create a second Pivot Table and associated PivotChart, as shown in Figure 8-92b, in a separate
sheet in the workbook. Name the worksheet Digital Product Sales by Month.
5. Change the contents of cell A4 to Month and cell B3 to Store. Apply the 'Pivot Style Dark 4'
style to the Pivot Table. Format the values as currency values with a dollar sign and no
decimal places.
6. Filter the category by Digital Products. Filter the store to O.G.'s Wholesale Club.
7. Change the chart type to Line and then add a linear trendline that forecasts the trend for three
more months. Add the R squared value to the trendline. Apply the chart Style 15 to the PivotChart
and then hide the field buttons. Delete the legend. Resize the chart to the range D1:H16.
.
8. If requested by your instructor, add the text, Contact number, followed by your phone number
to cell B66 of the Sales Results worksheet.
Save the workbook. Submit the revised document in the format specified by your instructor.
9. How helpful is the monthly breakdown when analyzing sales of various products?
Lab 2: Manipulating PivotTables and PivotCharts
Problem: The Wordsmiths editor-in-chief has asked you to analyze the current week's billing
sheet using Pivot Tables and PivotCharts
. She wants you to create them for three scenarios: (a) the
payment amount totals for hours billed, (b) the averages of the hours per region, and (c) the cost of
the miscellaneous hours if they had been billable. The Pivot Tables and PivotCharts should appear
as shown in Figure 8-93.
Instructions: Perform the following tasks:
1. Open the workbook Lab 8-2 Wordsmiths Billing from the Data Files for Students and then
save the workbook using the file name, Lab 8-2 Wordsmiths Billing Complete.
2. Create the Pivot Table shown in Figure 8–93 a based on the data in the range A4:F26 in the
Billing worksheet. Add a calculated field
called Billed Amount that multiplies the hours billed
by 45. Create the PivotChart shown in Figure 3-93a. Name the worksheet Billing Amounts
.
Change the contents of cell A3 to Regions. Apply the Pivot Style Medium 2 to the Pivot Table.
Format the Billed Amount values as currency values with a dollar sign and no decimal places.
Format the Hours Billed with the number format. Apply the chart Style 5 to the PivotChart.
Purchase answer to see full
attachment