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 your team 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 1^st and 15^th of each month through direct deposit.  Hourly employees are paid bi-weekly (every two weeks).
• 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
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
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
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
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
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

Group Assignments:

You will work with your team and 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 your team.  To do this your group 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 should also conduct between 3-5 additional analytics that will add additional insight or that you feel would be of interest to the CFO, managers, or auditors.

You will meet again with the CFO on (insert date of final presentations) to deliver your results.  She is expecting that you have a Tableau visualization representing the analytical procedures you performed and assumptions you made.  You will have (insert length) minutes to present this visualization, assumptions made, and recommendations.  Each member of your group must have an active speaking role within the presentation.

You will also prepare a final report (insert due date) 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 your team has 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 assigned to you.  You may consider data and information from other processes if you feel it has a significant impact on the process assigned to you.

Accounts Payable – (insert group names assigned to this process)

Customer Sales – (insert group names assigned to this process)

Inventory – (insert group names assigned to this process)

Payroll – (insert group names assigned to this process)

 

Checkpoint Assignments:

To ensure your report and presentation will be ready for the CFO, your team must periodically check in with (insert name).  To do this you need to complete the following:

Checkpoint #1 (Insert Due Date) – 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

 

Checkpoint #2 (Insert Due Date) – 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 (Insert Due Date) – 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.  You will be meeting with the director/partner on (insert date) and you will have (insert length) minutes to show them the visualization created, discuss the issues/challenges, and the format and flow of your final presentation.  You will receive feedback which you need to incorporate into your final presentation and report.