Descriptive Statistics Question & Sample Answer

Create your detailed analysis plan using the appropriate statistical techniques for analyzing data on descriptive measures and outcome measures based on the methodology you proposed.

Evaluate data analysis tests to select appropriate tests pertinent to the sampling methodology and program evaluation design.

How will you answer your guiding research question (or questions)?

Evaluate the research assumptions that guided your selection of methodology and data analysis. For example, to conduct paired t tests, you must have a minimum of 30 participants in your sample size.

Describe any qualitative data you might collect and how you would analyze that data.

Describe the alignment of your research question and/or hypothesis and your chosen program evaluation design.

AC-335 – Final Course Project


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. (30 points total, 5 points for the writeup, and 5 points for each analysis)
    1. Sales by store by product class. Provide insights into which store generated the most sales of each product type. (5 points)

Store 4 led in the amount of spirits sales last year, after selling 111 items. Store 1, on the other hand, led in the wine sales, with 103 items sold that year. Therefore, the company should ensure that Store 4 is prioritized while distributing supplies of spirits and the same case for Store 1. They can consequently maximize the sales there new by investing more resources in Spirits’ marketing campaigns around store 4 and of wine around store 1. This also presents an excellent opportunity to discover why a particular class of product is more prevalent in one market than other markets. For example, spirits are generally more expensive than wine therefore with an average price of $22.91 as compared to the $15 that whine sell at on average. Therefore, the management can try to lower spirits prices in store one in an attempt to increase their demand.

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

Top 5 Vendors

Vendor Sum of Sales Revenue
3960 109496.45
4425 80685.72
12546 76186.12
17031 59512.33
3252 50807.19
Total 37668781

Bottom 5 Vendors

Vendor Sum of Sales Revenue
28750 4958.38
8352 4354.72
7239 4064.18
9744 2939.08
4950 2409.16
Total 18725.52

The top five vendors make almost half the sales (43.43 percent). This is an implication of their value to the ABC Liquors at large the management can prioritize these stores before in terms of allocation of resources to optimize their productivity. It is also important for the management to identify and understand the cause of this disparity in terms of the productivity of between these vendors’ performance and the rest of the chain. By determining whether these factors are internal or external and whether the management has control over them, it is good to ensure that the rest of ABC Liquors catches up. On the other hand, Drastic changes should be carried on the bottom five vendors, as management can use them for experimenting on changes before implementing them on the rest of ABC Liquors, as they present minimal risk to the survivor of the business.

    1. Sales by employee by store. Provide insights as to who sold the most product in each location. (5 points)
Row Labels Sum of Sales Revenue Sum of Quantity Sold
Jerry Lawler 50960.11 4389
1 50960.11 4389
Rebekah Mraz 48385.62 4338
1 48385.62 4338
Craig Icarus 48452.46 4254
1 48452.46 4254
Tom Flannigan 45390.73 4127
1 45390.73 4127
Steve Stevens 46559.63 4037
1 46559.63 4037
Andrew Roads 46591.76 4024
1 46591.76 4024
Mo Phelps 47298.62 3938
1 47298.62 3938
Jill Holmes 35243.94 3606
5 35243.94 3606
Shavonne River 35237.12 3488
5 35237.12 3488
Antonio Rivas 43842.6 3040
2 43842.6 3040
Sinclair Sonos 45789.78 3022
2 45789.78 3022
Tony James 44418.09 2991
2 44418.09 2991
Ruth Jordan 40909.8 2920
2 40909.8 2920
Fred Treps 42569.4 2860
2 42569.4 2860
Amanda Austin 39733.77 2823
2 39733.77 2823
Thomas Brady 30215.99 2801
4 30215.99 2801
Avery Hardaway 39255.01 2799
2 39255.01 2799
Edward Manning 30613.51 2749
4 30613.51 2749
Sarah Roberts 29748.19 2681
4 29748.19 2681
Jane Lee 28750.62 2638
4 28750.62 2638
Chris Revis 26545.87 2413
4 26545.87 2413
Mike McCarthy 10665.01 1399
3 10665.01 1399
Tim Smith 10121.5 1350
3 10121.5 1350
Grand Total 867299.13 72687

The employee who sold the most product is Jerry Lawler, who works in-store 1. He sold 4389 times valued at $50960.11 and was followed closely by Rebekah Mraz 4338 items worth $48385.62. On the other hand, Tim Smith was the poorest performing employee, who sold 1350 items worth $10121.5 in total. The margin between the best and poorest performing employee is significant, something that can be attributed to the store’s general performance, as the best performing employee worked in store 1 while the worst performing one, Tim Smith and Mike McCarthy both work in store three. There is also a significant correlation between the number of items sold and the value of sales as employee with the most sales value had also sold more items.

Net Income by Store by Month (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. Aggregate the total store costs by month. Subtract the store costs from the margin.

    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.
Store Total Margin for each Store by Month Total Store Costs by Month Net Income by Store by Month
1 96731.74 81650 15081.74
2 80276.02 75700 4576.02
January 3911.75 6500 -2588.25
February 4114.44 6000 -1885.56
March 6630.58 6300 330.58
April 5526.13 6200 -673.87
May 7762.6 6700 1062.6
June 10040.46 6400 3640.46
July 8570.32 7100 1470.32
August 5604.58 6300 -695.42
September 4621.43 5900 -1278.57
October 11899.44 6150 5749.44
November 6178.48 5850 328.48
December 5415.81 6300 -884.19
3 5283.6 4425 858.6
January 353.16 300 53.16
February 285.83 350 -64.17
March 391.88 400 -8.12
April 369.46 350 19.46
May 311.25 300 11.25
June 795.72 450 345.72
July 403.32 350 53.32
August 469.16 300 169.16
September 431.98 350 81.98
October 607.8 400 207.8
November 505.41 425 80.41
December 358.63 450 -91.37
4 38176.61 28810 9366.61
January 1511.58 2250 -738.42
February 2248.67 2340 -91.33
March 5270.12 2175 3095.12
April 1928.51 2550 -621.49
May 2222.06 2775 -552.94
June 4554.87 2475 2079.87
July 2924.48 2620 304.48
August 3409.47 2175 1234.47
September 3078.82 2475 603.82
October 5404.12 2355 3049.12
November 2095.06 2175 -79.94
December 3528.85 2445 1083.85
5 18319.91 32000 -13680.09
January 911.22 2400 -1488.78
February 1361.29 2250 -888.71
March 1606.4 2600 -993.6
April 1136.34 2700 -1563.66
May 3086.07 3250 -163.93
June 1679.31 2325 -645.69
July 1158.75 2550 -1391.25
August 1607 3200 -1593
September 1431.23 2650 -1218.77
October 1819.3 2800 -980.7
November 1149.08 2650 -1500.92
December 1373.92 2625 -1251.08
Grand Total 238787.88 222585 16202.88

The most common profit margin was 35.54 percent and 40.54 percent for the wine, with 778 items sold in total, while least laid between 45.54 percent and 20.54 percent, with only four entries. Therefore, it is clear that the wines that generate the most sales yield moderate profits while the business yields the most profit margin from the least popular among customers.

The most popular spirits yield between 19.52 percent and 24.52 percent in sales with 531 entries while the least popular spirits yield between 9.52 percent and 14.52 percent, with 164 entries. It is therefore clear that the spirits that yield more profit margin for the business are also the most popular among customers. This can also be proof that the demand for spirits are price inelastic, thus the business can raise the process of spirits without the risk of a significant fall in sales.

  • 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. (25 points total. 5 points for the writeup, and 20 points for the analysis, see breakdown of points below)
    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. (5 points)

Frank Benford suggested that people are committing fraud and fixing numbers with an attempt to commit fraud, they are more likely to limit themselves to a given set of digits. They are also likely to choose numbers closer to 1, and this reduces to nine. The yellow line depicted in the figure above, represents the line of behavior of Bedford’s numbers, while bars represent the most common first digits as used in the sales revenue. By superimposing the two graphs, it is clear that the first digits are consistent with Benford’s prediction of fraud. This method, therefore, processes that fraud was committed.

    1. 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. (10 points)
      • Hint: Late chapter 7 labs should be helpful. The field you are interested in is the count of “Transaction ID” in the pivot.

Particular vendors seem to have relationships with the employees; for example, Diageo North America Inc And Jim Beam Brands Company appear to have comprised a significant proportion of the various employee sales. There is, therefore, a need for the mode of operation of the vendors within ABC Liquors to be monitored to ensure that they do not influence the employee. There may be fraud being committed.

    1. Perform a gap analysis on Transaction ID. Are there any gaps? Highlight your findings. (5 points)

Gap analysis can be conducted on the transaction IDs, which are usually chronologically ordered in ABC Liquors. One can, therefore, monitors whether there is a significant difference between the number of transactions made and the movement of the transaction ID. Thirty-two thousand three hundred eighty transactions were recorded in the data set, while the transaction ID starts at 100001 and ends at 132404, recording 32 404 orders according to the allocation of the transaction ID. Thus 24 transactions cannot be accounted for, showing that fraudulent activities may be happening.

  • 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 overview your results 300-350 words. Provide appropriate visualizations and tables to support your findings.
      • 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. (10 points)

In order to break even, Store 1 would have to sell 24569 items, Store two would have to sell 19289 items, store 3 would have to sell 2302 items, store 4 would be 10023 items, while store 5 12391items. Given that store one is the best performing store in the chain, its also have a higher break even point, while store three the worst performer has the least break even point. It is therefore clear the stores where more resources have been invested seem to perform better.

    1. 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?(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.

The projection of the performance of the store within the next quarter is as follows: for store 1 34792, 34637, 33794 for the first three months of the next year, respectively. While 29347, 28756, and 27451 respectively, store 2, 2037, 2081, and 2043 for store 3, store 5 is expected to sell 14700, 14104 13537. On the other hand, Sore fire is expected to sell 6044.136, 5669.75, and 5413.446 for the first three months of the next year, respectively. It is therefore clear the best performing, store 5 is expected to perform much better the next year. Thus, the management can rally its resources in marketing and investment into the store to ensure that this happens.

  • Management is considering expanding the business to a new state. The choices are Rhode Island, Vermont, and Michigan. Management expects to have the following cost of capital for each state: RI – 11.5%, VT – 8.5% and MI – 9.5%. Cash outlays are as follows:

RI – Initial outlay: 1,600,000, increased profit years 1-5: 250K, 350K, 500K, 625K and 665K.

VT – Initial outlay: 2,400,000, increased profits years 1-5: 450K, 525K, 675K, 750K, and 785K.

CA – Initial outlay: 1,850,000, increased profits years 1-5; 350K, 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 250-300 words and explain which is the best decision for management to make. (10 points)

The three projects have positive nest present values earning that they are all viable to be carried. However, the management is more likely to yield more benefit by investing into the Michigan project as it is expected to yield the highest economic benefit within the next 5 years. The Michigan project is the most prospective for the management as it has the highest Net present value of 70,904.25. The next viable project is Rhodes Island, expected to yield, $56,687.60 and Vermont comes last with a net present value of $52,414.91. Therefore, it is important to note a significant difference between the best and second-best project, meaning that the manager should focus on the Michigan project with no doubt about its productivity. It is also important not that the discount rate las less influence on a project’s productivity. Therefore, the manager should focus less on government regulations and taxes and more on investing more in a project and ensuring that it has viable sash flows.

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

      • Hint: Make a copy of the data, use a formula to create a new sales tax calculation for each transaction

This change would affect the margin of the products sold but the business differently, with some having a positive and others having a negative difference. However, it is essential to note that the flat tax will affect expensive products that came a cheap one. Therefore, the business can mitigate the loss suffered by ensuring that its sells more luxurious products than the cheaper ones. Given that spirits have more inelastic demand, the increase in taxes will not significantly affect their demand. The wine will, however, be more affected by the increase. The is, however, many uncertainties that will come with the loss it is therefore essential for the ABC Liquor management to influence the lobbyists into working to the rejection of this law.

    1. Management estimates that it spends $2,000 in overhead to maintain relationships with each vendor. As such, if the margin from selling a vendor’s products dips below that threshold the vendor is considered to be unprofitable. Two vendors, E & J Gallo Winery and Banfi Products Corp are going to be raising their prices. Every product they currently provide to the company will increase by a flat $3 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 $3 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 each vendor and perform the analysis on each vendor at a time.

The company is expected to profit from 8962.01 from its business with J Gallo Winery afters flat $ 3 increase in its prices and a loss of -488.97 while it continues with its interaction with Banfi Products Corp. Therefore, the company can maintain these vendors as the cost incurred for the business to maintain its products outweighs the benefit of foots traffic, thus increasing the chances of more sales. However, it is important to confirm whether these products can be obtained from other vendors to avoid losses. The company can also try to reduce the overheads spent on Banfi Products Corp if they choose to continue increasing the prices of its products.


  • Your final submission: 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%.
  • Everything that you are being asked to do relates to a lab exercise. Use them to help you!!!
  • The word count is recommended and is meant for guidance only. If you go a tad 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.
  • Project must be submitted to Blackboard no later than 12/9/2020. 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:

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

  • SAVE YOUR EXCEL WORKBOOK OFTEN!!! Perhaps every 10 minutes. Just in case it crashed you don’t want to lose all of your hard work. If excel is every hung up, just give it a minute or two. Don’t click around and panic. It might just need a minute to process the task.

Looking for a Similar Assignment? Our Experts can help. Use the coupon code SAVE30 to get your first order at 30% off!

Calculate Price

Price (USD)