Chapter 21, Problem 6P

Spreadsheet and Statement of Cash Flows The following information was taken from Lambcrson Company’s accounting records:

To determine

Prepare a statement of cash flows using spreadsheet method.

Explanation of Solution

Statement of cash flows: This statement reports all the cash transactions which are responsible for inflow and outflow of cash, and result of these transactions is reported as ending balance of cash at the end of reported period. Statement of cash flows includes the changes in cash balance due to operating, investing, and financing activities.

Worksheet: A worksheet is a spreadsheet used while preparing a financial statement. It is a type of form having multiple columns and it is used in the adjustment process. The use of a worksheet is optional for any organization. A worksheet can neither be considered as a journal nor a part of the general ledger.

Prepare a statement of cash flows using spreadsheet method.

Table (1)

Table (1)

Notes:

a. Depreciation for the year ended is $2,100

b. Calculate accounts receivable.

$\begin{array}{l}\begin{array}{c}\text{Decrease in accounts receivable = }\left(\begin{array}{l}\text{opening accounts receivable}\\ \text{ – closing accounts receivable}\end{array}\right)\\ \text{= \$2,800- \$2,690}\\ \text{=\$110}\end{array}\hfill \\ \hfill \end{array}$

c. Calculate the marketable securities.

$\begin{array}{c}\text{Markatable Securities}=\left(\begin{array}{l}\text{Beginning year's markatable securities}\\ \text{- Ending year's marketable securities}\end{array}\right)\\ =\$1,700-\$3,000\\ =\$1,300\end{array}$

d. Calculate the decrease in inventories.

$\begin{array}{c}\text{Decrease in Inventories}=\left(\begin{array}{l}\text{Beginning year's inventories}\\ \text{-Ending year's inventories}\end{array}\right)\\ =\$8,100-\$7,910\\ =\$110\end{array}$

e. Calculate the prepaid items.

$\begin{array}{c}\text{Prepaid items}=\left(\begin{array}{l}\text{Beginning year's prepaid items}\\ -\text{Ending year's prepaid items }\end{array}\right)\\ =\$1,300-\$1,710\\ =\$410\end{array}$

f. Calculate the increase in accounts payable.

$\begin{array}{c}\text{Increase in Accounts Payable = }\left(\begin{array}{l}\text{Closing balance of accounts payable}\\ -\text{Opening balance of accounts payable}\end{array}\right)\\ \text{=\$3,800}-\$4,150\\ =\$350\end{array}$

g. Calculate the income taxes payable.

$\begin{array}{c}\text{Income Taxes Payable}=\left(\begin{array}{l}\text{Beginning year's income taxes payable}\\ -\text{Ending year's income taxes payable}\end{array}\right)\\ =\$2,400-\$2,504\\ =\$104\end{array}$

h. Calculate the wages payable.

$\begin{array}{c}\text{Decrease in Wages Payable}=\left(\begin{array}{l}\text{Beginning year's wages payable}\\ \text{-Ending year's wages payable}\end{array}\right)\\ =\$1,100-\$650\\ =\$450\end{array}$

j. Interest payable for the year ended is $400.

k. Calculate the retained earnings.

              $\begin{array}{c}\text{Retained Earnings}=\left(\begin{array}{l}\text{Beginning year's retained earnings }\\ -\text{Ending year's retained earnings}\end{array}\right)\\ =\$10,000-\$14,100\\ =\$4,100\end{array}$

      l-1. Calculate the issuance of common stock to preferred stock.

          $\begin{array}{c}\text{ Issuance of common stock = Number of share}\times \text{Value of share}\\ =500\times \$10\\ =\$5000\end{array}$

      $\begin{array}{c}\left(\begin{array}{l}\text{Issuance of common stock }\\ \text{to preferred stock}\end{array}\right)=\left(\begin{array}{l}\text{Issuance of common stock}\\ \text{+Additional paid-in capital}\end{array}\right)\\ =\$5,000+\$4,000\\ =\$9,000\end{array}$

      $\begin{array}{c}\text{Additional Paid-in Capital}=\left(\begin{array}{l}\text{Beginning year's paid-in capital}\\ \text{- Ending year's paid-in capital}\end{array}\right)\\ =\$8,700-\$13,700\\ =\$4,000\end{array}$

     1-2. Conversion of preferred stock to common stock is $9,000.

      m. Calculate the proceeds from sale of long-term investments

        $\begin{array}{c}\left(\begin{array}{l}\text{Proceeds from sale of }\\ \text{long-term investments}\end{array}\right)=\left(\begin{array}{l}\text{long-term investments}\\ \text{+Gain on sale of investments}\end{array}\right)\\ =\$1,600+\$700\\ =\$2,300\\ \end{array}$

        $\begin{array}{c}\text{Long-term investments}=\left(\begin{array}{l}\text{Beginning year's investment}\\ -\text{Ending year's investment}\end{array}\right)\\ =\$7,000-\$5,400\\ =\$1,600\end{array}$

n-1. Calculate the Issuance of common stock to pay long-term note.

$\begin{array}{c}\left(\begin{array}{l}\text{Issuance of Common Stock}\\ \text{ to pay long-term debts}\end{array}\right)\text{=}\left(\begin{array}{l}\text{Additional paid-in capital}\\ +\text{Issuance of common stock}\end{array}\right)\\ =\$1,000+\$2,500\\ =\$3,500\end{array}$

 $\begin{array}{c}\text{ Issuance of common stock = Number of share}\times \text{Value of share}\\ =250\times \$10\\ =\$2,500\end{array}$    

n-2. Long- term notes payable is $3,500.

o. Calculate the sale of building and equipment.

• Cost of the equipment is $2,000.
• Loss on sale of equipment is $200.
• Proceeds from sale of equipment is $100.

p. Calculate the payment for purchase of equipment.

 $\begin{array}{c}\text{Payment for Purchase of Equipment=}\left(\begin{array}{l}\text{Sale of building and equipment}\\ \text{+ Cost of equipment}\end{array}\right)\\ =\$14,200+\$2,000\\ =\$16,200\end{array}$

$\begin{array}{c}\text{Sale of Building and Equipment}=\left(\begin{array}{l}\text{Beginning year's building and equipment}\\ -\text{Ending year's building and equipment}\end{array}\right)\\ =\$32,000-\$46,200\\ =\$14,200\end{array}$

q. Calculate the proceeds from issuance of bonds.

$\begin{array}{c}\text{Proceeds from issuance of bonds=}\left(\begin{array}{l}\text{Bonds payable}\\ \text{-Discount on bonds payable}\end{array}\right)\\ =\$10,000-\$300\\ =\$9,700\end{array}$

r. Calculate the increase in deferred tax.

$\begin{array}{c}\text{Increase in Deferred Tax Payable =}\left(\begin{array}{l}\text{Beginning year's deferred income taxes}\\ -\text{Ending year's deferred income taxes}\end{array}\right)\\ =\$800-\$1,196\\ =\$396\end{array}$

s. Calculate the bond discount amortization.

$\begin{array}{c}\text{Bond discount amortization =}\left(\frac{\text{ 12\% on bonds payable}}{\text{Period from issued to maturity}}\right)\\ \text{=}\left[\frac{\text{ \$10,000}\times \text{12\% }}{\text{120 months}\left(\text{10 years}\right)}\right]\\ =\frac{\$1,200}{120\text{ months}}\\ =\$10\end{array}$

t. Calculate the allowance for change in value.

$\begin{array}{c}\text{Allowance for change in value=}\left(\begin{array}{l}\text{Beginning year's allowance }\\ -\text{Ending year's allowance}\end{array}\right)\\ =\$500-\$800\\ =\$300\end{array}$

u. Calculate  net increase in cash.

$\begin{array}{c}\text{Net Increase in Cash}=\left(\begin{array}{l}\text{Beginning year's cash}\\ -\text{Ending year's cash}\end{array}\right)\\ =\$1,400-\$2,400\\ \$1,000\end{array}$

To determine

Prepare the statement of cash flows of L Company for the year ended December 31, 2016.

Explanation of Solution

Prepare the statement of cash flows of L Company for the year ended December 31, 2016.

L Company
Statement of cash flows for the year 2016
Particulars	Amount($)	Amount($)
Operating Activities:
Net income	4,800
Adjustment for non-cash income items:
Add: Depreciation expenses	2,100
Bound discount amortization	10
Loss on sale of equipment	200
Increase in deferred taxes payable	396
Less: Gain on sale of equipment	(700)
Adjustments for cash flow effects from working capital items:
Decrease in accounts receivable	110
Decrease in inventories	190
Increase in prepaid items	(410)
Increase in accounts payable	350
Decrease in wages payable	(450)
Increase in income taxes payable	104
Increase in interest payable	400
Net cash provided by operating activities		7,100
Investing Activities:
Payment for purchase of short-term marketable securities	(1,300)
Proceeds from sale of long-term investments	2,300
Proceeds from sale of equipment	100
Payment for purchase of equipment	(16,200)
Net cash used for investing activities		(15,100)
Financing Activities:
Proceeds from issuance of 12% bonds	9,700
Payment of dividends	(700)
Net cash provided by financing activities		9,000
Net increase in cash( schedule 1)		1,000
Cash on January 1, 2016		1,400
Cash on December 31,2016		2,400
Schedule 1: Investing and Financial Activities Not Affecting Cash
Financing Activities:
Conversion of preferred stock to common stock		(9,000)
Issuance of common stock to convert preferred stock		9,000
Payment of long-term note by issuing common stock		(3,500)
Issuance of common stock to pay long- term note		3,500

Table (2)

To determine

Determine the difference in cash flow from operation to sales ratio and profit margin ratio of L Company for the year 2016.

Explanation of Solution

Cash flow to sales ratio: The cash flow to sales ratio reveals the ability of a business to generate cash flow in proportion to its sales volume. It is calculated by dividing operating cash flows by net sales.

Formula for Formula for to sales ratio:

$\begin{array}{c}\text{Cash flow from operation to sales ratio=}\frac{\text{Cash flow from operation}}{\text{Net sales}}\\ =\frac{\$7,100}{\$39,930}\\ =\$17.8\%\end{array}$

Profit margin ratio: The profit margin ratio, also called the return on sales ratio or gross profit ratio, is a profitability ratio that measures the amount of net income earned with each dollar of sales generated by comparing the net income and net sales of a company.

Formula for Formula for to profit margin ratio

$\begin{array}{c}\text{Profit Margin Ratio}=\frac{\text{Net income}}{\text{Sales}}\\ =\frac{\$4,800}{\$39,930}\\ =12\%\\ \end{array}$

Conclusion

The primary reason for the difference is depreciation expenses ($2,100), which was deducted to determine the net income, which did not involve an operating cash outflow.