Information

The final project is designed to incorporate many of the techniques we have learned throughout the course and also to enhance your ability to communicate findings derived from data analysis. The data provided represents a variety of transaction types for a company from January 1, 2020 through December 31, 2020 (although some 2021 transactions may also be present). This data is structured as if it were an accounting system extract in the form of an Excel file with the following tabs: Sales Orders, Shipments, Invoices, Customers, AR Adjustments, Cash Receipts, Employees.

Your responsibility is to create a business report (with a supporting Excel file) communicating the findings from your analytical review. This report should be structured as a formal communication to an executive manager or CEO about what you have done, what you have found, and the concerns and recommendations you have. There should be seven primary sections to the report: Introduction, Data Review, Sales Reporting, AR Adjustment Reporting, KPIs, Process Review, and Conclusion/Recommendations. Make sure to cover the following points by section:

Introduction:

• Explain what areas of the business the report will cover and what analyses will be provided.

Data Review:

• Are there any open sales orders that have not been shipped? If there are open orders, is it reasonable for these orders to have not been shipped by the end of the year?
• Have any shipments been made without a Sales Order? If there are shipments without a Sales Order, what might this indicate about business processes?
• Have any orders been invoiced but not shipped? If there are invoices without shipments, what could this signify about revenue recorded for the year?

Sales Reporting:

• Management tends to review the characteristics of Sales Orders on a frequent basis due to the critical impact of sales on company profitability. This section reviews sales by customer and by sales agent.
• In your Excel file, create a pivot table showing total sales by customer as well as the % of total company sales each customer represents (use the field Sales Order Amount from the Sales Orders tab). In your report, show the top ten customers, combine all other customers, and note the amount of total company sales represented by each of the top ten customers and by all other customers combined. Do the results indicate a high or low level of risk for the companys future sales?
• Provide the same information in Excel and in the report for total sales by state.
• Provide the same information in Excel and in the report for total sales by Employee Role. Determine the employee by using the Entered by field in the Sales Orders tab. Are there any potential concerns with which employees are making sales transactions?

AR Adjustments:

• The AR Adjustments tab contains adjustments (write-offs) of credit amounts due to some issue (e.g., defective product, non-payment, etc.). These are amounts that the company will not receive on the invoices it has created. It is important to monitor these adjustments to help avoid potential fraud.
• In your Excel file, create a pivot table showing total AR adjustments by customer (based on Invoiced amounts) sorted from highest to lowest write-off percentage relative to sales. In your report, show the customers having a 5% or higher write-off percentage.
• In your Excel file, create a pivot table showing total AR adjustments by sales agent (determined from Sales Order Entered by) sorted from highest to lowest write-off percentage relative to sales. In your report, show the sales agents having a 5% or higher write-off percentage.
• How do the customers and sales agents with a 5% or higher write-off compare to the company average write-off?

KPIs:

• This section covers items that would typically be important for management and be reported through daily, weekly, or monthly KPIs (key performance indicators).
• In your Excel file, create a summary pivot table showing the companys outstanding accounts receivable using the following categories: Current, 1 to 30, 31 to 60, 61 to 90, 90+, and Grand Total. Include the table in your report. What comments would you have about the distribution of accounts receivable among the categories?
• In your report, include a calculation of DSO (Days Sales Outstanding) using a 365-day year. What is your opinion of the DSO result?
• Create a pivot chart showing total invoice amounts by month, average write-off % by month, and a comparison benchmark of 5% write-off by month. Invoice amounts should be shown as a bar graph, and the actual and benchmark write-off percentages should be shown as line graphs (hint: use a combo chart). Include the chart in your report and comment on the companys performance relative to the 5% benchmark.
• Create a pivot chart showing the average days to ship by month (i.e., shipment date minus sales order enter date) as compared to a company benchmark of two days. The actual average should be shown as a bar graph, and the benchmark days should be shown as a line graph. Include the chart in your report and comment on the companys performance relative to the two-day benchmark.

Process Review:

• This section reviews items that would be important for auditors to evaluate whether company processes are being followed and whether they are effective.
• Create appropriate analytics to answer the following questions:
  • Are there situations where an invoice is issued before shipment is made?
  • Are there customers who have a balance greater than their total credit limit?
  • Are there situations where the same person enters an AR adjustment and records cash receipts?
• Respond to each of these questions in your report, including any concerns that may result from the answers.

Conclusion/Recommendations:

• Provide a summary of the analyses you performed along with your findings.
• Discuss whether there were key concerns that should be addressed by management.
• State your recommendations regarding process improvements, KPI achievements, or reporting changes.

Be sure to include supporting figures (tables) in your report.