Using excel formulas

Q1. Serious Products Company wants you to prepare a 2020 Pro Forma Income Statement (PFIS) and a 2020 Pro Forma Balance Sheet (PFBS) using the 2019 and 2018 data. The templates for preparing the documents are given in the Assignment 2 workbook. The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2020. Cost of goods sold and S,G&A expense in 2020 are expected to be the average of their two-year proportion of sales. On the balance sheet, accounts receivable, inventory, accounts payable, and accrued expenses are expected to be at the two-year average of the proportion of these items in relation to sales. The firm has planned an investment of $500,000 in fixed assets in 2020, with an estimated life of 10 years and no salvage value. These fixed assets will be depreciated using the straight line depreciation method. All other financial statement items are expected to remain constant in 2020. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2020 will be the same as those paid in 2019. a) What is the Discretionary Financing Needed (DFN) in 2020? Use an IF function to determine if this a surplus, deficit, or balance. Print the PFIS and PFBS. b) Use the Scenario Manager to set up three scenarios: 1. Best Case Sales are 15% higher than expected. 2. Base Case Sales are exactly as expected. 3. Worst Case Sales are 15% less than expected. Perform the scenario analysis that includes the scenarios, the DFN and whether the DFN is surplus, deficit or balanced. Include your name in the summary results and check your printout is meaningful. Nm 5 A B D 2 Q1a (PFIS) 3 Serious Products Company 4 Pro Forma Income statement 5 For the Period Ended Dec. 31, 2019 6 2020* 2019 2018 7 Sales 6,035,000 6,148,000 5,134,000 8 Cost of Goods Sold 4,060,897 4,176,000 3,422,000 9 Gross Profit 1,974,103 1,972,000 1,712,000 10 Selling and G&A Expenses 635,368 588,000 590,000 11 Fixed Expenses 70,000 70,000 70,000 12 Depreciation Expense 478,000 478,000 446,000 13 EBIT 790,735 836,000 606,000 14 Interest Expense 186,000 186,000 182,000 15 Earnings Before Taxes 604,735 650,000 424,000 16 Taxes 241,894 195,000 127,200 17 Net Income 362,841 455,000 296,800 18 *Forecast 19 20 Notes 21 Tax Rate 40% 30% 30% 22 23 Additional Depreciation 24 Short-Term Interest Rate 4% 25 Long-term Interest Rate 7% 26 Scenario Multiplier for part b 85% 27 DFN for Scenario Summary 4 JU 8 A B Pro Forma Income statement 5 For the Period Ended Dec. 31, 2019 6 Assets 2020* 2019 2018 Cash and Equivalents 862,000 678,000 Accounts Receivable 1,006,000 730,000 9 Inventory 578,000 600,000 10 Total Current Assets 2,446,000 2,008,000 11 Plant & Equipment 9,338,000 8,644,000 12 Accumulated Depreciation 4,590,000 4,112,000 13 Net Fixed Assets 4,748,000 4,532,000 14 Total Assets 7,194,000 6,540,000 15 Liabilities and Owners' Equity 16 Accounts Payable 764,000 540,000 17 Short-term Notes Payable 158,000 198,000 18 Accrued Expenses 318,000 228,000 19 Total Current Liabilities 1,240,000 966,000 Long-Term Debt 2,046,000 1,934,000 21 Total Liabilities 3,286,000 2,900,000 22 Common Stock 1,638,000 1,616,000 23 Retained Earnings 2,270,000 2,024,000 24 Total Shareholder's Equity 3,908,000 3,640,000 25 Total Liabilities and Owners' Equity 7,194,000 6,540,000 20 26 27 *Forecast 28 Discretionary Financing Needed 29 30 2019 Dividends 31 Net Addition to Plan and Equipment 32 Life of New Equipment in Years 33 Salvage Value of New Equipment 34 New Depreciation (Straight Line) Q1. Serious Products Company wants you to prepare a 2020 Pro Forma Income Statement (PFIS) and a 2020 Pro Forma Balance Sheet (PFBS) using the 2019 and 2018 data. The templates for preparing the documents are given in the Assignment 2 workbook. The firm has forecasted sales of $7,100,000 and a tax rate of 40% for 2020. Cost of goods sold and S,G&A expense in 2020 are expected to be the average of their two-year proportion of sales. On the balance sheet, accounts receivable, inventory, accounts payable, and accrued expenses are expected to be at the two-year average of the proportion of these items in relation to sales. The firm has planned an investment of $500,000 in fixed assets in 2020, with an estimated life of 10 years and no salvage value. These fixed assets will be depreciated using the straight line depreciation method. All other financial statement items are expected to remain constant in 2020. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Assume that the dividends in 2020 will be the same as those paid in 2019. a) What is the Discretionary Financing Needed (DFN) in 2020? Use an IF function to determine if this a surplus, deficit, or balance. Print the PFIS and PFBS. b) Use the Scenario Manager to set up three scenarios: 1. Best Case Sales are 15% higher than expected. 2. Base Case Sales are exactly as expected. 3. Worst Case Sales are 15% less than expected. Perform the scenario analysis that includes the scenarios, the DFN and whether the DFN is surplus, deficit or balanced. Include your name in the summary results and check your printout is meaningful. Nm 5 A B D 2 Q1a (PFIS) 3 Serious Products Company 4 Pro Forma Income statement 5 For the Period Ended Dec. 31, 2019 6 2020* 2019 2018 7 Sales 6,035,000 6,148,000 5,134,000 8 Cost of Goods Sold 4,060,897 4,176,000 3,422,000 9 Gross Profit 1,974,103 1,972,000 1,712,000 10 Selling and G&A Expenses 635,368 588,000 590,000 11 Fixed Expenses 70,000 70,000 70,000 12 Depreciation Expense 478,000 478,000 446,000 13 EBIT 790,735 836,000 606,000 14 Interest Expense 186,000 186,000 182,000 15 Earnings Before Taxes 604,735 650,000 424,000 16 Taxes 241,894 195,000 127,200 17 Net Income 362,841 455,000 296,800 18 *Forecast 19 20 Notes 21 Tax Rate 40% 30% 30% 22 23 Additional Depreciation 24 Short-Term Interest Rate 4% 25 Long-term Interest Rate 7% 26 Scenario Multiplier for part b 85% 27 DFN for Scenario Summary 4 JU 8 A B Pro Forma Income statement 5 For the Period Ended Dec. 31, 2019 6 Assets 2020* 2019 2018 Cash and Equivalents 862,000 678,000 Accounts Receivable 1,006,000 730,000 9 Inventory 578,000 600,000 10 Total Current Assets 2,446,000 2,008,000 11 Plant & Equipment 9,338,000 8,644,000 12 Accumulated Depreciation 4,590,000 4,112,000 13 Net Fixed Assets 4,748,000 4,532,000 14 Total Assets 7,194,000 6,540,000 15 Liabilities and Owners' Equity 16 Accounts Payable 764,000 540,000 17 Short-term Notes Payable 158,000 198,000 18 Accrued Expenses 318,000 228,000 19 Total Current Liabilities 1,240,000 966,000 Long-Term Debt 2,046,000 1,934,000 21 Total Liabilities 3,286,000 2,900,000 22 Common Stock 1,638,000 1,616,000 23 Retained Earnings 2,270,000 2,024,000 24 Total Shareholder's Equity 3,908,000 3,640,000 25 Total Liabilities and Owners' Equity 7,194,000 6,540,000 20 26 27 *Forecast 28 Discretionary Financing Needed 29 30 2019 Dividends 31 Net Addition to Plan and Equipment 32 Life of New Equipment in Years 33 Salvage Value of New Equipment 34 New Depreciation (Straight Line)