ACC ACF 2400 s1 2021 Tutorial 8 Excel exercises 1, 2, 3 Instructions PDF

Preview

Summary

ACC ACF 2400 s1 2021 Tutorial 8 Excel exercises 1, 2, 3 Instructions...

Description

ACC/ACF 2400 s1 2021 – Tutorial Week 8 Excel Exercises - Instructions

1) Pivot tables – Exercise 1 (exercise modified from: Parsons et al. (2017), New perspectives Microsoft Office 365 & Excel 2016, Cengage Learning, USA)

Lorena started her business selling organic vegetables harvested from her backyard farm. To better accommodate both her individual customers and business clients, 6 months ago, she opened Australian Veggies as a storefront. Lorena wants to use the June data to analyse the current state of Australian Veggies storefront operations. Lorena has entered the June sales data into an Excel workbook. You have been hired by Lorena Lopez to work for Australian Veggies as a junior accountant. As a first task, she asked you to help her analysing the data. • • •

Open the excel workbook Week 8 Tutorial_Exercise 1_Australian Veggies_ACC ACF 2400 s1 2021 In the Documentation worksheet, enter your name in cell B3 and the date in cell B4 Go to the Sales Data worksheet. This worksheet contains data about the vegetable store’s sales. Name data on cells A1:E105 as JuneTbl. You would need to create your PivotTables in new worksheets using JuneTbl as table range.

Lorena asked you to do the following [HINT: You need to create a PivotTable and/or PivotChart for each of the following items]: a) Lorena wants to see the total value of sales by Sales ID. Within each Sales ID, she wants to see total sales for each day. Finally she wants each day further divided to display sales for each type of business. She recommended dragging the Business field to columns. b) In relation to the PivotTable you created, Lorena wants you to apply the Pivot Style Medium 14 style. By using this style, each group in the PivotTable stands out and subtotals in the report are easier to find. She also wants the numbers in the PivotTable to be quickly recognised as dollars. Finally, change the name of ‘values’ from ‘Sum of Amount’ to ‘Total Sales.’ c) Lorena wants to focus on specific subsets of sales so she wants you to create another table that allows you to view or print either total sales for all Sales IDs, or total sales for a specific Sales ID (such as 1), or multiple Sales ID (such as 2 through 5). She wants you to prepare a summarised

Page 1

ACC ACF 2400 Accounting Information Systems

d)

e)

f)

g)

Tutorial 8

report that shows only sales for Sales ID 2 (use same Pivot Table style as ‘b’) [HINT: She suggests using the FILTER area]. Lorena wants flexibility in how she views the data in the PivotTable so she asked you to filter the Business field using a slicer. For presentation purposes, the colour of the slicer should match the PivotTable created in 2 above. Copy Pivot Table created in ‘d’ and changed the format of the slicer: First insert four (4) columns in the slicer that you created so the four (4) options are presented horizontally AND then remove the slicer heading ‘business.’ Lorena wants you to create another PivotTable that summarises sales by days of the week so she can gain insights into staffing and ordering for each day. In terms of presentation, she asked you to use Pivot Style Medium 14 style when creating the table. She is also asking you to add a PivotChart next to it. She thinks the CLUSTERED COLUM chart would best represent the data in the PivotTable. In terms of the PivotChart, she recommends removing the legend (re. the PivotChart has 1 data series only) and the filters. Finally, she asked you to title it Sales by Day, to use the same colour style as the PivotTable created before, and to display sales only from Monday to Friday. Finally, she asked you to start preparing the dashboard report by copying the chart created in ‘f’ into the ‘report’ worksheet.

2) Pivot tables – Exercise 2

(exercise modified from: Dalgleish (2018) from Contextures Inc.) (this exercise is a follow-up to the exercise done in week 5 lecture)

•

Download the file Week 8 Tutorial_ACC ACF 2400 s1 2021_Excel Exercise 2_Pen and Paper Pty Ltd from Moodle

•

Once you have it open, do the following: a. Create a PivotTable that shows total sales and number of units sold for each sales representative per product. b. Assume that each sales representative will earn a 5% bonus if they sell more than 120 units of any product. Show bonuses by adding a calculated field called Bonus. c. Change name of calculated field to Employee Bonus.

When you create a calculated field, you might expect to see a sum of the calculated amounts in the Pivot Table's Subtotal and Grand Total rows. However, as you can see in the field you just created, the calculated field uses the same calculation in the subtotal and grand total rows, instead of showing a sum. To overcome this problem, hide the Subtotals and Grand Totals rows.

Page 2

ACC ACF 2400 Accounting Information Systems

Tutorial 8

3) Pivot tables – Exercise 3 (this exercise is supported by the modelling text Fairhurst (2015), chapter 9, pp. 245254)

•

Download the file Week 8 Tutorial_Trade Data_PivotTable_ACC ACF 2400 s1 2021 from Moodle o

•

These data have been modified from a Pivot Table spreadsheet from http://www.fontstuff.com/training/excel/stepbystep.htm

Once you have it open, construct pivot tables and pivot charts to answer the following questions: a. What is the total value of trade for Australia in each year (20 132020)? b. What is the percentage difference from the 2013 total trade value of Australia’s trade in each of the subsequent years (2014-2020)? c. Show these figures in tables AND in a line chart

Remember! By setting up the pivot table, you have just completed the first stage of programming a business dashboard.

Page 3...