Need Help With This Assignment?

Let Our Team of Professional Writers Write a PLAGIARISM-FREE Paper for You!

SUBMIT YOUR ASSIGNMENT

Management, Math/Economics/Statistics - Excel Based

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

Question 

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

Inventory Analysis at Walmart

Inventory Analysis at Walmart

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