Inventory Analysis at Walmart

Inventory Analysis at Walmart

Inventory Analysis at Walmart Example Spreadsheet SKU numbers, unit costs, retail pricing, and other numbers in the spreadsheet are fictitious and are not intended to represent actual data related to authentic Walmart products
a) Calculate the cells that are highlighted in yellow. Express dollar amounts to the nearest penny and percentage answers to the nearest tenth. (7 points)
Line	Div	Vendor	Item #	Description	Cost	Retail	Margin %	Beginning Week – On Hand	On Order	Sell Thru %	Week 1 – Sales in Units	Week 2 -Sales in Units	Week 3 – Sales in Units	Week 4- Sales in Units	MTD Sales in  Units	MTD Sales $
1	Screw Drivers	Acme	665780	Phillips Head Small 4″	$         1.78	$                   4.00	55.5%	178	66	18.0%	32	53	55	33	173	692.00
2	Screw Drivers	Acme	665781	Phillips Head Medium 4″	$         1.89	$                   4.00	52.8%	118	0	17.8%	21	33	64	0	118	472.50
3	Screw Drivers	Acme	665782	Phillips Head Large 6″	$         2.12	$                   4.50	52.9%	200	47	33.5%	67	44	35	42	188	846.50
4	Screw Drivers	Acme	665783	Phillips Head Extra Large 7″	$         2.25	$                   4.50	50.0%	47	4	6.4%	3	5	3	9	20	90.00
5	Screw Drivers	Acme	665784	Regular Head Small 4″	$         1.78	$                   4.00	55.5%	89	43	38.2%	34	51	22	12	119	476.00
6	Screw Drivers	Acme	665785	Regular Head Medium 4″	$         1.89	$                   4.00	52.8%	62	1	19.4%	12	23	65	120	220	881.00
7	Screw Drivers	Acme	665786	Regular Head Large 6″	$         2.12	$                   4.50	52.9%	89	76	24.7%	22	54	65	43	184	828.50
8	Screw Drivers	Acme	665787	Regular Head Extra Large 7″	$         2.25	$                   4.50	50.0%	150	33	21.3%	32	23	76	66	197	886.50
9	Screw Drivers	Acme	665788	6-in-1 Ratcheting Screwdriver	$         3.46	$                   8.96	61.4%	333	21	23.4%	78	57	77	95	307	2752.00
10	Screw Drivers	Acme	665789	Screwdriver Assortment Pack	$         5.55	$                 18.88	70.6%	345	1500	29.6%	102	110	134	154	500	$ 9,438.78
TOTALS								1611	1791		403	453	596	574
b) Assume that the products in the spreadsheet are ordered every three weeks and take another week to arrive in the store. Consider the products’ on-hand numbers, sell-through percentages, and the month-to-date sales in units. Identify and comment on any consequences that may cause concerns about inventory and order levels and the ability to meet demand over the next 4-week period. (4 points)
Question b:	Items 665784, 665785, 665,786, 665787, and 665789 are fast-moving and cause concerns about the inventory and order levels and the ability to meet demand over the next 4-week period. The number of orders and month-to-date sales outweigh their stock, implying that the company must order more of these items in weeks to balance the demand and supply.
c) Comment on any trend(s) that may be developing and recommend one action to address the direction (s). (4 points)
Question c:	The inventory is characterized by erratic demand patterns for various screwdrivers, defying the usual assumption of normal distribution. Essentially, multiple items have highly variable demand in size. The most effective solution to this problem is to adopt ERP software to facilitate efficient computation of service levels for the customers and the characteristics of the specific suppliers (Nenes et al., 2010). In this way, the software derives the appropriate order quantities for every supply, taking into account the beginning week on-hand of the various screw items.
References
Nenes, G., Panagiotidou, S., & Tagaras, G. (2010). Inventory management of multiple items with irregular demand: A case study. European Journal Of Operational Research, 205(2), 313-324. https://doi.org/10.1016/j.ejor.2009.12.022

ORDER A PLAGIARISM-FREE PAPER HERE

We’ll write everything from scratch

---

Walmart associates use business math to analyze reports and make decisions and recommendations related to inventory levels and product pricing.

Instructions

Use Excel formulas to complete the calculations and answer the short-answer questions in the following spreadsheet: Inventory Analysis at Walmart.

• Hint: When calculating retail price {RP} based on cost {C} and markup percentage {M%} on RP, use the Cost Complement formula we have discussed previously in class: RP = [(C)/(100% – M%)] x 100