require to do python coding and generate 12 charts based on the excel data using google collab
Microsoft Word - Assessment03 Student Guidelines Assessment 3 Pixystems Toys Project Due: 13 October 2019. 11:59 pm Weightage: 30% 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 centres for accounts payable (AP), payroll, and inventory management. These shared service centres 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 1st and 15th 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 employee’s 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