ITM310 – Problem Solving #9 – Analyzing BI Data – 25 points

CEO Janet Cash is becoming concerned about the profitability of products that AdventureWorks has available for sale. While the overall numbers look ok, there are some problem areas. The company’s product managers are becoming overwhelmed with the volume of sales data, and need some analytical help to make better decisions.

The IT department has prepared an Excel data extract from the AdventureWorks database for you to use (BI sales query.xls). It contains:

· TerritoryName

· ProductCategory

· ProductSubCategory

· QtySold – total units sold

· AvgPrice – average sales price per unit

· GrossRevenue – sum of LineTotal

· StdUnitCost – the accounting department’s standard cost per unit

· StdCost – total units sold multiplied by standard cost per unit

· GrossMargin – GrossRevenue minus StdCost

· MarginPct – GrossMargin as a percentage of GrossRevenue

Janet wants you to prepare a memo with recommendations for products that the product managers need to address. Specifically, you should include the following:

a. How did you approach your analysis? What steps did you take to isolate the products you are recommending? (Hint: Don’t stop with the first problem you uncover.)

b. What product categories or sub-categories did you identify? Use Excel to prepare a formatted summary.

c. What kinds of reports or analytical procedures would you recommend for the product managers to use on a regular basis to avoid this problem in the future?

Deliverables: Please respond in 3 pages or less, double-spaced, be sure to:

a. define any terms you use

b. include chart examples and cites

c. upload your completed case paper back to the Blackboard Assignment link in this week’s folder.

Do not paste your response in the text box, but click on the “upload” link below the text box. Your file name should be named: PS#9_lastname, and be saved in Office for Windows or PDF format.