Please give step by step solution 
LEARNING OBJECTIVES Calculate the cost of goods sold and ending inventory using the specific identification, FIFO, LIFO, and weighted average methods. Contrast the effect of each on income determination, taxes, and cash flow during periods of inflation, deflation, and stable prices. Identify the source and effect of inventory ("fictitious") profits. Identify the effect of last-minute purchases on income determination. PROBLEM DATA Del Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of $320,000 and the 500 units purchased to replace them cost $256,000, so his cash account has increased by $64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her he'd "make at least $50,000 after taxes. That will give us $25,000 after paying off the investors." Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Purchase Unit Unit Total Specific Cost of Date Quantity Cost Cost Units Sold Units Sold Beginning 200 $ 400 $ 80,000 200 $ 80,000 Mar. 7 100 $ 440 $ 44,000 50 22,000 May 13 50 480 24,000 Aug. 28 200 520 104,000 200 104,000 Nov. 20 150 560 84,000 50 28,000 Total 500 256,000 purchased Total available 700 $336,000 Sales 500 500 $234,000 Ending balance 200 A quick calculation shows that Rio's net income will be $51,600 using specific costs for the inventory sold. Sales minus cost of goods sold equals gross profit ($320,000 - $234,000 = $86,000). Taxes to be paid are 40% ($86,000 x 4 = $34,400). Subtract taxes from gross profit to get net income ($86,000 - $34,400 = $51,600). Next, Del calculates his ending cash balance. He currently has $64,000 from his sales less his inventory replacement purchases ($320,000 - $256,000 = $64,000). He needs to pay taxes ($34,400) and dividends to his investors ($51,600 x .5 = $25,800). Subtracting $34,400 and $25,800 from $64,000 leaves him with only $3,800. Yikes! Del is shocked by the computations. He cannot figure out how he will ever explain to his wife that he has net income in excess of $50,000 and yet after paying off the investors he will have only $3,800 to show for it! Del knows you are taking an accounting class and comes to you for help. REQUIREMENT 1. Del has heard that the choice of an inventory cost flow assumption can have a significant effect on net income and taxes. He asks you to show him the differences between the specific identification method and the cost flow assumptions of FIFO, LIFO, and weighted average methods. Review the worksheet FIFOLIFO that follows these requirements. Note that all of the problem data have been entered in the Data Section of the worksheet. 2. Using a pencil, fill in columns F and G in the Data Section of the worksheet printout at the end of this problem 3. Open the file FIFOLIFO from the website for this book at cengagebrain.com. Fill in columns F and G in the Data Section. Then enter all 14 formulas where indicated on the worksheet. Be careful to write FORMULAS 1 through 8 broadly enough to include different sales quantities (i.e., suppose all inventory was sold or no inventory was sold). Enter your name in cell A1. Save the results as FIFOLIFO3. Print the worksheet when done. Also print your formulas. Check figure: FIFO net income (cell E36). $56,400. 4. Examine your completed worksheet and answer the following questions: a. Which inventory cost flow assumption produces the most net income? b. Which inventory cost flow assumption produces the least net income? c. What caused the difference between your answers to a and b? d. Which inventory cost flow assumption produces the highest ending cash balance? e. Which inventory cost flow assumption produces the lowest ending cash balance? f. Does the assumption that produces the highest net income also produce the highest cash balance? Explain. g. As you recall, Del originally used the specific identification method in his initial calculations when he projected $51,600 net income. According to Del's reckoning, that should have left him cash of $25,800 (50% of $51,600) after paying his investors. Why would he only have $3,800 left? Explain. h. Which inventory cost flow assumption would you suggest Del use? Explain. FIFOLIFO Inventory Cost Flow Assumptions Data Section FIFO Units Sold Specific Units Sold 200 50 LIFO Units Sold Purchase Date Beginning Balance Mar 7 May 13 Unit Cost $400 $440 480 520 560 Unit Quantity 200 100 50 200 150 500 700 500 200 Total Cost $80,000 $44,000 24,000 104,000 84,000 256,000 $336,000 Aug 28 200 50 Nov 20 Total purchased Total available Sales Ending balance 0 500 0 these totals must agree with cell B17 Calculations Cost of goods sold Ending inventory cost Total goods available Specific ID FIFO LIFO Average FORM1 FORM2 FORM3 FORM4 FORM5 FORM6 FORM7 FORM8 SO SO SO SO these totals must agree with cell D16 Answer Section Sales Cost of goods sold Gross profit Taxes (40%) Net income Specific ID $320,000 FORM9 FORM11 FORM13 SO Income Statement FIFO LIFO $320,000 $320,000 FORM10 0 FORM12 SO FORM14 0 SO SO Average $320,000 0 SO 0 SO Sales Less inventory replacement Specific ID $320,000 (256,000) $64,000 0 0 SO Cash Flow Analysis FIFO LIFO $320,000 $320,000 (256,000) (256,000) $64,000 $64,000 0 0 0 0 SO SO Average $320,000 (256,000) $64,000 0 0 SO Less taxes Less dividends Change in cash account Ending Inventory Costs $120,000 $100,000 $80,000 $60,000 $40,000 $20,000 SO A B Chart Data Table B 80000 110000 98000 LEARNING OBJECTIVES Calculate the cost of goods sold and ending inventory using the specific identification, FIFO, LIFO, and weighted average methods. Contrast the effect of each on income determination, taxes, and cash flow during periods of inflation, deflation, and stable prices. Identify the source and effect of inventory ("fictitious") profits. Identify the effect of last-minute purchases on income determination. PROBLEM DATA Del Rio began Rio Enterprises on January 1 with 200 units of inventory. During the year, 500 additional units were purchased, 500 units were sold, and Del ended the year with 200 units. Del is very satisfied with his first year of business although the cost of replacing his inventory rose continually throughout the year. The 500 units sold for a total of $320,000 and the 500 units purchased to replace them cost $256,000, so his cash account has increased by $64,000. Del is concerned however because he has three obligations yet to meet: taxes, dividends, and his wife. Federal and state income taxes will take 40% of his income. His investors are to receive dividends equal to half of any income after taxes are paid. And finally, Del promised his wife a big trip to Hawaii if she let him quit his job as a professor and start his own business. He promised her he'd "make at least $50,000 after taxes. That will give us $25,000 after paying off the investors." Del kept fairly good records during the year and knows the specific cost of each inventory unit sold. He has prepared the following table to summarize his purchases and sales. Purchase Unit Unit Total Specific Cost of Date Quantity Cost Cost Units Sold Units Sold Beginning 200 $ 400 $ 80,000 200 $ 80,000 Mar. 7 100 $ 440 $ 44,000 50 22,000 May 13 50 480 24,000 Aug. 28 200 520 104,000 200 104,000 Nov. 20 150 560 84,000 50 28,000 Total 500 256,000 purchased Total available 700 $336,000 Sales 500 500 $234,000 Ending balance 200 A quick calculation shows that Rio's net income will be $51,600 using specific costs for the inventory sold. Sales minus cost of goods sold equals gross profit ($320,000 - $234,000 = $86,000). Taxes to be paid are 40% ($86,000 x 4 = $34,400). Subtract taxes from gross profit to get net income ($86,000 - $34,400 = $51,600). Next, Del calculates his ending cash balance. He currently has $64,000 from his sales less his inventory replacement purchases ($320,000 - $256,000 = $64,000). He needs to pay taxes ($34,400) and dividends to his investors ($51,600 x .5 = $25,800). Subtracting $34,400 and $25,800 from $64,000 leaves him with only $3,800. Yikes! Del is shocked by the computations. He cannot figure out how he will ever explain to his wife that he has net income in excess of $50,000 and yet after paying off the investors he will have only $3,800 to show for it! Del knows you are taking an accounting class and comes to you for help. REQUIREMENT 1. Del has heard that the choice of an inventory cost flow assumption can have a significant effect on net income and taxes. He asks you to show him the differences between the specific identification method and the cost flow assumptions of FIFO, LIFO, and weighted average methods. Review the worksheet FIFOLIFO that follows these requirements. Note that all of the problem data have been entered in the Data Section of the worksheet. 2. Using a pencil, fill in columns F and G in the Data Section of the worksheet printout at the end of this problem 3. Open the file FIFOLIFO from the website for this book at cengagebrain.com. Fill in columns F and G in the Data Section. Then enter all 14 formulas where indicated on the worksheet. Be careful to write FORMULAS 1 through 8 broadly enough to include different sales quantities (i.e., suppose all inventory was sold or no inventory was sold). Enter your name in cell A1. Save the results as FIFOLIFO3. Print the worksheet when done. Also print your formulas. Check figure: FIFO net income (cell E36). $56,400. 4. Examine your completed worksheet and answer the following questions: a. Which inventory cost flow assumption produces the most net income? b. Which inventory cost flow assumption produces the least net income? c. What caused the difference between your answers to a and b? d. Which inventory cost flow assumption produces the highest ending cash balance? e. Which inventory cost flow assumption produces the lowest ending cash balance? f. Does the assumption that produces the highest net income also produce the highest cash balance? Explain. g. As you recall, Del originally used the specific identification method in his initial calculations when he projected $51,600 net income. According to Del's reckoning, that should have left him cash of $25,800 (50% of $51,600) after paying his investors. Why would he only have $3,800 left? Explain. h. Which inventory cost flow assumption would you suggest Del use? Explain. FIFOLIFO Inventory Cost Flow Assumptions Data Section FIFO Units Sold Specific Units Sold 200 50 LIFO Units Sold Purchase Date Beginning Balance Mar 7 May 13 Unit Cost $400 $440 480 520 560 Unit Quantity 200 100 50 200 150 500 700 500 200 Total Cost $80,000 $44,000 24,000 104,000 84,000 256,000 $336,000 Aug 28 200 50 Nov 20 Total purchased Total available Sales Ending balance 0 500 0 these totals must agree with cell B17 Calculations Cost of goods sold Ending inventory cost Total goods available Specific ID FIFO LIFO Average FORM1 FORM2 FORM3 FORM4 FORM5 FORM6 FORM7 FORM8 SO SO SO SO these totals must agree with cell D16 Answer Section Sales Cost of goods sold Gross profit Taxes (40%) Net income Specific ID $320,000 FORM9 FORM11 FORM13 SO Income Statement FIFO LIFO $320,000 $320,000 FORM10 0 FORM12 SO FORM14 0 SO SO Average $320,000 0 SO 0 SO Sales Less inventory replacement Specific ID $320,000 (256,000) $64,000 0 0 SO Cash Flow Analysis FIFO LIFO $320,000 $320,000 (256,000) (256,000) $64,000 $64,000 0 0 0 0 SO SO Average $320,000 (256,000) $64,000 0 0 SO Less taxes Less dividends Change in cash account Ending Inventory Costs $120,000 $100,000 $80,000 $60,000 $40,000 $20,000 SO A B Chart Data Table B 80000 110000 98000
