Forecast the financial statements (balance sheet, income statement, statement of cash flows) for Spinning Wheel for 2022 and 2023. You should explicitly indicate as a separate column on your worksheet the assumptions you are making to forecast each line item. To simplify this exercise, you can assume that this business was not affected by COVID-19 historically and therefore no COVID-19 adjustments to the financial statements are needed.

Requirment:

You can assume existing financial statement relationships will continue except for the following items:

1. Sales are projected to increase 20% for both 2022 and 2023
2. A new warehouse will be constructed in 2022 and 2023, with $2.4 million of costs expended in 2023 and $200,000 expended in January 2023. The building will be placed in service in January 2023 and 5% of its cost will be depreciated in 2023. You can assume that construction costs will be incurred evenly during 2022 starting in January.
3. Constructing the new warehouse will require a reduction in inventory of $500,000 in 2022, which will be replaced in 2023 to restore historical relationships of inventory and sales.
4. The cost of the warehouse will be financed through a construction loan at 8% interest, with interest accruing quarterly and paid as accrued. Assume that drawings from the loan are made monthly – treat it like a line of credit – until the final amount is drawn in January 2023 and the repayment term starts. The loan will be repaid in four annual installments beginning July 2023.
5. Tax rates for the period should approximate 40%, and dividends should be paid consistent with historical levels.
6. Existing debt will continue to be paid consistent with history
7. If additional funding is required, it will be obtained through line of credit borrowings with interest at 12%, interest payable at the beginning of the subsequent year, no principal payments required.
8. If excess cash is forecast (greater than $1,000,000) it can be invested in marketable securities yielding 6% interest.
9. 

Transcribed Image Text:

Paste 326 Clear 1 2 3 Income Statement 4 Net Sales S Cost of Sales 6 Gross Profit 7 Sellis 7 8 Depreciation 9 Net Interest Expense 10 Pretax Income 11 Income Taxes 12 Net Income 13 Dividends 14 Change in R/E 15 + X ♥- Selling General Admin PEN BI U 16 Balance Sheet 20 17 Assets 18 Cash 19 A/R WA 20 Inventories 21 Total Current Assets 22 Plant & Equipment 23 Accum. Depreciation 24 Net Plant & Equipment 25 Total Assets 26 27 Liabilities 37 28 Current LT Debt 29 A/P 30 Accrued Expenses 31 Total Current Liabilities 32 Long-Term Debt 33 Total Liabilities 34 Common Stock ($5 Par) 35 Retained Earnings 36 Total Shareholders Equity 37 Total Liab & Equity 38 39 Market Price of Stock at Y/E 40 H 41 Statement of Cash Flows 42 Cash Flows from Operations 43 Net Income 44 Depreciation Expense 45 Change in Current Accounts 46 Net Cash from Operations 47 144 Normal View fx B 2019 16,230 9,430 6,800 5,195 160 119 1,326 546 780 155 625 2019 508 2,545 1,630 4,683 3,232 1,335 1,897 6,580 125 1,042 1.145 2,312 1,000 3,312 1,135 2,133 3,268 6,580 Instructions Ready 59 2019 D E Spinning Wheel Tire Company 2020 780 160 (280) 660 20,355 11,898 8,457 6,352 180 106 1,819 822 997 200 797 2020 609 3,095 1,838 5,542 3,795 1,515 2,280 7,822 125 1,325 1,432 2,882 875 3,757 1,135 2,930 4,065 7,822 61 2020 EEEEE 997 180 (188) 989 F 2021 23,505 13,612 9,893 7,471 213 94 2,115 T 925 1,190 240 950 2021 706 3,652 2,190 6,548 4,163 1,728 2,435 8,983 125 1,440 1,653 3,218 750 3,968 1,135 3,880 5,015 8,983 2021 65 1,190 213 (573) 830 G Merge H %> ,00 C-:.0 .00 .0 Statement of Cash Flows Cont'd Cash flows from Investing Change in PP&E Net Cash from Investing Cash Flows from Financing Change in Long Term Debt Dividends Net Cash from Financing Change in Cash Cash Beginning of Year Cash End of Year History and Ratio Analysis and Ratio Analysis Forecast Model and Ratios CIP and Debt Assumptions History + Conditional Formatting Sum-0 MacBook Air K 2019 (400) (400) (125) (155) (280) (20) 528 508 Neutral L M 2020 Calculation (563) (563) (125) (200) (325) 101 508 609 N Check Cell O 2021 (368) (368) (125) (240) (365) 97 609 706 P O Paste 326 Clear 1 2 3 Income Statement 4 Net Sales S Cost of Sales 6 Gross Profit 7 Sellis 7 8 Depreciation 9 Net Interest Expense 10 Pretax Income 11 Income Taxes 12 Net Income 13 Dividends 14 Change in R/E 15 + X ♥- Selling General Admin PEN BI U 16 Balance Sheet 20 17 Assets 18 Cash 19 A/R WA 20 Inventories 21 Total Current Assets 22 Plant & Equipment 23 Accum. Depreciation 24 Net Plant & Equipment 25 Total Assets 26 27 Liabilities 37 28 Current LT Debt 29 A/P 30 Accrued Expenses 31 Total Current Liabilities 32 Long-Term Debt 33 Total Liabilities 34 Common Stock ($5 Par) 35 Retained Earnings 36 Total Shareholders Equity 37 Total Liab & Equity 38 39 Market Price of Stock at Y/E 40 H 41 Statement of Cash Flows 42 Cash Flows from Operations 43 Net Income 44 Depreciation Expense 45 Change in Current Accounts 46 Net Cash from Operations 47 144 Normal View fx B 2019 16,230 9,430 6,800 5,195 160 119 1,326 546 780 155 625 2019 508 2,545 1,630 4,683 3,232 1,335 1,897 6,580 125 1,042 1.145 2,312 1,000 3,312 1,135 2,133 3,268 6,580 Instructions Ready 59 2019 D E Spinning Wheel Tire Company 2020 780 160 (280) 660 20,355 11,898 8,457 6,352 180 106 1,819 822 997 200 797 2020 609 3,095 1,838 5,542 3,795 1,515 2,280 7,822 125 1,325 1,432 2,882 875 3,757 1,135 2,930 4,065 7,822 61 2020 EEEEE 997 180 (188) 989 F 2021 23,505 13,612 9,893 7,471 213 94 2,115 T 925 1,190 240 950 2021 706 3,652 2,190 6,548 4,163 1,728 2,435 8,983 125 1,440 1,653 3,218 750 3,968 1,135 3,880 5,015 8,983 2021 65 1,190 213 (573) 830 G Merge H %> ,00 C-:.0 .00 .0 Statement of Cash Flows Cont'd Cash flows from Investing Change in PP&E Net Cash from Investing Cash Flows from Financing Change in Long Term Debt Dividends Net Cash from Financing Change in Cash Cash Beginning of Year Cash End of Year History and Ratio Analysis and Ratio Analysis Forecast Model and Ratios CIP and Debt Assumptions History + Conditional Formatting Sum-0 MacBook Air K 2019 (400) (400) (125) (155) (280) (20) 528 508 Neutral L M 2020 Calculation (563) (563) (125) (200) (325) 101 508 609 N Check Cell O 2021 (368) (368) (125) (240) (365) 97 609 706 P O

Answer rating: 100% (QA)

In the bookss of YY Ltd Less Less Less Consolidated Statement of NOTE Comprehensive i... View the full answer