Consulting Case Study
Background:
John Smith is the President of “ABC Co”, a local company that manufactures industrial products. ABC Co sells several product lines, each having multiple individual items (SKUs). ABC Co sells to only three customers. ABC Co does not have a published “List” price for their products – customers are provided with a negotiated price quotation for each product they may want to purchase.
John has a problem. His long-time VP of Sales & Marketing has left the company in January 2015, and since that point John has been unable to analyze his invoice data. Although John’s accountant can tell him ABC Co’s overall sales levels, John is unable to determine his sales trends for each product line or customer. Without understanding his detailed sales data, John cannot tell if his customers are increasing or decreasing their purchases, or if they are buying more or less of a particular product line. This information is critical for John to direct his Purchasing Manager in raw material procurement and to evaluate customer buying patterns.
Over the past 2 years, John has also been experiencing cost inflation in his raw materials and, as a result, his margins have declined to an unacceptable level. Early this year, John tried to address this situation by announcing a price increase to his customers, with an effective date of February 1, 2016. The price increase was set at 5% across the board for all customers and products. Several months have since passed and the profit improvement he expected has not materialized. John’s Sales Manager has assured him that they implemented the price increase everywhere they could, except for “a few” exceptions due to competitive reasons. Given what his Sales Manager is reporting, John doesn’t understand why his revenue and profits are not improving after the price increase went into effect, and wonders if another price increase is needed.
Yesterday, John called you for advice to help him better understand what is happening at ABC Co. At your request, he has sent you a download of his invoice data, covering 2015 and 2016 year to date. You have a couple of days to review this data and offer John your assessment of his business, including overall revenue, cost and profit trends, the performance of his product lines, customer purchase trends and the observed impact of the February price increase. As part of your feedback to John, provide a recommendation as to whether or not he needs to announce another price increase.
Case Model Considerations:
1) Using Excel Lookup functions, add new fields to the invoice data by bringing in information from the other data sheets (Customer Name, Product Line Name, Product Unit Costs).
2) Identify any deficiencies or errors in the dataset and explain what issues they may lead to if unresolved.
3) Using Pivot Tables, provide ABC Co’s revenue, product costs and margin percent for 2015 and 2016 in total and for each of the three customers. Add any additional fields to the invoice data that are needed to calculate these metrics. Exclude any transactions that do not have a product cost available.
4) Exclude any transactions that do not have a product cost available. Explain why it’s important.
5) What next steps would you take in your analysis based on the trends you see in the data thus far?
-----------------------------------------------------------------------------------------------------------------------
6) What trends are showing in the data, and what do they say about ABC Co’s performance year over year? (Consider any of the following: revenue, price, volume, etc.)
7) Highlight significant SKU’s for ABC Co and review performance trends over time.
8) Based on the transaction data, calculate the financial impact of the February 1 price increase. At the customer-item level, what were the Price Change $ and Price Change %, by customer, relative to the period before the price increase?
9) Based on your review of the data, does John need to announce another price increase? What other steps could be taken to improve ABC Co’s profits?
10) What other insights can you provide John based on his invoice data?
Formula Notes:
Average price should be calculated at the customer-item level