INTRO TO INFO TECHNOLOGY EXCEL

User Generated

Svvfnyfhjv

Business Finance

Description

Bus 230 EXCEL Chap 8 (Page 536-538). NEED TO BE DONE AFTER 2 HOURS OR SOONER.





Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Just what I was looking for! Super helpful.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags