Background:

A new ownership group has recently purchased ABC Graphics. 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).

Management Request:

• Perform the following five descriptive analytics to help Management understand the business environment. Management has requested a write-up, describing what you found. Appropriate visualizations should be included to back up your claims. (30 points total, 5 points for the writeup, and 5 points for accuracy of each analysis)

1. Sales dollars by store and product size. Which combination of store and size was the most popular, what was the least? (5 points)
2. Sales quantities by vendor. Who were the top 5 vendors and bottom 5 vendors from an overall quantity perspective (5 points)
3. Sales Dollars by employee by store. Which employee sold the most in each location (5 points)
4. Net Income in Dollars by Store by Month. Which stores were profitable, and which were not? What month had the highest profit overall across all stores, which month was least profitable? (5 points)

• 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 in a pivot table. Aggregate the total store costs by month in another pivot table. Subtract the store costs from the margin to get profit.

1. 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) (5 points)

• 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. Chapter 2 labs are a great resource!

• 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, 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. (25 points total. 5 points for the writeup, and 20 points for the analysis, see breakdown of points below)
  1. Perform a Bedford's 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. Did the data conform? What do the results mean? (5 points)
  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 12% for all vendors. For example, if they found that 15% of an employee's transactions were selling products from one vendor that would be a problem. Create a conditionally formatted red-yellow-green (or something similar) that highlights your results. Did you find any concerning relationships? If so, who were they between? (10 points)
     • Hint: Late chapter 7 labs should be helpful. The "Value" field you are interested in is the count of "Transaction ID" in the pivot. Vendors should be the row...pick the column that makes the most sense
  3. Perform a gap analysis on Transaction ID. Are there any gaps? Highlight your findings. (5 points)

• 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. 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 sold. Use that figure in your break-even analysis. (10 points)
  2. Perform a sales forecast for the first quarter of next year for each store. Describe the trends and offer advice to Management. Are sales expected to increase or decrease? What are the forecasted sales for last month?(5 points)
     • 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. Chapter 8 labs will be a great resource!

• Management is considering expanding the business to a new state. The choices are Texas, California, and Michigan. Management expects to have the following cost of capital for each state: TX - 12.25%, CA - 11.50% and MI - 10.25%. Cash outlays are as follows:

TX - Initial outlay: 1,600,000, increased profit years 1-5: 250K, 350K, 450K, 625K and 650K.

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

MI - Initial outlay: 1,950,000, increased profits years 1-5; 450K, 500K, 575K, 525K, 600K.

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 and explain which is the best decision for Management to make. (10 points)

• New changes are coming! Management is looking for some insights on how these changes will impact the business. Provide an overview of your results. 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. NH is currently considering passing a new law that will remove their flat sales tax rate of 8% and replace it with a sales tax of 9% on adult size clothing and 8.5% on teen size clothing. Lobbyists against the sales tax change argue that raising the sales taxes will reduce total sales by 10%, 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 NH for each product based on the current 8% 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 10%. 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. (10 points)

• 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.

1. Management estimates that it spends $1,000 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, "Watsica, Bruen and Rosenbaum" and "Feest, Breitenberg and Schuppeare" are going to be raising their prices. Every product they currently provide to the company will increase by a flat $2.00 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.00 increase takes place. Are the products helping the company's bottom line and providing a positive margin? What recommendation would you have for management? (10 points)
   • Hint: Filter your data on the vendors in question and paste the records into a new chart. Recalculate the new cost and then profit.

Expectations:

• Your final submission: You are required to submit one properly formatted memo to Management that addresses each of the topical areas above. Each topical area should be its own section. The document should be clear, concise, and include graphs, tables, charts as necessary to convey relevant information. 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%.
• Use the labs completed throughout the semester to help you!!! Most items in this project relate back to one or two labs.
• The word count is recommended and is meant for guidance only. If you go over it is not the end of the world. That said, Management has limited time to review your work. Excessively long answers will have points deducted as you failed to be concise. Extremely short responses will be considered incomplete. Charts and tables with appropriate labels typically cut down word counts significantly... use that to your advantage.
• The project submission (word document memo, excel sheet) must be submitted to Blackboard no later than August 4^th by midnight. Failure to meet the deadline will result in a zero for the project. No exceptions. That means you are better off turning in incomplete work then nothing at all. At least ATTEMPT items for partial credit.

Overall hints:

• Similar to the descriptive project, the first step is combining all of your data. Go back to week 3-4 where we covered v-lookups and created relationships between tables in excel. Document all of the relationships and use v-lookups to pull in data as needed between tables. The sales table should be your primary table in most of the analysis, so pull data from other tables into it.
• Don't be afraid to use common sense to do basic calculations. For example, on the sales table you are told the product sold as well as the quantity. Pull in the selling price and the cost of these items. Do a simple calculation that multiplies the quantity times the selling price to get the total sales. A similar calculation with the cost will get you the total cost of goods sold. Similar calculations can be made for sales taxes, profits, etc.

I provided you with a data dictionary that tells you what each column on each table represents. Review it before you get started. Most of the data is obvious, however it might help solve some basic confusions.

The Data is linked in the links below!

https://docs.google.com/spreadsheets/d/1-m1HQrSxztzAN-AHyzpCgEzLhIwL9g8EcVnYGgdyNYM/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1rOT4JARhDvytWDcxWOTMw5Gfo8abJ-sQ8DuZT_M715I/edit?usp=sharing