110.279 Auditing Assignment 2, 2023 – Audit Planning

Objective of this assignment: Developing an analytics mindset

Having and using an analytics mindset is critical in audit, accounting and business. This assignment focuses on developing all aspects of your analytics and critiquing mindset, namely the ability to:

● Ask the right questions (the assignment questions illustrate what these may look like).

● Apply appropriate data analytic techniques (Using Power BI, prepare appropriate tables and graphs).

● Interpret and communicate the results and insights (Prepare an audit planning memorandum for Kingi Fisher, Audit Partner).

How to Approach this Assignment

1. Read all instructions before starting the assignment.

2. This assignment requires you to complete tasks using Power BI Desktop. It is free for Microsoft Windows users. Mac users can use remote desktop which has Power BI installed.

3. Attend the Power BI Zoom workshop on 24^th August 2023, 4.30pm – 6.00pm.

4. Use the Power BI Resources on the ‘Course Resources’ page on the 110.279 Stream site.

5. Study the material for Weeks 3, 6 and 7 to make sure you understand the concepts covered.

Before attempting the assignment

1. Download Power BI Desktop OR access Power BI through Massey’s Flexible Learning environment. See ‘Course Resources’ tab on Stream.

2. Download the following Excel file from Stream under the ‘Assessment’ tab and save it on your device: 110.279 Assign2_Receivables Data.xlsx

3. Access the material provided for Power BI training under the ‘Course Resources’ page on Stream.

Submitting the Assignment files

1. The assignment will result in 2 separate files: one Power BI file (.pbix) and one Word file (.docx).

2. Submit both files in the provided submission box for Assessment 2 - Stream records the submission date and time.

3. See the Assessments page on Stream for policies re assignment submission, naming conventions for files submitted, extensions and late submissions. Penalties will apply to late submissions for which an extension has not been granted.

4. Note that substantial marks could be lost if you fail to submit either of the two required files as marks are allocated to the content of each of the files.

Backups

Once you have started working on an assignment you should make backups. We recommend you give each backup a new version number. (This allows you to go back to earlier versions)

ASSIGNMENT BRIEF

You are an audit senior working for Laker Milly, a professional services firm. Your manager, Hazel Flyer, has asked you to assist with planning for an audit client, Mikehill Ltd for the financial year ended 30 June 2023. You have received some summary information as well as data relating to the company’s Receivables. Please access this information from the 110.279 Assign2 – Receivables Data.xlsx file.

Required:

(i) Create one Power BI file and in it proceed as follows:

a. Use ‘Get data’ to load ‘Receivables 2023’ and ‘Receivables 2022’ data into Power BI;

b. Use ‘Transform data’ to access Power Query Editor and add a ‘Custom Column’ for ‘Year’ to each of the two ‘Queries’ i.e., the 2022 and 2023 Receivables data sets/tables. The ‘Custom column formula’ in each case is simply the year concerned (e.g., if you are in the ‘Receivables 2023’ Query, then ‘Custom column formula should be ‘ = 2023’).

c. Move your new ‘Year’ column in each query to the far left, i.e., first column (right click on the column heading to find the ‘Move’ menu option to easily do this).

d. While still in ‘Power Query Editor’, select ‘Home’ then use ‘Append Queries’ to create a new merged table from the Receivables 2022 and 2023 tables. Rename this new table ‘Receivables 22/23’ then click ‘Close and apply’.

e. Go to Model View and hide the Receivables 2022 and 2023 tables.

f. Please then create four pages within this file as per the instructions (1) to (4) which follow (see Power BI File Pages):

(ii) Create one Word file - a draft Audit Planning Memorandum addressed to the audit partner, Kingi Fisher for your manager’s approval.

You should aim to raise at least ten well explained comments/observations in relation to audit risk and/or materiality from the work completed for (i) above. Note: some visualisations may warrant two or three more detailed comments while others may warrant only a brief comment.

Power BI File Pages

1. Page 1, labelled ‘1. Receivables Overview’ should provide an overall understanding of the following aspects relating to Receivables. The purpose here is to provide a simple ‘At a glance, big picture understanding’ of this account balance and related ageing information:

1.1. Use the ‘Text box’ function to provide a large, clear label at the top left of the page indicating that this is the Mikehill Ltd Receivables Overview at 30/6/2023;

1.2. Create an appropriate logo image for the company and upload to the page. You may use PowerPoint or other applications to create the logo.

1.3. Next, use the ‘Visualisations’ - ‘Card (new)’ function to provide clearly labelled comparative totals of Receivables $ balances as well as count of number of balances – show these so 2023 information appears at top left and 2022 information at top right of the page;

1.4. Below the 1.2 information, use the ‘Visualisations’ functions to provide a map for each year, with bubble sizes set to show the sum of balances for each country).

1.5. To clearly differentiate 2023 from 2022 information, colour code the backgrounds for the titles of your cards and maps, as well as the map bubbles: 2023 – light blue, 2022 – light orange;

2. Page 2, labelled ‘2. Ageing’ should provide:

2.1. One clustered column chart showing each of the four ageing categories, arranged from left to right so that ‘0 to 30’ is far left and ‘>90’ is far right. The chart should show a cluster of such columns for 2023 on the left and for 2022 on the right. Use the ‘Format Visual’ options (‘General’) to ensure the title of the chart is amended so that it reads “Age Analysis by YEAR”). Ensure remaining titles also appear sensible and informative.

2.2. A table alongside the chart, showing the same information, but add ‘Country’. Experiment with the down arrows for each heading in the table until you believe your table is sorted to show the most significant items at the top of the table. Ensure the table has a suitable title.

3. Page 3, labelled ‘3. Company Detail’ should provide a table which you need to construct as follows:

3.1. Go to ‘Data View’ and use the ‘New column’ function to calculate the difference between ‘Balance’ and ‘Credit_Lim’ for each row. Name this column ‘Bal > Credit Lim’.

3.2. Go to ‘Report View’ and the “Data” Pane - click on the three dots to the right of the ‘Company’ field and select ‘Create hierarchy’. This should create a new field called ‘Company Hierarchy’. Then click on the three dots to the right of the ‘Balance’ field and select ‘Add to hierarchy’. Similarly, go to the ‘> 90’ field and select ‘Add to hierarchy’ there too. Do the same again for the ‘Bal > Credit_Lim’ field. This should result in the Company Hierarchy showing four subsections: Company, Balance, > 90 and Bal > Credit_Lim.

3.3. Create a table and drag the Company Hierarchy onto it (there should be a column for each of the four subsections).

3.4. Add one slicer for ‘YEAR’ and one slicer for ‘COUNTRY’

3.5. Experiment with the slicers and the down arrows for each heading in the table and record your observations in the Audit Planning Memorandum to highlight significant audit risk and/or materiality as appropriate.

3.6. Finalise this page by selecting (a) ‘Balance’ - sorted from highest to lowest; (b)‘YEAR’ – 2023; and (c) ‘COUNTRY’ – Australia and save the file with these selections before submitting it.

4. Page 4, labelled ‘4. Challenges’ is for you to test your creativity with visualisations to show in the most effective/efficient way possible:

4.1. Only the ‘Top 10 companies by balance - 2023’;

4.2. An exception report of only those 2023 companies where the ‘Balance’ figure does not equal the sum of the figures in the ageing breakdown (ignore immaterial amounts less than $1).

4.3. An exception report of only those 2023 companies where the ‘Balance’ figure is higher than ‘ credit limit  (ignore immaterial amounts less than $1).

Presentation:

● Your Power BI pages should aim to provide simple, clear visualisations with a low ink to information ratio.

● Your Audit Planning Memorandum should be concise but should convey important information in a well-reasoned manner, supported by the data and your analysis, where appropriate. It should be no more than 750 words (10% leeway will be given).

● Failure to adhere to the word limit will lead to mark deductions. You may be penalised at a rate of 1 mark for every 10 words over the word limit.

.