Inventory Cost Analysis for the Month Last-In-First-Out Units Sold for the Month 5,200 Purchase Cost Total Units Cumulative Units Remaining Purchased After Sale Units Sold Inventory Cost Units Remaining From This From This From This Layer Layer Layer COGS From This Layer Units Purchase Date purchased Unit Cost 1-Oct 70 $ 20.00 2-Oct 980 S 20.00 3-Oct 100 $ 20.00 4-Oct 550 $ 20.20 5-Oct 730 $ 20.30 8-Oct 930 $ 20.30 9-Oct 720 $ 20.50 10-Oct 420 S 20.60 11-Oct 40 $ 20.60 12-Oct 180 $ 20.80 15-Oct 890 $ 20.90 16 Oct 220 $ 21.00 17-Oct 240 $ 21.20 18-Oct 510 $ 21.30 19-Oct 200 S 21.30 21-Oct 90 S 21.50 22 Oct 70 $ 21.60 23-Oct 800 $ 21.60 24-Oct 350 $ 21.80 25 Oct 470 $ 21,90 28-Oct 30 $ 21,90 29-Oct 770 S 22.10 30 Oct 870 S 22.20 31-Oct 270 $ 22.20 Total Purchase Cost Units on Hand Total Inventory Cost Total COGS Build a spreadsheet that can calculate the inventory cost and the cost of goods sold. 1. In business, you are likely to have so many transactions that you cannot simply calculate everything manually. Using an Excel spreadsheet will allow you to automate the calculations. Before you create calculations in Excel, you should think about the inputs that you will need to solve this problem. You should also list the major steps that you need Excel to perform A major teaching point for this case is that a complex Excel task can be broken down into smaller tasks. Then you can make a plan to accomplish each small task step by step. For example, FIFO inventory analysis may seem like a complex task but it can be broken down into the following steps: 1. Sort the layers according to their purchase dates: For FIFO, the oldest purchases should be listed first; for LIFO, the newest purchases should be listed first. 2. Calculate the purchase cost for each layer. 3. Calculate how many units have been cumulatively purchased at each purchase date. 4. Compare the cumulative units purchased and the total units sold to determine the number of remaining units after the sale. 5. Calculate how many units remain from each layer after the sale. 6. Calculate the number of units sold from each layer. 7. Calculate the inventory cost for each layer 8. Calculate the cost of goods sold for each layer. Inventory Cost Analysis for the Month First-In-First-Out Units Sold for the Month 5,200 Total Units Remaining After Sale Units Remaining Units Sold From Inventory Cost From This Layer This Layer Form This Layer Th Cumulative Units Purchase Date Units purchased Unit Cost Purchase Cost Purchased 1-Oct 70 $ 20.00 2-Oct 980 $ 20.00 3-Oct 100 $ 20.00 4-Oct 560 $ 20.20 5-Oct 730 $ 20.30 8-Oct 930 $ 20.30 9-Oct 720 $ 20.50 10-Oct 420 $ 20.60 11-Oct 40 $ 20.60 12-Oct 180 $ 20.80 15-Oct 890 $ 20.90 16-Oct 220 $ 21.00 17-Oct 240 $ 21.20 18 Oct 510 $ 21.30 19-Oct 200 S 21.30 21-Oct 90 $ 21.50 22-Oct 70 $ 21.60 23-Oct 800 $ 21.60 24-Oct 360 $ 21.80 25-Oct 470 $ 21.90 28-Oct 30 $ 21.90 29-Oct 770 S 22.10 30-Oct 870 S 22.20 31-Oct 270 $ 22.20 Total Purchase Cost Units on Hand Units on Hand Total Inventory Cost Total
