Please provide the formulas that satisfy each blank on the excel sheet

B3 fx Use the information provided in column H and the assumptions in column M to build a forecast income statement and balance sheet for A B D E G H M N o P Q R S T 6 7 8 Income Statement 9 10 2021E 12,894 10,315 2,579 2022F 16,118 12,894 3,224 Iterations 2022F 2022F 16,118 16,118 12,894 12,894 3,224 3,224 2022F 16.118 12,894 3,224 Assumptions 25% 80% 11 12 13 14 1,934 1,934 1,934 Net Sales Cost of goods sold Gross profit Expenses Selling, general, and administrative expenses Net interest expense Earnings before tax Tax Earnings after tax Dividends 1,547 90 942 12% 10% 15 16 17 1,934 90 1,199 420 780 195 330 35% 18 612 153 19 25% 20 21 Balance Sheet Iterations 2022F 2022F 22 2021E 2022F 2022F 23 24 25 795 1.987 28 27 28 Assets Current assets Cash and securities Accounts receivable Inventory Prepaid expenses Total current assets Net fixed assets Total assets 636 1.590 2,579 18 4,822 18 45 4 Keep constant days days turnover 18 18 18 18 29 300 30 31 25 55 depreciation cap ex The company plans to spend $55 on new fixed assets and has a depreciation expense of $25. 5,122 32 33 34 35 38 days plug 32 Keep constant Keep constant Start with Oin bank loan for first iteration. Hold amortization of debt principle constant at $100 and keep accrued wages constant 37 Liabilities and Owners' Equity Current liabilities Bank loan Accounts payable CMLTD Accrued wages Total current liabilities Long-term debt Common stock Retained earnings Total liabilities and owners' equity 38 39 40 50 904 100 18 1,072 900 250 2,900 5,122 0 Long-term debt amortizes by 100 each year. No new common stock is issued nor repurchased. Use BASE for retained earnings. 42 44 balance 45 Plug your balance in to the next iteration's bank loan if this value is positive. If negative, subtract from bank loan. Once bank loan is zero, add to marketable securities. 48 47 48 49 B3 Jx Use the information provided in column H and the assumptions in column M to build a forecast income statement and balance sheet for 2022. iterate until your forecast balance sheet balances. S T U V w Y z AB AC AD AE AF AG AH AJ AK AL AM AN AP AQ AR AS AT AU Sources of Cash 7 7 8 9 10 11 2022F Begin with net income and depreciation. Any asset that declines or liability that increases will be a source of cash. Sum total sources of cash. Re-format the income statement to get EBIT. Using the tax rate of 35% calculate the 2022 forecasted NOPAT. 12 13 14 Net Sales Cost of goods sold Gross profit SGA Operating profit (EBIT) Interest Expense Earnings before tax Tax Earnings after tax Dividends Total sources of cash Uses of Cash 15 16 17 18 19 20 Any asset that increases or liability that declines will be a use of cash. Sum total uses of cash. 21 NOPAT 2021 2022 Working cash AR Using the final iteration values calculate the net operating working capital for 2021 and 2022. Inv AP 2021 2022 The difference between sources and uses of cash equal the external funding required, aka Additional Funding Needed (AFN) in your textbook. NOWC Net Fix 22 23 24 25 28 Total uses of cash 27 28 29 External funding required 30 ed assets and has a 31 32 33 34 35 Hold amortization of 38 crued wages constant 37 38 39 40 r. No new common 41 for retained 42 Add net fixed assets to the NOW for each year to get total operating capital for 2021 and 2022. Then subtract 2021 from 2022 to get the change in operating capital. Total Op Change in Op Cap FCF Subtract the change in operating capital from the 2022 NOPAT to get FCF. Use the check below to ensure the figures match. As a refresher, refer to problem 6-18 in your text on page 274. Uses of funds (check) 1 Pay interest 2 Buyback ST debt Buyback LT deck 3 Pay dividend 4 Buyback stock 5 Make investments 44 bank loan if this value 45 loan. Once bank loan 6 48 Total FCF This sum of the five uses of funds should match the figure above from NOPAT - changed in operating capital. 48 49 50 yool Proinnt 6 B3 fx Use the information provided in column H and the assumptions in column M to build a forecast income statement and balance sheet for A B D E G H M N o P Q R S T 6 7 8 Income Statement 9 10 2021E 12,894 10,315 2,579 2022F 16,118 12,894 3,224 Iterations 2022F 2022F 16,118 16,118 12,894 12,894 3,224 3,224 2022F 16.118 12,894 3,224 Assumptions 25% 80% 11 12 13 14 1,934 1,934 1,934 Net Sales Cost of goods sold Gross profit Expenses Selling, general, and administrative expenses Net interest expense Earnings before tax Tax Earnings after tax Dividends 1,547 90 942 12% 10% 15 16 17 1,934 90 1,199 420 780 195 330 35% 18 612 153 19 25% 20 21 Balance Sheet Iterations 2022F 2022F 22 2021E 2022F 2022F 23 24 25 795 1.987 28 27 28 Assets Current assets Cash and securities Accounts receivable Inventory Prepaid expenses Total current assets Net fixed assets Total assets 636 1.590 2,579 18 4,822 18 45 4 Keep constant days days turnover 18 18 18 18 29 300 30 31 25 55 depreciation cap ex The company plans to spend $55 on new fixed assets and has a depreciation expense of $25. 5,122 32 33 34 35 38 days plug 32 Keep constant Keep constant Start with Oin bank loan for first iteration. Hold amortization of debt principle constant at $100 and keep accrued wages constant 37 Liabilities and Owners' Equity Current liabilities Bank loan Accounts payable CMLTD Accrued wages Total current liabilities Long-term debt Common stock Retained earnings Total liabilities and owners' equity 38 39 40 50 904 100 18 1,072 900 250 2,900 5,122 0 Long-term debt amortizes by 100 each year. No new common stock is issued nor repurchased. Use BASE for retained earnings. 42 44 balance 45 Plug your balance in to the next iteration's bank loan if this value is positive. If negative, subtract from bank loan. Once bank loan is zero, add to marketable securities. 48 47 48 49 B3 Jx Use the information provided in column H and the assumptions in column M to build a forecast income statement and balance sheet for 2022. iterate until your forecast balance sheet balances. S T U V w Y z AB AC AD AE AF AG AH AJ AK AL AM AN AP AQ AR AS AT AU Sources of Cash 7 7 8 9 10 11 2022F Begin with net income and depreciation. Any asset that declines or liability that increases will be a source of cash. Sum total sources of cash. Re-format the income statement to get EBIT. Using the tax rate of 35% calculate the 2022 forecasted NOPAT. 12 13 14 Net Sales Cost of goods sold Gross profit SGA Operating profit (EBIT) Interest Expense Earnings before tax Tax Earnings after tax Dividends Total sources of cash Uses of Cash 15 16 17 18 19 20 Any asset that increases or liability that declines will be a use of cash. Sum total uses of cash. 21 NOPAT 2021 2022 Working cash AR Using the final iteration values calculate the net operating working capital for 2021 and 2022. Inv AP 2021 2022 The difference between sources and uses of cash equal the external funding required, aka Additional Funding Needed (AFN) in your textbook. NOWC Net Fix 22 23 24 25 28 Total uses of cash 27 28 29 External funding required 30 ed assets and has a 31 32 33 34 35 Hold amortization of 38 crued wages constant 37 38 39 40 r. No new common 41 for retained 42 Add net fixed assets to the NOW for each year to get total operating capital for 2021 and 2022. Then subtract 2021 from 2022 to get the change in operating capital. Total Op Change in Op Cap FCF Subtract the change in operating capital from the 2022 NOPAT to get FCF. Use the check below to ensure the figures match. As a refresher, refer to problem 6-18 in your text on page 274. Uses of funds (check) 1 Pay interest 2 Buyback ST debt Buyback LT deck 3 Pay dividend 4 Buyback stock 5 Make investments 44 bank loan if this value 45 loan. Once bank loan 6 48 Total FCF This sum of the five uses of funds should match the figure above from NOPAT - changed in operating capital. 48 49 50 yool Proinnt 6