Background:

A new ownership group has recently purchased ABC Liquors. You have been hired by the new management team to analyze their sales data for the past year and provide them with insights about their new company that will help their long-term success. Management provided you with sales data, and some other information related to vendors, employees, products and stores. Being so knew to the business, Management won’t be of much help outside of this document as their background is real estate and not retail. Management provided the following requests, all of which must be completed accurately to obtain full payment (in your case a perfect score).

Managements Request:

• Perform the following five descriptive analytics to help Management understand the business environment. Management is requested a write-up, between 450-500 words describing what you found. Appropriate visualizations should be included to back up your claims.
  1. Sales Dollars by store by product class. Provide insights into which store generated the most sales of each product type.
  2. Sale Quantities by employee by store. Provide insights as to who sold the most product in each location.
  3. Sales Dollars by vendor to identify the top 5 and bottom 5 vendors. Provide insights as to what percentage of total sales do the top 5 vendors make up.
  4. Net Income in Dollars by Store by Month
     • Hint: Find the margin of each sale by taking the selling price less the cost (don’t forget the quantity sold!). Aggregate the total margin for each store by month. Aggregate the total store costs by month. Subtract the store costs from the margin.
  5. A Histogram of Profit Margins for each Product Class. Provide insights into the top bin for each product (i.e. what is the most common margin for each)
     • Hint: Use the products data tab. Filter your products by class. Identify the maximum and minimum margin percentage. Think back to managerial accounting on how to get a profit margin %. Create a histogram and create bins. Each bin should be 5% or .05.

• An anonymous employee left a sticky note on Management’s desk that they believe that fraudulent and unusual transactions were taking place. Management is requesting that you perform the following three analytics to provide insights into the claim. Management is requesting a write-up, between 350-400 words describing your results. Incorporate the output from your analytics in the writeup. Does the evidence support that fraud could be taking place? Yes or no.
  1. Perform a Benfords analysis on the total sales value of each record. Create a visualization of the analysis and describe the results in your own words in your writeup.
  2. Determine if there are any unusual relationships between Employees and Vendors. Management expects that all employee should be promoting all products, and as such, would expect an employee’s transactions to be under 10% for all vendors. For example, if they found that 11% of an employee’s transactions were selling Bacardi products that would be a problem. Create a conditionally formatted red-yellow-green (or something similar) that highlights your results. Explain your results in the write-up.
     • Hint: Late chapter 7 labs should be helpful. The field you are interested in is the count of “Transaction ID” in the pivot. Vendors should be the row.
  3. Perform a gap analysis on Transaction ID. Are there any gaps? Highlight your findings.

• Management is looking for some forwarding looking information to help them determine the appropriate direction of the business. They asked that you completed the following analytics and provide an overview of your results 300-350 words. Provide appropriate visualizations and tables to support your findings.
  1. Perform a break-even analysis by store. How many items does each store have to sell in order to cover their fixed costs (S&A) for the year?
     • Hint: First, find the total profit of each store for the year, and divide it by the total quantity of goods sold. That will give you the average profit (or margin) made on each item. Use that figure in your break-even analysis. Perform a sales forecast for the first quarter of next year for the combined stores. Describe the trends and offer advice to Management. Are sales expected to increase or decrease? What are the forecasted sales for last month?
       • Once you figure out your sales for each store by month, copy and paste your pivot as “values”. Change the months to numbers, i.e. January =1, February =2.

• Management is considering expanding the business to a new state. The choices are Michigan, Texas, and Florida. Management expects to have the following cost of capital for each state: MI – 11.5%, TX – 8.5% and FL – 10%. Cash outlays are as follows:

MI – Initial outlay: 1,600,000, increased profit years 1-5: 250K, 350K, 500K, 650K and 650K.

TX – Initial outlay: 2,400,000, increased profits years 1-5: 450K, 525K, 675K, 725K, and 800K.

FL – Initial outlay: 1,850,000, increased profits years 1-5; 350K, 600K, 575K, 525K, 500K.

1. Perform a NPV analysis on three potential new stores and provide insights into the following:
   • If Management ignored time value of money, which projects would you recommend they invest in and in what order? Why?
   • What project order would you recommend based on NPV calculations? If you had unlimited funding which projects would you invest in?

Provide an overview of your results 250-300 words and explain which is the best decision for Management to make.

• New changes are coming! Management is looking for some insights on how these changes will impact the business. Provide an overview of your results in 350-400 words. Incorporate visualizations in your write-up as needed from your analytics.

1. Sales taxes are currently collected on each sale and remitted to the state government. NY is currently considering passing a new law that will remove their flat sales tax rate of 7% and replace it with a sales tax of 8% on wines and 9.5% on spirits. Lobbyists against the sales tax change argue that raising the sales taxes will reduce total sales by 20%, and as such if there are less sales dollars there will be less tax dollars.

Provide Management with insights into the current sales taxes collected in NY for each product based on the current 7% flat tax. Recalculate the new sales tax collected assuming that the next period sales would be identical this current periods except for the drop in total sales by 20%. What was the difference in sales tax collected for each product? Create an appropriate visualization to show what will happen if the new law goes into effect.

• Hint: Make a copy of the data, use a formula to create a sales tax column based on the original information. Next, make a new sales tax column based on the new tax rate information given.

2. Management estimates that it spends $2,500 in overhead to maintain relationships with each vendor. As such, if the margin from selling a vendors products dips below that threshold the vendor is considered to be unprofitable. Two vendors, SPIRITS R US and HOOPLA PRODUCTS are going to be raising their prices. Every product they currently provide to the company will increase by a flat $75 in cost. Management is insistent that they do not change the selling price of these items, as customers won’t buy them anymore. These products also bring in foot traffic. Management does not want to drop the vendor, thinking that the company overall will still generate a profit for the year selling those vendor’s products. Reforecast the profit that will be earned on these two vendors’ products for the year assuming the $2.75 increase takes place. Are the products helping the company’s bottom line and providing a positive margin? What recommendation would you have for management?
   • Hint: Filter your data on each vendor and perform the analysis on each vendor at a time.

Expectations:

• Write a memo to Management that addresses each of the topical areas above. The document should be formatted like a memo, and should be clear, concise, and include graphs, tables, charts as necessary. In addition to the writeups requested above, Management is expecting that you turn in a separate excel document that shows your work. Your document should be clear and easy for them to follow. For example, if you performed a Benfords analysis, label that tab of the excel workbook “Benfords”. If you complete an NPV, label the tab “NPV”. Failure to provide clear documentation will result in loss of points. Failure to provide an excel document will result in max score of 50%.