Instructions

Read the following case background (adapted from Chartered Professional Accountants of Canada, 2020) and the Required section below. Once you have completed reading the background and the Required section, refer to the step-by-step instructions on how to complete Part 1 and Part 2.

Background

Circuit Board Inc. (CBI) is a distributor of computer parts such as circuit boards and memory storage. CBI is located in Calgary, Alberta, and has a small customer base that tends to place regular orders. You, CPA, have just been assigned as the audit senior on the inaugural CBI audit for the period ended December 31, 2018. Previously, CBI had reviews completed by your firm.

As senior auditor, you will be responsible for dealing with inventory where professional judgment is required, including valuation of inventory items and analysis of items that may require a writedown. CBI sells an average of 46 items of each type of product per year at an average margin of 24%.

CBI has provided you with an electronic copy of its inventory cost analysis and inventory data log files for the past three years. The inventory cost analysis file includes the master list of the product numbers, cost, and average sales price. The inventory data log file shows the quantity sold. CBI provided this information to help you determine if any of the inventory items require a writedown.

The dataset provided contained the following fields:

CustomerID	Unique identifier for each customer. CBI has 23 customers.
InvoiceNumber	Invoice number for sales made on credit
InvoiceDate	Date of credit sale
ProductNumber	Unique identifier for each product. CBI has 25 different products.
QuantitySold	Number of units sold
ItemCost	Per-unit cost the item was purchased for
AmountSoldFor	Per-unit amount the item was sold for
InvoiceAmount	Total amount of the invoice (quantity sold unit price)

Hints:

Your visualization has to be selected before you can work on it. To select your visualization, simply click on it in Power BI.

Using Microsofts Snipping Tool, you can easily capture an image of the specific visualization requested.

Required

Part #1 - Sales and product number cost analysis

To help the company evaluate inventory values, CBIs accounting system tracks cost and the sales price. One of the key concerns with inventory is whether the items have been recorded at the proper value (valuation of inventory). Inventory should be recorded at the lower of its cost and the net realizable value (that is, the most recent sales price).

To begin your analysis of the inventory, its important to obtain a good understanding of CBIs operations, including the sales demand for each item over the past two years and whether sales for the item are increasing or decreasing.

Submit your response to each of the requirements listed below using screenshots from Power BI and by following the steps outlined in Appendix A.

Create a card visualization for total sales.

Create a card visualization for total number of products.

Create a cluster column chart visualization displaying the total sales by product number.

Create a table visualization displaying total sales by product number.

Create a cluster bar chart displaying average margin by product number.

Create a table visualization displaying item cost, sales prices for 2016, 2017, and 2018, average sales price, average variance, and average margin by product number.

Using the information above, identify the five products with the lowest margins. For these five products, create a table visualization displaying the total quantity sold. Filter the date to show the quantities for 2016, 2017, and 2018.

Part #2 - Memorandum to audit partner regarding valuation of inventory

Based on the analysis above, prepare a memo to the audit partner discussing what inventory items are overvalued and the amount of provision CBI should adjust for the December 31, 2018, inventory year end. The partner may wish to discuss the inventory writedown with CBIs management, so be as specific as possible with your examples and state any assumptions you have made in your analysis.

Structure

Your submission should include one document including the following:

All screenshots outlined in the required for Part 1.

A memo for Part 2