Screen 4 tasks us with using data to optimize the business’ inventory. I find the prompts that were given to be a strange way to go about improving profitability through inventory management. As other have pointed out, their formula for low stock is backwards - it would have you reorder items that are already massively overstocked. Additionally, we are given 30 months of data. It makes little business sense to base your inventory purchase quantity on 30 cumulative months of sales data. I decided to look at just the previous 6 months’ worth of data. This does two things - it allows you to order a quantity sufficient for 6 months’ worth of sales (can easily be reduced to fewer months if the business needs to run a leaner inventory) instead of a quantity sufficient for 30 months of sales, AND it takes new trends into account. Items that sold well years ago but are no longer selling shouldn’t be restocked. Frankly it would be best to break the timeframes down into quarters or months to identify sales trends. Maybe I’ll try that exercise in the future.
Additionally, inventory management requires goals. I have set a goal of carrying inventory of what is typically sold between 6 months and two years. So 6 months to 24 months of stock (or a ‘stock’ of 1 to 4).
Based on my results, I would recommend the following for this business.
Purchase:
1960 BSA Gold Star DBD34 x 500
1968 Ford Mustang x 250
1997 BMW F650 ST x 150
1928 Ford Phaeton Deluxe x 100
More importantly, there are 99 items which have enough product on the shelf to last between 2.5 and 38 years at current sales volume. That’s an unhealthy amount of inventory to be carrying, I would recommend to significantly reduce that inventory by whatever means are available to the business owner.
Lastly, in addition to product performance = quantity ordered * price each, I would look at product profitability = quantity ordered * (price each - buy price) to determine profitability. Revenue is an incomplete picture, profits are much more important. Additionally, product performance and profitability should not have much of an impact on inventory management. Inventory management is based on demand. The business would be best served by ordering items that sell and not ordering items that don’t sell, regardless of the revenue or profits generated. That is more of problem for marketing, and a successful marketing campaign would be reflected in the inventory and allow the inventory manager to adjust purchases accordingly.
I haven’t continued to the remaining screens so maybe a lot of this is covered there, but I thought it was a fun thought experiment.
SELECT p.productname,
p.quantityinstock,
SUM(od.quantityordered) AS quantity_ordered,
od.orderNumber,
ROUND( 1.0 * p.quantityinstock / SUM(od.quantityordered),2) AS stock
FROM orderdetails od
JOIN products p
ON p.productcode = od.productcode
WHERE od.ordernumber IN (SELECT o.orderNumber
FROM orders o
WHERE SUBSTR(o.orderdate,1,4) LIKE '2005')
GROUP BY p.productName
ORDER BY stock