12 31 21 Balance Sheet Cash $17,000 Accounts Receivable $12,000 Prepaid Insurance $5,000 Inventory $15,000 $49,000 Equipment $100,000 Accumulated Depreciation $(20,000) $80,000 Total Assets $129,000 Accounts Payable $9,000 Income Taxes Payable $3,000 Total Liabilities $12,000 Common Stock $100,000 Retained Earnings $17,000 Total Equity $117,000 Total Liabilities Equity $129,000 Additional Information Sales for 2022 are expected to be $210,000 Accounts Receivable turnover is expected to be 12 times 30 days of sales in accounts receivable out of a 360 day year (1 12 of annual sales) This would be used to get ending accounts receivable on the 2022 balance sheet days sales in accounts receivable is ending accounts receivable divided by average sales (sales for 2022 divided by 360 days) We can back into ending accounts receivables once we have estimated sales Note that the turnover ratio changes so the turnover ratio at the end of 2021 may have been different than that expected at the end of 2022 Gross Margin ratio is expected to be 40 percent Inventory Turnover is expected to be 12 times 30 days of cost of sales in ending inventory out of a 360 day year (based upon cost of goods sold and ending 2022 inventory) This would be used to get inventory on the 2022 balance sheet See accounts receivable above for similar computations The cost of ending inventory is expected to be paid next month ending accounts payable will be same as ending inventory Or, to state in another way, accounts payable turnover is same as the inventory turnover The assumption is that only inventory purchases flow through accounts payable the assumption actually used by most manufacturing merchandising companies when prepared the statement of cash flows Equipment was purchased on 1 1 22 for $20,000 Equipment has a five year life, no salvage value, and is depreciated using the straight line method The old equipment is being depreciated on the same basis Salaries are expected to be $2,000 per month It is expected that one half month will be owed on 12 31 22 because of when payday falls $30,000 in cash was borrowed on 12 31 22 by issuing a Note Payable Insurance costing $18,000 was purchased on 6 1 22 (the same time in which the policy purchased in 2021 expired the new policy was for 12 months) The tax rate is 30 percent Income taxes for the current year are payable during the first two months of the next year Dividends of $3,000 were paid during 2022 Instructions Prepare an Income Statement After you are completed, a corrected Income Statement should be completed by your spreadsheet automatically with only a change in any of the assumptions that will be within spreadsheet one Prepare a Statement of Retained Earnings This statement should automatically change if any of the assumptions are changed within spreadsheet one Prepare a Balance Sheet without cash yet known Have the Balance Sheet for 12 31 21 (given in template) and 12 31 22 on the same schedule so that the differences can be easily computed for instruction 4 When you are finished, a corrected Balance Sheet for 2022 should automatically be computed by your spreadsheet with a change in any of the assumptions within spreadsheet one Prepare a Statement of Cash Flows on the direct method (do not include the indirect method of calculating operating cash flows) The Statement of Cash Flows should automatically change when any assumption is changed The ending cash as shown on the statement of cash flows will then flow to the Balance Sheet On spreadsheet A have only the following Assumptions Sales $210,000 Equipment Purchases $20,000 Salaries per Month $2,000 Twelve month insurance policy purchased $18,000 Dividends paid $3,000 Borrowings $30,000 Have the Income Statement and Retained Earnings Statement on spreadsheet B Use spreadsheet C for the comparative Balance Sheet and Spreadsheet D for the Statement of Cash Flows When you believe your spreadsheets from instructions 1 through 4 are complete, then save that spreadsheet within a file Reopen that file and make the following changes On the spreadsheet A for the assumptions change sales to $215,000, equipment purchases to 25,000, insurance purchased to $36,000, borrowings to $40,000, and dividends to $1,000 You should see all the financial statements change automatically to a new balanced balance sheet If not, then you did not use a formula where needed within at least one cell When the changes are complete, use the save as function and save in a new file with a different name

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 19, 2024

12/31/21 Balance Sheet Cash $17,000 Accounts Receivable $12,000 Prepaid Insurance $5,000 Inventory $15,000 $49,000 Equipment $100,000 Accumulated Depreciation $(20,000) $80,000 Total Assets $129,000 Accounts Payable

12/31/21 Balance Sheet

Cash $17,000

Accounts Receivable $12,000

Prepaid Insurance $5,000

Inventory $15,000

$49,000

Equipment $100,000

Accumulated Depreciation $(20,000)

$80,000

Total Assets $129,000

Accounts Payable $9,000

Income Taxes Payable $3,000

Total Liabilities $12,000

Common Stock $100,000

Retained Earnings $17,000

Total Equity $117,000

Total Liabilities & Equity $129,000

Additional Information:

Sales for 2022 are expected to be $210,000.
Accounts Receivable turnover is expected to be 12 times - 30 days of sales in accounts receivable out of a 360 day year (1/12 of annual sales). This would be used to get ending accounts receivable on the 2022 balance sheet days sales in accounts receivable is ending accounts receivable divided by average sales (sales for 2022 divided by 360 days). We can back into ending accounts receivables once we have estimated sales. Note that the turnover ratio changes so the turnover ratio at the end of 2021 may have been different than that expected at the end of 2022.
Gross Margin ratio is expected to be 40 percent.
Inventory Turnover is expected to be 12 times - 30 days of cost of sales in ending inventory out of a 360 day year (based upon cost of goods sold and ending 2022 inventory). This would be used to get inventory on the 2022 balance sheet. See accounts receivable above for similar computations.
The cost of ending inventory is expected to be paid next month ending accounts payable will be same as ending inventory. Or, to state in another way, accounts payable turnover is same as the inventory turnover. The assumption is that only inventory purchases flow through accounts payable the assumption actually used by most manufacturing/merchandising companies when prepared the statement of cash flows.
Equipment was purchased on 1/1/22 for $20,000.Equipment has a five-year life, no salvage value, and is depreciated using the straight-line method. The old equipment is being depreciated on the same basis.
Salaries are expected to be $2,000 per month. It is expected that one-half month will be owed on 12/31/22 because of when payday falls.
$30,000 in cash was borrowed on 12/31/22 by issuing a Note Payable.
Insurance costing $18,000 was purchased on 6/1/22 (the same time in which the policy purchased in 2021 expired - the new policy was for 12 months).
The tax rate is 30 percent. Income taxes for the current year are payable during the first two months of the next year.
Dividends of $3,000 were paid during 2022.

Instructions

Prepare an Income Statement. After you are completed, a corrected Income Statement should be completed by your spreadsheet automatically with only a change in any of the assumptions that will be within spreadsheet one.

Prepare a Statement of Retained Earnings. This statement should automatically change if any of the assumptions are changed within spreadsheet one.

Prepare a Balance Sheet without cash yet known. Have the Balance Sheet for 12/31/21 (given in template) and 12/31/22 on the same schedule so that the differences can be easily computed for instruction 4. When you are finished, a corrected Balance Sheet for 2022 should automatically be computed by your spreadsheet with a change in any of the assumptions within spreadsheet one.

Prepare a Statement of Cash Flows on the direct method (do not include the indirect method of calculating operating cash flows). The Statement of Cash Flows should automatically change when any assumption is changed. The ending cash as shown on the statement of cash flows will then flow to the Balance Sheet.

On spreadsheet A have only the following:

Assumptions

Sales - $210,000

Equipment Purchases - $20,000

Salaries per Month - $2,000

Twelve-month insurance policy purchased - $18,000

Dividends paid - $3,000

Borrowings - $30,000

Have the Income Statement and Retained Earnings Statement on spreadsheet B. Use spreadsheet C for the comparative Balance Sheet and Spreadsheet D for the Statement of Cash Flows.

When you believe your spreadsheets from instructions 1 through 4 are complete, then save that spreadsheet within a file. Reopen that file and make the following changes:

On the spreadsheet A for the assumptions change sales to $215,000, equipment purchases to 25,000, insurance purchased to $36,000, borrowings to $40,000, and dividends to $1,000. You should see all the financial statements change automatically to a new balanced balance sheet. If not, then you did not use a formula where needed within at least one cell.When the changes are complete, use the save as function and save in a new file with a different name.