Visualize the data with Tableau

3-Data-Analytics-ToyCase-Assignment.pdf

Home >Business & Finance homework help >Accounting homework help >Visualize the data with Tableau

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

© 2017 PwC. All rights reserved. PwC refers to the US member firm or one of its subsidiaries or affiliates, and may sometimes refer to the PwC network. Each member firm is a separate legal entity. Please see www.pwc.com/structure for further details.

Data Analytics Class Project

Project Assignment

_________________________________________

Pixystems Toys Information

Pixystems Toys is a leading manufacturer and distributor of children’s toys across the globe through third party

distributors (non-company owned stores) and through their flagship store located in New York. The company is

headquartered in Cleveland, Ohio, and has manufacturing locations in Brazil, China, Mexico, and across the United

States. Product development is also done in Cleveland with new products released every six months. The majority

of the toys manufactured by Pixystems are top sellers that are sell outs during holidays. Some of their most well-

known products include: toy cars, plush dolls, and electronic games.

The company does all back office processing at their headquarters where they have shared service centers for

accounts payable (AP), payroll, and inventory management. These shared service centers handle processing of

transactions related to these processes across the globe driving a consistent process and controls. Customer sales

are managed at the flagship store by the local sales team while online sales and sales to distributors are handled at

headquarters.

Pixystems recently hired a new CFO who is focused on improving the financial performance of Pixystems and

improving operational efficiency in AP, payroll, inventory, and sales. She has challenged each of the managers of

these business processes to identify areas for improvement and quickly address errors. Internal audit has also been

working closely with the CFO, managers, and external auditors to identify control gaps, areas of non-compliance,

and operational efficiencies.

The CFO has also engaged you to review these business processes using Pixystems’ data and performing analytics.

She would like you to make any necessary recommendations, identify data issues, and provide her and the

managers with insights gained through your analytics. During your last meeting, she shared the following

information with you:

Accounts Payable:  The Internal Audit team has identified errors within AP that are negatively affecting working capital. The

root cause of these errors has not yet been determined but is thought to be a control gap that will need to be

addressed by the AP manager and subsequently tested by the external auditor.

 Cash flows have not been consistent over the past year. Pixystems sees the majority of sales taking place

around holidays and uses a retail fiscal calendar to accommodate the cyclical nature of sales.

 At least two inaccurate payments to key vendors have been identified by the AP manager and monitoring

for duplicate or erroneous payment is not conducted.

 Payments within terms are also not monitored and leverage of discounts is not reviewed to ensure quantity

levels have been met and Pixystems receives the appropriate discounts.

Payroll:  Majority of employees working at headquarters and managers at the manufacturing locations are salary

employees and not entitled to overtime (twice hourly rate) payments. All other workers should be paid

hourly.

 Payroll is paid to salaried employees on the 1st and 15th of each month through direct deposit. Hourly

employees are paid bi-weekly (every two weeks).

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

 Executive team receives an annual bonus each year which is paid at the end of the year. The amount is

dependent on the employees overall performance ranking and Pixystems’ performance as a whole.

 Average annual employee pay increase is between 3-5% for salaried employees

Inventory:  Inventory levels of finished goods at the flagship store are set by headquarters with sales and marketing

consultation.

 Finished goods inventory is owned by the manufacturing facility until loaded on a truck (FOB Shipping

Point) to a distributor or sent to the flagship store which then takes ownership.

 Online sales directly to customers are sent from the manufacturing facility

 Physical inventory is taken once a year and the completion of cycle counts is at the discretion of the facility

manager.

 Adjustments to inventory levels due to spills, damaged goods, old age, etc. are done once per month and are

approved by the facility manager and headquarters.

Customer Sales:  Distributors must place orders from Pixystems with a minimum of a month lead time. Special and

expedited orders must be approved by headquarters.

 Available inventory levels for direct customer sales are communicated via the online ordering system which

allows customers to order up to the quantity available at the manufacturing facility or distribution center.

 Sales representatives are assigned to specific distributors and are responsible for both increasing sales and

managing the relationship Pixystems has with the distributor.

 Promotions and discounts must be approved by sales and marketing

 Sales are highest around the holiday and certain toys quickly become out of stock.

Data Overview

Following is a high-level summary of the data IT has provided for your analysis.

Customer Table

Field Description

Customer ID SAP generated unique identifier for each customer

Customer Name Name of the customer

Customer Address Customer’s location – address

Customer City Customer’s location - city

Customer State Customer’s location – state

Customer Zip Customer’s location – zip

Sales Rep Name of the sales representative assigned to the customer

Employee Table

Field Description

Employee ID SAP generated unique identifier for each employee

User ID Employee user id

First Name Employee first name

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Last Name Employee last name

Hire Date Date the employee was hired

Termination Date Date of employee’s last working day

Work Location Location where the employee works

Job Title Employee’s job title

Medical Plan ID of the medical plan selected by the employee

Medical Start Date Date medical plan became effective

Savings Plan ID of the savings plan selected by the employee

Savings Start Date Date savings plan became effective

Approval Level Dollar amount the employee can approve

Salary Amount Total annual salary for employee

Hourly Amount Rate per hour

Type Salary or hourly employee

Pay Frequency How frequently employee is paid

Bonus Employee eligibility for bonus payment

Finished Goods On-Hand Table

Field Description

Location ID Location of the finished goods

Detailed Location ID Detailed bin location within the facility where the goods are stored

Finished Item # Item number for specific finished good

Description Description of the finished good

Quantity Amount of product within the location available for sale

Date Received Date the product was received from factory and ready to sell

Min Level The minimum level of inventory to be maintained of the good

On-hold Notes if the inventory is on-hold for a specific reason

Formulas Table

Field Description

Formula Name of the formula

Raw Material #1 - #5 Item numbers from the raw material table that are used to make the finished good

Quantity #1 - #5 Amount of each raw material that is used to make the finished good

Inventory Adjustments Table

Field Description

Date Date the adjustment was made

Location Location where the adjustment should be applied

Detailed Location Bin where adjustment should be applied

Raw Material # Item number associated with the raw material if the bin holds raw materials

Finished Goods # Item number associated with the finished goods if the bin holds finished goods

Adjustment Quantity that is being adjusted

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Adjustment Reason Reason for variance in inventory

Approver Name of the person approving the adjustment

Inventory Count Table

Field Description

Date of Count Date the physical count took place

Location Location of count

Detailed Location Bin that was counted

Item # Raw material or finished good item number

Quantity Counted Amount of the item physically counted

Quantity Expected How much should have been in the bin

Difference Difference between what was counted and expected

Counter Person who performed the count

Approver Person who approved the count

Invoice Table

Field Description

Invoice # Vendor issued invoice number

Vendor # Unique vendor number generated by SAP

Invoice Date Date invoice was generated by the vendor

Item Item number

Item Quantity Amount of the item

Item Cost Cost of the item per unit

Extended Cost Quantity * Item Cost

Freight Cost Cost to ship

Total Cost Extended Cost + Freight Cost

Processor ID Name of the person who processed the invoice

Associated PO Purchase order used to purchase item

Associated Receipt Receipt number associated with goods from invoice

Payment Terms Terms the vendor expects payment in as noted on the invoice

Discount Discount provided if paid within terms

Location Table

Field Description

Location ID XYZ location ID

Location Name Name of XYZ location

Country Country where the location is

Type Type of location

Medical Plan Table Data

Field Description

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Medical Plan ID of medical plan

Plan Description Description of medical plan

Deductible Monthly deductible due from employee

Payment Table Data

Field Description

Payment ID SAP generated unique identifier for each payment

Vendor Vendor Name

PO # SAP generated unique purchase order number associated with payment

Receipt # SAP generated unique receipt number associated with payment

Invoice # SAP generated unique invoice number associated with payment

Payment Amount Total amount of payment

Check # XYZ check number used for payment

Payment Type Type of payment (check #/wire #)

Wire # Wire transfer number associated with payment

Payment Date Date of payment

Processor ID User ID processing payment

Payroll Table Data

Field Description

Pay Type Weekly, bi-weekly, monthly, bonus

Date of Payment Day payment was made

Employee ID Employee’s unique ID

Employee Type Type of employee – Hourly or Salary

Pay Base payment amount; Amount earned

Medical Deduction withheld for medical payment

Savings Percentage withheld for savings selection

Tax Amount of tax paid by the employee

Total Pay Pay minus medical, savings, and tax

Purchase Order Table

Field Description

PO # SAP generated unique purchase order number

Location XYZ location making purchase

Item Item number

Description Item description

Item Quantity Quantity of item to be purchased

Unit Cost Item cost per unit

Total Amount Item quantity * unit cost

Vendor # SAP generated unique identifier for each vendor

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Creator ID User id creating the PO

Creation Date Date the PO was created

Creation Time Time the PO was created

Approver ID User id approving the PO

Approver Date Date the PO was approved

Approver Time Time the PO was approved

Raw Materials On-Hand Table

Field Description

Location ID Location of the finished goods

Detailed Location ID Detailed bin location within the facility where the goods are stored

Raw Item # Item number for specific raw material

Description Description of the finished good

Quantity Amount of product within the location available for sale

Date Received Date the product was received from factory and ready to sell

Receipt Table

Field Description

Receipt ID SAP generated unique identifier for receipt

Location XYZ location receiving goods

Date Date items received

Time Time items received

Item # Item number associated with items received

Item Quantity Quantity of items received

Truck # Associated truck number carrying items

Receiver ID User id of person receiving goods

Vendor # SAP generated unique identifier for each vendor

PO Number Purchase order number

Sales Table

Field Description

Order ID Sales order ID generated by SAP

Order Date Date XYZ received the sales order

Customer ID Customer number uniquely identifying the customer

Item # Finished good item the customer ordered

Quantity Amount of the finished good the customer ordered

Price Price of the finished good item the customer ordered

Promotion Promotional price if product ordered as part of a promotion

Freight Freight cost to ship order

Total Price Price*Quantity or Promotion*Quantity plus freight costs

Promised Date Date order promised to the customer

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Special Order Notes if this is a special order product

Approver Name of person approving the order

Approval Date Date approval was provided

Shipped Date Date the order was shipped

Savings Plan Table Data

Field Description

Savings Plan ID of savings plan

Plan Description Description of savings plan

Percentage Percentage of withholdings from employee paycheck

Vendor Table

Field Description

Vendor ID SAP generated unique identifier for each vendor

Vendor Name Legal name of each vendor

Address Vendor's location – address

City Vendor's location – city

State Vendor's location – state

Zip Vendor's location – zip

Billing Address Vendor's billing location – address

Billing City Vendor's billing location – city

Billing State Vendor's billing location – state

Billing Zip Vendor's billing location – zip

Phone Vendor's phone number

Main Contact Name of company's main contact

Email Email address of main contact

Payment Terms Agreed upon payment terms

Payment Method Agreed upon payment method (check, wire transfer, etc.)

Status Status of vendor (active/inactive)

Status Change Date Date the vendor was entered into the system or their status changed

Discount Level Quantity to be purchased in order to receive discount

Discount Period Period of time allowed to achieve discount

Assignment:

You will work to leverage the data provided by Pixystems (see the Data Overview above for a high-level explanation

of data provided) and information obtained from the CFO to identify areas for financial and operational

improvement, address issues and errors found in processing or data, and confirm the information that the CFO

shared with you. To do this you will be assigned one process and must create analytics that will assess the validity of

data and provide the insight the CFO is looking for. You may also conduct at least 2 additional analytics that will

add additional insight or that you feel would be of interest to the CFO, managers, or auditors.

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

The CFO is expecting that you have Tableau visualizations representing the analytical procedures you performed

and assumptions you made.

You will prepare a final report outlining the following:

 Results of the analytics you performed along with your rational for performing and assumptions made

 Insight that the analytics provided management

 Explanation of any analytics you decided not to perform

 Recommendations you have for improving Pixystems’ processes

 Overview of any other issues that Pixystems should follow-up on

 Recommendations on system controls that could be put in place

 Any other data you would like to have obtained from Pixystems

Process Assignments:

You will only be responsible for analyzing the business process in one functional area. You may consider data and

information from other processes if you feel it has a significant impact on the process assigned to you. Professor

Chalawsky will communicate which area to focus on.

Accounts Payable

Customer Sales

Inventory

Payroll

Checkpoints as part of this assignment (optional, if it helps):

To ensure your report and presentation will be ready for the CFO, you should complete the following:

Checkpoint #1 – Before you load data into any tools you should review the data provided and information obtained

from the CFO to become more familiar with the client and business process. After reviewing this data, you will need

to list out the analytics you plan to perform and document the following in the Checkpoint #1 Spreadsheet:

 Rational for performing each analytic

 Insight the analytic will provide to the CFO, business process manager, internal audit department, and

external audit.

 Data fields to be included in each analytic

 Assumptions made

 Known issues or challenges in performing the needed analytics

http://www.pwc.com/structure

Note: All characters, data and situations appearing in this work are fictitious. Any resemblance to real persons, data or situations is purely coincidental.

Checkpoint #2 – The success of the project requires that you have accurate data along with comfort that the data is

accurate and valid. To do this, you will need to document the following in the Checkpoint #2 Spreadsheet:

 How you will join the tables you plan to use in conducting the analytics determined in checkpoint #1

 Perform summary statistics on all financial fields within the tables used both in total and by calendar year.

 You should also document any additional procedures you performed to assess the validity, accuracy, and

completeness of the data.

 Highlight any missing data or errors in the data

 Other data you would like to obtain if available from Pixystems

Checkpoint #3 You should conduct the analytics noted in checkpoint #1 along any additional analytics you have

determined as you have worked through the project. Your analytics should be performed within Tableau and you

should create visualizations showing your results which you will be presenting to the CFO.

http://www.pwc.com/structure