Posted: April 13th, 2023

Mod3 essay

Module 3 Mid-Level Exercise: Analyzing Sales for MTSU Pharma (MO-3.1, 3.2, 3.3)
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.

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(your last name).xlsx.

2. On the Sales sheet, use formulas to complete the worksheet for the total cost, total profit, total sales per order and % of total sales 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 sales 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 sales columns 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 overall.

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. Add data labels to display the dollar amounts on the chart. 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. This trendline should be seen no matter what is selected in the slicer.

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

Check Price Discount

Study Notes, Research Topics & Assignment Examples: Discussion Healthy Eating Approach Essay »MKT00720 – Marketing Assignment 2: Report