Chapter 5 Big Bob's, Inc., wants you to prepare a 2018 Pro Forma Income Statement and a 2018 Balance Sheet using the following 2017 and 2016 data: Fashion Trends, Inc. Fashion Trends, Inc. Balance Sheet Balance Sheet For the Period Ended Dec. 31, 2017 As of Dec. 31, 2017 2017 2016 Assets 2017 2016 Sales 6,148,000 5,134,000 Cash and Equivalents 862,000 678,000 Cost of Goods Sold 4,176,000 3,422,000 Accounts Receivable 1,006,000 730,000 Gross Profit 1,972,000 1,712,000 Inventory 578,000 600,000 S. G&A Expenses 588,000 590,000 Total Current Assets 2,446,000 2,008,000 Fixed Expenses 70,000 70,000 Plant & Equipment 9,338,000 8,644,000 Depreciation Expense 478,000 446,000 Accumulated Depreciation 4,590,000 4,112,000 EBIT 836,000 606,000 Net Fixed Assets 4,748,000 4,532,000 Interest Expense 186,000 182,000 Total Assets 7,194.000 6,540,000 Earnings Before Taxes 650,000 424,000 Liabilities and Owners' Equity Taxes 195,000 127,200 Accounts Payable 764,000 540,000 Net Income 455,000 296,800 Short-term Notes Payable 158,000 198,000 Accrued Expenses 318,000 228,000 Total Current Liabilities 1,240,000 966,000 Long-term Debt 2,046,000 1,934,000 Total Liabilities 3,286,000 2,900,000 Common Stock 1,638,000 1,616,000 Retained Earnings 2,270,000 2,024,000 Total Shareholder's Equity 3.908,000 3,640,000 Total Liabilities and Owners' Equity 7,194,000 6,540,000 The firm has forecasted sales of $7,800,000 and a tax rate of 30% for 2018. Cost of goods sold and SG&A expense in 2018 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 2018, 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 2018 from 2017. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Calculate interest on an average debt outstanding for the year. Assume that the dividends in 2018 will be the same as those paid in 2017. What is the Discretionary Financing Needed (DFN) in 2018? Is this a surplus or deficit? Build a formula to determine which it is. DFN will be absorbed by long-term debt. Set up an iterative worksheet to eliminate it. D E F G Name: 2016 B 1 Big Bob's Inc. 2 Balance Sheet 3 ProformaFor the Period Ended Dec. 31, 2018 4 2018* 2017 5 Sales 6 Cost of Goods Sold 7 Gross Profit 8 Selling and G&A Expenses 9 Fixed Expenses 10 Depreciation Expense 11 EBIT 12 Interest Expense 13 Earnings Before Taxes 14 Taxes 15 Net Income 16 17 * Forecast 18 19 Notes 20 Tax Rate 21 22 Additional Depreciation 23 Sorth-term Interest Rate 24 Long-term Interest Rate 25 26 27 28 Big Bob's, Inc. 29 Balance Sheet 30 Proforma As of Dec. 31, 2018 31 Assets 2018* 2017 32 Cash and Equivalents 33 Accounts Receivable 34 Inventory 35 Total Current Assets 36 Plant & Equipment 37 Accumulated Depreciation 38 Net Fixed Assets 39 Total Assets 40 Liabilities and Owners' Equity 41 Accounts Payable 42 Short-term Notes Payable 43 Accrued Expenses 44 Total Current Liabilities 45 Long-term Debt 46 Total Liabilities 47 Common Stock 48 Retained Earnings 49 Total Shareholder's Equity 50 Total Liabilities and Owners' Equity 51 52 * Forecast 53 Discretionary Financing Needed 54 Total Accumulated DFN 55 56 2017 Dividends 57 Net Addition to Plan and Equipment 58 Life of New Equipment in Years 59 Salvage Value of New Equipment 60 New Depreciation (Straight Line) 61 Iteration 62 2016 Chapter 5 Big Bob's, Inc., wants you to prepare a 2018 Pro Forma Income Statement and a 2018 Balance Sheet using the following 2017 and 2016 data: Fashion Trends, Inc. Fashion Trends, Inc. Balance Sheet Balance Sheet For the Period Ended Dec. 31, 2017 As of Dec. 31, 2017 2017 2016 Assets 2017 2016 Sales 6,148,000 5,134,000 Cash and Equivalents 862,000 678,000 Cost of Goods Sold 4,176,000 3,422,000 Accounts Receivable 1,006,000 730,000 Gross Profit 1,972,000 1,712,000 Inventory 578,000 600,000 S. G&A Expenses 588,000 590,000 Total Current Assets 2,446,000 2,008,000 Fixed Expenses 70,000 70,000 Plant & Equipment 9,338,000 8,644,000 Depreciation Expense 478,000 446,000 Accumulated Depreciation 4,590,000 4,112,000 EBIT 836,000 606,000 Net Fixed Assets 4,748,000 4,532,000 Interest Expense 186,000 182,000 Total Assets 7,194.000 6,540,000 Earnings Before Taxes 650,000 424,000 Liabilities and Owners' Equity Taxes 195,000 127,200 Accounts Payable 764,000 540,000 Net Income 455,000 296,800 Short-term Notes Payable 158,000 198,000 Accrued Expenses 318,000 228,000 Total Current Liabilities 1,240,000 966,000 Long-term Debt 2,046,000 1,934,000 Total Liabilities 3,286,000 2,900,000 Common Stock 1,638,000 1,616,000 Retained Earnings 2,270,000 2,024,000 Total Shareholder's Equity 3.908,000 3,640,000 Total Liabilities and Owners' Equity 7,194,000 6,540,000 The firm has forecasted sales of $7,800,000 and a tax rate of 30% for 2018. Cost of goods sold and SG&A expense in 2018 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 2018, 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 2018 from 2017. Assume the firm pays 4% interest on short-term debt and 7% on long term debt. Calculate interest on an average debt outstanding for the year. Assume that the dividends in 2018 will be the same as those paid in 2017. What is the Discretionary Financing Needed (DFN) in 2018? Is this a surplus or deficit? Build a formula to determine which it is. DFN will be absorbed by long-term debt. Set up an iterative worksheet to eliminate it. D E F G Name: 2016 B 1 Big Bob's Inc. 2 Balance Sheet 3 ProformaFor the Period Ended Dec. 31, 2018 4 2018* 2017 5 Sales 6 Cost of Goods Sold 7 Gross Profit 8 Selling and G&A Expenses 9 Fixed Expenses 10 Depreciation Expense 11 EBIT 12 Interest Expense 13 Earnings Before Taxes 14 Taxes 15 Net Income 16 17 * Forecast 18 19 Notes 20 Tax Rate 21 22 Additional Depreciation 23 Sorth-term Interest Rate 24 Long-term Interest Rate 25 26 27 28 Big Bob's, Inc. 29 Balance Sheet 30 Proforma As of Dec. 31, 2018 31 Assets 2018* 2017 32 Cash and Equivalents 33 Accounts Receivable 34 Inventory 35 Total Current Assets 36 Plant & Equipment 37 Accumulated Depreciation 38 Net Fixed Assets 39 Total Assets 40 Liabilities and Owners' Equity 41 Accounts Payable 42 Short-term Notes Payable 43 Accrued Expenses 44 Total Current Liabilities 45 Long-term Debt 46 Total Liabilities 47 Common Stock 48 Retained Earnings 49 Total Shareholder's Equity 50 Total Liabilities and Owners' Equity 51 52 * Forecast 53 Discretionary Financing Needed 54 Total Accumulated DFN 55 56 2017 Dividends 57 Net Addition to Plan and Equipment 58 Life of New Equipment in Years 59 Salvage Value of New Equipment 60 New Depreciation (Straight Line) 61 Iteration 62 2016