please show excel formulas

The Assignment: Use ONLY Excel for this WA; and make sure I can trace ALL your calculations. I can only do this if you did NOT plug in the end answers. If done via Word file, no points will be given. Here are two problems under this assignment: Problem 1. Given the following information, and using the VC method of valuation, determine the % of the company that needs to be given up for $1.5 m investment (10 points total) Required ROR by investor(s): 25% Net income in five years: $ 3.2 m Expected P/E ratio in five years: 12 times. Problem 2. Part A. Apply the fundamental method of valuation to the following data in order to find a market value (price) for the venture. Part B. What portion of the company should be given up if there is a need for $1.6 m in venture capital? (30 points total) Year % of Revenue After-tax After-tax Discount PV of growth (Sales) Margin Profit Factor Earnings 15% 3.4 m 0 1.30 2 20 4% 3 25 5 30 10 5 35 15 6 40 16 7+ 12% Required ROR by investor(s): 25% Net income in five years: S 3.2 m Expected P/E ratio in five years: 12 times, Problem 2. Part A. Apply the fundamental method of valuation to the following data in order to find a market value (price) for the venture. Part B. What portion of the company should be given up if there is a need for $1.6 m in venture capital? (30 points total) Year % of Revenue After-tax After-tax Discount PV of growth (Sales) Margin Profit Factor Earnings 1 15% 3.4 m 0 0 1.30 0 2 20 4% 3 25 S 4 30 10 5 35 15 6 40 16 7+ 12% 9 As you see from line 7 and beyond (7+), growth falls to 12%, and profit margin is 9%. Assume also discount rate for years 7 and beyond falls to 15% and stays the same for all such years. The Assignment: Use ONLY Excel for this WA; and make sure I can trace ALL your calculations. I can only do this if you did NOT plug in the end answers. If done via Word file, no points will be given. Here are two problems under this assignment: Problem 1. Given the following information, and using the VC method of valuation, determine the % of the company that needs to be given up for $1.5 m investment (10 points total) Required ROR by investor(s): 25% Net income in five years: $ 3.2 m Expected P/E ratio in five years: 12 times. Problem 2. Part A. Apply the fundamental method of valuation to the following data in order to find a market value (price) for the venture. Part B. What portion of the company should be given up if there is a need for $1.6 m in venture capital? (30 points total) Year % of Revenue After-tax After-tax Discount PV of growth (Sales) Margin Profit Factor Earnings 15% 3.4 m 0 1.30 2 20 4% 3 25 5 30 10 5 35 15 6 40 16 7+ 12% Required ROR by investor(s): 25% Net income in five years: S 3.2 m Expected P/E ratio in five years: 12 times, Problem 2. Part A. Apply the fundamental method of valuation to the following data in order to find a market value (price) for the venture. Part B. What portion of the company should be given up if there is a need for $1.6 m in venture capital? (30 points total) Year % of Revenue After-tax After-tax Discount PV of growth (Sales) Margin Profit Factor Earnings 1 15% 3.4 m 0 0 1.30 0 2 20 4% 3 25 S 4 30 10 5 35 15 6 40 16 7+ 12% 9 As you see from line 7 and beyond (7+), growth falls to 12%, and profit margin is 9%. Assume also discount rate for years 7 and beyond falls to 15% and stays the same for all such years