Excel Project - Financial Statement Forecasting Posted with these instructions is a forecasting template which includes the 2022 financial statements for Ted and Elaines Airplane Parts. Inc. Ted and Elaine, the owners, have invented a part to reduce fuel usage in planes by 10%. They are investing in a new plant on the west coast as well as in a national marketing campaign. They need to determine their capital (financing) requirements. Your task is to forecast the 2023, 2024 and 2025 financial statements (Income Statement, Statement of Retained Earnings (This is on the same tab as the Income Statement and directly below it), Balance Sheet and Statement of Cash Flows) using the posted spreadsheet and the following information: Unit sales in 2023 will be 300,000 units and increase by 25% in 2024 and then by 10% in 2025. All sales are credit sales. Unit costs for inventory in 2023 will be $45.00 and increase by 5% in 2024 and 4% in 2025 Target Gross Margin is to be 65% in 2023 and 60% in 2024 and 2025. Salaries are expected to be $7,000,000 in 2023, $7,500,000 on 2024 and $8,500,000 in 2025 Benefits are expected to be 25% of salaries in all years Marketing will be 30% of sales in 2023 and 25% of sales in 2024 and 15% of sales in 2025 General and Administrative expenses (G&A) will be 4% of sales in 2023 and 2024 and 3% of sales in 2025 Depreciation expense will increase by $200,000 in 2023, $100,000 in 2024 (from 2023) and $20,000 in 2025 (from 2024) Interest income will be $429 in 2023, $7,330 in 2024 and $35,771 in 2025 Interest Expense on long term notes payable will be $41,250 in 2023, $58,125 in 2024 and $54,375 in 2025. Tax Rate is 35% for all years DSO is targeted to be 50 days in 2023, 38 days in 2024 and 32 days in 2025 DSI is targeted to be 95 days in 2023 and 75 days in 2024 and 45 days in 2025 Prepaids will be 2% of prior year current assets for all years Other Current Assets will be 1% of prior year current assets in 2023 and 3% in 2024 and 2025 Days in payable will be 35 days in all three years Other current liabilities will be 4% of prior years current liabilities for all years Capital purchases (purchases of fixed assets) will be $2,000,000 in 2023, $1,000,000 in 2024 and $200,000 in 2025. In 2023, long term notes payable will increase by $750,000 and then decrease by $75,000 per year in 2024 and 2025 Dividend payout ratio is 10% in 2023 and 20% in 2024 and 35% in 2025 You will issue (sell) 50,000 common shares in 2023 for a price of $20 per share. There is no change in shares outstanding in 2024 and 2025 Any shortfall in the cash will be filled with Short Term Notes Payable. Borrow in increments of $50,000. Interest expense on the short term notes is 3.5% of average balance and is in addition to the long term interest given above. This is considered investing activities for cash flow purposes and should be included in the increase in debt or repayments of debt lines. Remember, if you have any cash surplus at the end of the year, you need to pay off short term notes payable if there is a balance. Round to the nearest dollar except for per share amounts, which should be rounded to the nearest penny. You are to submit the completed spreadsheets though the iLearn Assignments module. You will be graded on accuracy and use of Excel. It is expected that all numbers in the financial statements will be formulas. Use the input tab to put in variables. The input tab is not write protected. The financial statement tabs are and you can only put formulas in the blue area. HINT: Complete the 2023 forecast and then copy over to the following years. You may have to make adjustments to interest expense after you do this, depending on the borrowing situation. This is due on or before Tuesday March 7. The spreadsheet contains the 2022 actual results.

Income Statements For the Years Ending December 31 \begin{tabular}{|l|l|l|l|} \hline 2022 & 2023 & 2024 & 2025 \\ \hline \end{tabular} Sales 21,639,935 Cost of Sales Gross Profit \begin{tabular}{|r|} \hline 8,655,974 \\ \hline 12,983,961 \\ \hline \end{tabular} Salaries and Benefits Sales and Marketing General and Administration Depreciation 5,564,8532,163,9941,896,554450,000 Operating Expenses Operating Profit \begin{tabular}{|r|} \hline 10,075,401 \\ \hline 2,908,560 \\ \hline \end{tabular} (24,563) 2,883,997 Income Taxes 1,153,599 Net Income 1,730,398 Earnings Per Share $3.46 Shares Outstanding 500,000 Ted and Elaine's Airplane Parts, Inc. Statement of Retained Earnings December 31 2022 2023 2024 2025 \begin{tabular}{l|c|} \hline Retained Earnings, Beginning & 340,185 \\ \hline Net Income / (Loss) & 1,730,398 \\ \hline Dividends & (692,159) \\ \hline Retained Earnings, End & 1,378,424 \\ \hline \end{tabular} Balance Sheets December 31 \begin{tabular}{|l|l|l|l|} \hline 2022 & 2023 & 2024 & 2025 \\ \hline \end{tabular} Current Assets \begin{tabular}{|l|r|r|} \hline Cash & 160,352 & s160,352 \\ \hline Accounts Receivable & 1,321,598 \\ \hline Inventory & 1,174,754 \\ \hline Prepaid Expenses & 35,674 \\ \hline Other Current Assets & 23,782 \\ \hline Total Current Assets & 2,716,160 \\ \hline \end{tabular} Net Property and Equipment Total Assets \begin{tabular}{rr} & 681,000 \\ \hline & \\ $3,397,160 \\ \hline \hline \end{tabular} Liabilities and Net Worth Current Liabilities Accounts Payable and Accrued Expenses Short Term Notes Payable Other Current Liabilities Total Current Liabilities 23,783568,736 Long Term Liabilities Notes Payable 450,000 Total Liabilities 1,018,736 Shareholders' Equity \begin{tabular}{|l|r|} \hline Capital Stock & 1,000,000 \\ \hline Retained Earnings & 1,378,424 \\ \hline \multicolumn{1}{|l}{ Total Shareholders' Equity } & 2,378,424 \\ \cline { 2 - 2 } & \\ \hline Total Liabilities and Net Worth & $3,397,160 \\ \hline \end{tabular} Statement of Cash Flows For the Years Ending December 31 2022202320242025 Cash Flows from Operations Vet Income Add: Depreciation $$,730,398450,000 Changes in Working Capital Increase) Decrease in Accounts Rec Increase) Decrease Inventory Increase) Decrease Prepaids Increase) Decrease Other Current A ncrease (Decrease) in Accounts Pay ncrease (Decrease) in Other Current Change in Operating Working Capita Total Cash Flows from Operations (52,793) (879,413) (827,848) (17,232) 421,601 Cash Flows from Investing Activities Durchase of Fixed Assets (40,000) Total Cash Flows from Investing Activities (40,000) Cash Flows from Financing Activities ncrease in Debt Repayments of Debt Stock Issuance Dividends Paid (692,159) Total Cash Flows from Financing Activities (692,159) Change in Cash and Investments (310,558) Cash and Investments Beginning Cash and Investments End $160,352 Income Statements For the Years Ending December 31 \begin{tabular}{|l|l|l|l|} \hline 2022 & 2023 & 2024 & 2025 \\ \hline \end{tabular} Sales 21,639,935 Cost of Sales Gross Profit \begin{tabular}{|r|} \hline 8,655,974 \\ \hline 12,983,961 \\ \hline \end{tabular} Salaries and Benefits Sales and Marketing General and Administration Depreciation 5,564,8532,163,9941,896,554450,000 Operating Expenses Operating Profit \begin{tabular}{|r|} \hline 10,075,401 \\ \hline 2,908,560 \\ \hline \end{tabular} (24,563) 2,883,997 Income Taxes 1,153,599 Net Income 1,730,398 Earnings Per Share $3.46 Shares Outstanding 500,000 Ted and Elaine's Airplane Parts, Inc. Statement of Retained Earnings December 31 2022 2023 2024 2025 \begin{tabular}{l|c|} \hline Retained Earnings, Beginning & 340,185 \\ \hline Net Income / (Loss) & 1,730,398 \\ \hline Dividends & (692,159) \\ \hline Retained Earnings, End & 1,378,424 \\ \hline \end{tabular} Balance Sheets December 31 \begin{tabular}{|l|l|l|l|} \hline 2022 & 2023 & 2024 & 2025 \\ \hline \end{tabular} Current Assets \begin{tabular}{|l|r|r|} \hline Cash & 160,352 & s160,352 \\ \hline Accounts Receivable & 1,321,598 \\ \hline Inventory & 1,174,754 \\ \hline Prepaid Expenses & 35,674 \\ \hline Other Current Assets & 23,782 \\ \hline Total Current Assets & 2,716,160 \\ \hline \end{tabular} Net Property and Equipment Total Assets \begin{tabular}{rr} & 681,000 \\ \hline & \\ $3,397,160 \\ \hline \hline \end{tabular} Liabilities and Net Worth Current Liabilities Accounts Payable and Accrued Expenses Short Term Notes Payable Other Current Liabilities Total Current Liabilities 23,783568,736 Long Term Liabilities Notes Payable 450,000 Total Liabilities 1,018,736 Shareholders' Equity \begin{tabular}{|l|r|} \hline Capital Stock & 1,000,000 \\ \hline Retained Earnings & 1,378,424 \\ \hline \multicolumn{1}{|l}{ Total Shareholders' Equity } & 2,378,424 \\ \cline { 2 - 2 } & \\ \hline Total Liabilities and Net Worth & $3,397,160 \\ \hline \end{tabular} Statement of Cash Flows For the Years Ending December 31 2022202320242025 Cash Flows from Operations Vet Income Add: Depreciation $$,730,398450,000 Changes in Working Capital Increase) Decrease in Accounts Rec Increase) Decrease Inventory Increase) Decrease Prepaids Increase) Decrease Other Current A ncrease (Decrease) in Accounts Pay ncrease (Decrease) in Other Current Change in Operating Working Capita Total Cash Flows from Operations (52,793) (879,413) (827,848) (17,232) 421,601 Cash Flows from Investing Activities Durchase of Fixed Assets (40,000) Total Cash Flows from Investing Activities (40,000) Cash Flows from Financing Activities ncrease in Debt Repayments of Debt Stock Issuance Dividends Paid (692,159) Total Cash Flows from Financing Activities (692,159) Change in Cash and Investments (310,558) Cash and Investments Beginning Cash and Investments End $160,352