can you show me in excel how each cell was done with reference the cell.

Mini-Case November of each year, the CFO of Barker Electronics egins the financial forecasting process to determine the firm's The electronics business has been growing rapidly over the past 18 months as the economy recovers, and the CFO estimates projected needs for new financing during the coming year that sales will expand by 20 percent in the next year. Depre Barker is a small electronics manufacturing company located in Moline, Illinois, a city best known as the home of the John ciation expense will equal $50.000, and Interest expense is er timated to be $10,000. In addition, he estimates the following Deere Company. The CFO begins the process with the most relationships next year between each of the income statement fucent year's income statement, then projects sales growth for expense items and sales: the coming year, then estimates net income, and finally estimates the additional earnings he can expect to retain and reinvest in the COGS/sales 709 firm. The firm's income statement for 2016 follows: Operating expenses/sales 156 Barker Electronics Tax rate 355 Income Statement ( thousands) for the Year Ended Note that for the coming year both depreciation expense and December 31, 2016 interest expense are projected to remain the same as in 2016. Sales $1,500 a. Estimate Barker's net income for 2017 and its addition to Cost of goods sold (1.050) retained earnings under the assumption that the firm leaves Gross profit $ 450 its dividends paid at the 2016 level. Operating costs S (225) b. Reevaluate Barker's net income and addition to retained Depreciation expense (50) earnings where sales grow at 40 percent over the com- ing year (assume dividends are the same as in 2016). This Net operating income (EBIT) $ 175 scenario requires the addition of new plant and equipment Interest expense (10) in the amount of $100,000, which increases annual depre- Earnings before taxes $ 165 ciation to $58.000 per year, and interest expense rises to $15,000 (58) Income taxes Net income $ 107 $ 20 Dividends Addition to retained earnings $ 87 Chapter 17 Mini-Case (page 575) Modified Change the percent increase in sales and tax rate for both parts 'a' and 'b' to the figures provided below 10.00% 26% Given % Increase in sales COGS as % of Sales Tax Rate Operating Expenses Depreciation expense Interest Expense Dividends INCOME STATEMENT 2016 2017 Sales Cost of goods sold Gross profit Operating cost Depreciation expense Net operating profit Interest expense Earnings before taxes Taxes Net income Dividends Addition to retained earning $1,500,000.00 $1,050,000.00 $450,000.00 $225,000.00 $50,000.00 $175,000.00 $10,000.00 $165,000.00 $58,000.00 $107,000.00 $20,000.00 $87,000.00 b) 3 3 D 1 2 3 4 5 -6 18.00% Given * Increase in sales) COGS as of Sales Tax Rate Operating Expenses Depreciation expense 26% G 5 6 37 Taxes Net income Dividends Addition to retained earning $58,000.00 $107,000.00 $20,000.00 $87,000.00 b) -9 0 1 2 18.00 26% Given Increase in sales) COGS as of Sales Tax Rate Operating Expenses Depreciation expense Interest Expense Dividends 4 -5 6 7 8 19 10 11 12 13 34 ns 16 12 INCOME STATEMENT 2016 2017 Sales Cost of goods sold Gross profit Operating cost Depreciation expense Net operating profit Interest expense Earnings before takes Taxes Net income Dividends Addition to retained earning $1,500,000.00 $1,050,000.00 $450,000.00 $225,000.00 $50,000.00 $175,000.00 $10,000.00 $165,000.00 558,000.00 $107.000,00 $20,000.00 $87,000.00 50 51 52 53 54 55 56 57 SE 52 GO 61 62 + G4 65 PELOMA Mini-Case M Ready 11