a. Set up rows 1 through 10 of the following spreadsheet to compute cost of goods sold and ending inventory, assuming (1) FIFO, (2) LIFO, and (3) weighted average cost flows. Notice that the FIFO cost flow has already been completed for you. Use columns 0 through W to complete the LIFO and weighted-average cost flow computations. Be sure to use formulas for all calculations. Page 314 X Microsoft Excel - ch8 1.ds file Edit View Insert Format Tods Data Window Help D A & D X C O . - 3. Y @ T Arial 10 Efx 2 + m + 100% BI U E333 $ % , t . -. A. F G H FIFO Ending Inventory Unit Total Units Price Cost B C D E Cost of Goods Available For Sale UnitT otal Units Price Cost 4 Beginning Inventory 150 $15 52.250 5 First Purchase (cash) 120 $16 $1,920 6 Second Purchase (cash) 160 $1752.720 7 Total Cost of Goods Available for Sale 430 $6,890 I J K FIFO Cost of Goods Sold Unit Total Units Price Cost 150 $15 $2,250 120 $16 $1,920 60 $17 $1,020 $5,190 L M N L IFO Ending Inventory Unit Total Units Price Cost 1 00 $17 100 $1,700 $1,700 Average Cost $16.02 Check: El +CGS El + CGS 430 Units $6 890 Total Cost WEIGHTED LIFO AVERAGE Income Statements 15 Sales (50 units @ $30 each) (cash) 16 Cost of Goods Sold 17 Gross Margin 18 Operating Expenses (cash) 19 Income Before Taxes 20 Income Tax Expense (30%) (cash) 21 Net Income FIFO 59.900 5.190 4710 1,700 3,010 903 $2.107 FIFO N&&&&&& Statement of Cash Flows Cash from Operating Activities: Cash received from Customers Cash paid for Inventory Purchased Cash paid for Operating Expenses Cash paid for Income Taxes 29 Net Cash Flow from Operating Activities 30 Beginning Cash 31 Ending Cash $9.900 (4,640) (1,700) (903) 2,657 1000 $3,657 Source: Microsoft Office Excel 1997 b. In rows 13 through 31, compute the amount of net income and net cash flow from operations under FIFO, LIFO, and weighted average. The FIFO column has been provided as an example. At January 1, Year 2, the accounting records of Ginger's Boutique had the following balances: Cash Inventory Common stock Retained earnings $1,000 2,250 (150 units @ $15) 2,000 1,250 2,0 During January, Ginger's Boutique entered into five cash transactions: 1. Purchased 120 units of inventory @ $16 each. 2. Purchased 160 units of inventory @ $17 each. 3. Sold 330 units of inventory @ $30 each. 4. Incurred $1,700 of operating expenses. 5. Paid income tax at the rate of 30 percent of income before taxes. Required a. Set up rows 1 through 10 of the following spreadsheet to compute cost of goods sold and ending inventory, assuming (1) FIFO, (2) LIFO, and (3) weighted average cost flows. Notice that the FIFO cost flow has already been completed for you. Use columns O through W to complete the LIFO and weighted average cost flow computations. Be sure to use formulas for all calculations. Page 314
