70.2K

Verified Solution

Link Copied!

Please show excel work for this spreadsheet:

Assumptions 2014 2015 2016 2017 2018 Sales volume (units) 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 Sales price per unit 12.80 12.80 12.80 12.80 12.80 Direct cost per unit 9.60 9.60 9.60 9.60 9.60 German corporate tax rate 29.5% 29.5% 29.5% 29.5% 29.5% Exchange rate ($/) 1.2000 1.2000 1.2000 1.2000 1.2000 Income Statement Sales revenue 12,800,000 12,800,000 12,800,000 12,800,000 12,800,000 Direct cost of goods sold -9,600,000 -9,600,000 -9,600,000 -9,600,000 -9,600,000 Cash operating expenses (fixed) -890,000 -890,000 -890,000 -890,000 -890,000 Depreciation -600,000 - 600,000 -600,000 -600,000 -600,000 Pretax profit 1,710,000 1,710,000 1,710,000 1,710,000 1,710,000 Income tax expense -504,450 -504,450 -504,450 -504,450 -504,450 Net income 1,205,550 1,205,550 1,205,550 1,205,550 1,205,550 Cash Flows for Valuation Net income 1,205,550 1,205,550 1,205,550 1,205,550 1,205,550 Add back depreciation 600,000 600,000 600,000 600,000 600,000 Changes in net working capital 0 0 0 0 0 Free cash flow for valuation, in euros 1,805,550 1,805,550 1,805,550 1,805,550 1,805,550 Cash flow from operations, in dollars $2,166,660 $2,166,660 $2,166,660 $2,166,660 $2,166,660 Present Value @ 15% $7,262,980 Notes: We assume, to simplify the analysis, that Ganado Germany has no debt and therefore no interest expenses. We also assume there are no additional capital expenditures required over the five years shown. We also assume no terminal value; Ganado is valued on its coming expected five years of cash flow only. Net working capital requirements require no additions in the base case due to constant sales. In subsequent scenarios it is assumed receivables are maintained at 45 days of sales, inventory at 10 days of cost of goods sold, and accounts payable at 38 days of sales. Assumptions 2014 2015 2016 2017 2018 Sales volume (units) 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 Sales price per unit 12.80 12.80 12.80 12.80 12.80 Direct cost per unit 9.60 9.60 9.60 9.60 9.60 German corporate tax rate 29.5% 29.5% 29.5% 29.5% 29.5% Exchange rate ($/) 1.2000 1.2000 1.2000 1.2000 1.2000 Income Statement Sales revenue 12,800,000 12,800,000 12,800,000 12,800,000 12,800,000 Direct cost of goods sold -9,600,000 -9,600,000 -9,600,000 -9,600,000 -9,600,000 Cash operating expenses (fixed) -890,000 -890,000 -890,000 -890,000 -890,000 Depreciation -600,000 - 600,000 -600,000 -600,000 -600,000 Pretax profit 1,710,000 1,710,000 1,710,000 1,710,000 1,710,000 Income tax expense -504,450 -504,450 -504,450 -504,450 -504,450 Net income 1,205,550 1,205,550 1,205,550 1,205,550 1,205,550 Cash Flows for Valuation Net income 1,205,550 1,205,550 1,205,550 1,205,550 1,205,550 Add back depreciation 600,000 600,000 600,000 600,000 600,000 Changes in net working capital 0 0 0 0 0 Free cash flow for valuation, in euros 1,805,550 1,805,550 1,805,550 1,805,550 1,805,550 Cash flow from operations, in dollars $2,166,660 $2,166,660 $2,166,660 $2,166,660 $2,166,660 Present Value @ 15% $7,262,980 Notes: We assume, to simplify the analysis, that Ganado Germany has no debt and therefore no interest expenses. We also assume there are no additional capital expenditures required over the five years shown. We also assume no terminal value; Ganado is valued on its coming expected five years of cash flow only. Net working capital requirements require no additions in the base case due to constant sales. In subsequent scenarios it is assumed receivables are maintained at 45 days of sales, inventory at 10 days of cost of goods sold, and accounts payable at 38 days of sales

Answer & Explanation Solved by verified expert

See Answer