Analyzing Sales for MTSU Pharma 

Analyzing Sales for MTSU Pharma 

You have been hired as an outside consultant to analyze sales and other documents for MTSU Pharma. They have three products that were marketed for three years, but they decided to reassess which to continue offering. They are trying to understand which product accounted for the most sales and profits, but also which one was the most expensive to manufacture and supply. Your task is to analyze the order/sales information that they have, gain additional insights through using formulas, and put together a presentation for senior leadership. They need to determine which of these three products to start marketing again and which products to potentially sell off to other companies.

Don't use plagiarized sources. Get Your Custom Essay on
Analyzing Sales for MTSU Pharma 
Just from $13/Page
Order Essay

You are being asked to present data showing total sales by product, total profits by product, and total costs by product using charts to illustrate your conclusions.

Complete the following:

  1. Open the Module 3 – Pharma.xlsx workbook and save the file as MTSUPharma.xlsx.
  2. On the Sales sheet, use formulas to complete the worksheet for the total cost, profit, total sales per order and % of total volume columns. In rows 39-42, calculate the highlighted cells (totals, average, etc.)
  3. Use conditional formatting to highlight certain orders. In the Order Volume (Doses) column, apply a light green fill with green font to order volumes above the average of all of the order volumes. In the Total Cost, apply a light red fill with darker red font to the top 10 most expensive orders (cost). In the Profit column, apply a light blue fill with darker blue font for the top 10 most profitable orders.
  4. Insert a new worksheet and name it “Sales by Product”. Create a pivot table from the Sales data that shows each product and the total costs for those products by year.
  5. In what year did each product generate the most sales (in $)? Highlight the cell for each product with the highest sales for that product in light orange. In what year did each product generate the lowest sales (in $)? Highlight the cell for each product with the lowest sales for that product in light green.
  6. Create a pivot chart using a column format that shows sales (in $) per product by year. Make the cost bars blue. Next, add a chart title and name it “Total Sales by Product by Year”. Add data labels to the columns, bold them, and move them (if needed) to be able to be read above the columns. Place the chart to the right of the pivot table and stretch it so that it runs from E1 to T25
  7. Next, insert a slicer and place it to the right of pivot table and set it to years so that the user can select between 2009, 2010 and 2011 and see overall profit in both the pivot table and the pivot chart. When using the slicer, the pivot table and chart should update simultaneously.
  8. Create a new sheet to the right of Sales by Product and name it Profit by Product. Create a pivot table that shows each product by year and its associated profits for that year.
  9. Next, create a pie chart that shows profit by product per year. As with the first chart, create a slicer that will allow you to select by year and review the profits generated for each of the three products.
  10. Lastly, create a fourth sheet and name it Cost by Product. Create a pivot table that shows total cost by product per year. Create a pivot line chart to show the total costs of each product by year. Extend the chart from cell E1 to R25. Create a slicer that allows selection of each product. Add a linear trend line to show how the cost of the drug is trending over the three years being reviewed. Make the trend line orange and extend it forward for one year.
  11. To complete the analysis, answer the following questions with a complete sentence each: Over the course of the three years in question, what product generated the most sales? Which one the most profits? Which product generated the greatest cost? Place your answers on the Sales sheet in cells A45, A46 and A47, respectively.
  12. Save and close

ORDER NOW »»

and taste our undisputed quality.