Revenue and expense reports. – Essaylink

 

 

For the Module 8 Lab Assignment, you will help the financial director of Matchbox Financial prepare and analyze revenue and expense reports. HINT: The finished Module 8 Lab Assignment should include 4 worksheets.

Problem: You work for Matchbox Financial and help the CFO prepare and analyze revenue and expense reports. He has asked you to create two PivotTables and corresponding PivotCharts based on sales data. One PivotTable and PivotChart summarize the sales by supplier. The other PivotTable and PivotChart summarize the digital products sales by month for the top supplier.

Perform the following tasks:

1. Open the workbook, Lab 8 Matchbox Financial from the Data Files folder and save the workbook as LastnameFirstnameModule8Lab.

2. Create a new worksheet before the Sales Results worksheet with your name in cell A1, date in cell A2, course in cell A3, instructor name in cell A4, and assignment name in cell A5. Name this worksheet “Module 8 Lab Assignment Info”.

3. Using the data in the Sales Results worksheet, create a PivotTable in a separate worksheet named “Sales by Supplier”. Move this worksheet after “Sales Results” and change the worksheet tab color to orange. The PivotTable needs to start in cell A3; Store is in Columns, Supplier is in Rows, and Sum of Sales is in Values; sort Store and Supplier by A-Z)

4. Change the contents of cell A4 (Row Labels) to Supplier and cell B3 (Column Labels) to Store. Apply the ‘Pivot Style Dark 21’ style to the PivotTable (data font color is white, data background color is brown, column heading row and Grand Total row font color is white/background color is black). Format the values as currency values with a dollar sign and no decimal places.

5. Create a Clustered Column PivotChart from the PivotTable data. Resize the PivotChart to cover the range A18:G35 and then hide ALL field buttons. Apply the ‘Chart Style 14’ to the PivotChart (last chart style in selection menu).

6. Using the data in the “Sales Results” worksheet, create a second PivotTable (should start in cell A3; Store is in Columns, Supplier is in Rows, Sum of Sales is in Values, and Category is in Filters; sort Store and Month by A-Z) in a separate worksheet in the workbook named “Digital Product Sales by Month”. Move this worksheet after “Sales by Supplier”.

7. Change the contents of cell A4 (Row Labels) to Months and cell B3 (Column Labels) to Store. Apply the ‘Pivot Style Dark 7’ style to the PivotTable (data font color is black, data background color is tan, column heading row and Grand Total row font color is white/background color is brown). Format the values as currency values with a dollar sign and no decimal places. Filter the category by Digital Products. Filter the store to Wilton Wholesale Club.

8. Create a Line PivotChart from the PivotTable data. Resize the PivotChart to cover the range D1:H16 and then hide ALL field buttons. Apply the ‘Chart Style 15’ to the PivotChart (last chart style in selection menu). Delete the legend. Now, add a linear trendline that forecasts the trend for three more months. Add the R-squared value to the trendline and make it visible.

9. Update the author (use your name) and title (use ‘Student’) in the document properties.

Upload your assignment as a Microsoft Excel workbook using the following naming protocol: LastnameFirstnameModule8Lab.xlsx

Please answer these questions:

Among many advanced Excel users, PivotTables are known as the best thing since sliced bread. Provide a reason why you believe this idiom might be said among advanced Excel users. Do you agree or disagree with the idiom? Explain your reasoning!
Suggest (2) real-world scenarios in which a PivotTable report would be useful to analyze data.
Discuss the role planning plays in the decisions you must make when deciding how to best use PivotTables PivotCharts, trendlines, and slicers.

 

The post Revenue and expense reports. first appeared on COMPLIANT PAPERS.