Using a premade excel template: estimate future cash flows for a firm and then apply discounted cash flow analysis to estimate the value of a firm. You will then need to decide if you would sell the firm at a specific price.The instructions and template will be included in files as well as 2 example files to see examples of how it should be done.
NAME
Official EBIT
Owner’s Salary
Reasonable Salary
Depreciation
Personal Expenses
Estimated EBITDA
New Equipment
Inventory Investment
Free Cash Flow
Current EBIT
Owner’s Salary
Reasonable Salary
Depreciation
Personal Expenses
New Equipment
Inventory Investment
Estimated Growth Rate
Tax Rate
Terminal Value
CF1
CF2
CF3
CF4
CF5
NPV
Year 0
200,000
100,000
50,000
20,000
10,000
280,000
280,000
200,000
100,000
50,000
20,000
10,000
200,000
15,000
7.00%
40.00%
1,602,268.21
279,000
108,980
325,009
342,159
1,962,779
1,340,650
Year 1
214,000
100,000
50,000
20,000
10,000
294,000
15,000
279,000
Year 2
228,980
100,000
50,000
20,000
10,000
308,980
200,000
108,980
Year 3
245,009
100,000
50,000
20,000
10,000
325,009
325,009
Year 4
262,159
100,000
50,000
20,000
10,000
342,159
342,159
Year 5
280,510
100,000
50,000
20,000
10,000
360,510
360,510
Weight Of Debt
Weight Of Equity
Cost Of Debt
Cost Of Equity
WACC
25.00%
75.00%
15.00%
27.00%
22.50%
Value Of Firm
Basis Of Firm
Gain
Taxes Owed
Value Of Debt
Net Proceeds
Sell For $1.2M?
Sell For $1.4M?
1,340,650
100,000
1,240,650
496,260
500,000
344,390
No
Yes
Final Project Handout
To complete the final project, you will need to estimate future cash flows for a firm and then
apply discounted cash flow analysis to estimate the value of a firm. This will all be done in
Excel and should be done in a way that utilizes formulas and cell references. This project is
worth a total of 25 points.
Estimating Free Cash Flows
Finding WACC
Estimating Free Cash Flow
Start with the operating profit or EBIT for a firm and make the following adjustments:
1. Add back the owner’s salary and benefits.
2. Subtract out a more reasonable compensation for the work performed by the owner
3. Add back any of the owner’s personal expenses that have been run through the company.
4. Add back any depreciation and amortization expenses that were claimed this year.
Remember, these are noncash expenses. They are not actual payments made to someone,
but merely a number from a table or formula that the IRS allows the owner to subtract out
before the calculation of the firm’s taxable income.
5. If a major piece of equipment or asset is intended to be purchased in this particular year,
then subtract this amount.
6. Subtract the amount of money it would take to bring the inventory of the company up to a
reasonable level. For instance, sometimes when a business owner knows that the company
will be sold, he may “sell out of inventory” rather than reorder new inventory. Therefore,
by the time the new owner takes possession, there may be nothing on the shelves to sell.
As a result, the new owner may need to invest in inventory or other types of working
capital before the business is able to operate and generate income. An analogy might be
the way that people sell their cars. They typically do not pay for new tires, a brake job, a
battery, a tune-up, and a tankful of gas the day before a car is sold.
Finding Terminal Value
Terminal value = year 6 cash flow / WACC
Discounted Cash Flow Analysis Steps
1.
2.
3.
4.
5.
6.
Calculate a firm’s free cash flows for the next five years.
Calculate the terminal value of the firm after year five.
Estimate a weighted average cost of capital for a firm.
Calculate the NPV of the firm.
Calculate any taxes that will be owed as a result of the sale of the firm.
Calculate the net proceeds from selling the firm.
Final Project Handout
Practice Problem
1. A potential buyer is interested in purchasing a company called MacroTech from an owner
whose financial statements report that this year’s EBIT was $200,000.
2. Other information gathered reveal that the owner paid himself salary and benefits of
$100,000 when a more reasonable compensation, given the local job market, was $50,000.
3. There was $20,000 in depreciation expense this year.
4. The owner had $10,000 in personal expenses such as lease payments on a Lexus that he
called a “company” car.
5. The firm will need a net investment of $200,000 to replace worn-out equipment during
year 2.
6. It is believed that EBIT will increase by 7% per year for the next five years given the
productive capacity of the firm and the nature of the product market. Beyond five years, a
reasonable estimate of growth is neither possible nor relevant because the buyer should
not have to pay for growth that would come from his own efforts and future investment.
7. The firm has an outstanding loan of $500,000.
8. The cost basis of the firm is $100,000. In other words, the original owner has
$100,000 of his money invested in the company. If the firm sells for more than
$100,000, the difference between the selling price and the $100,000 invested would be
considered income to the seller. Further suppose that the original owner would fall in a
combined state and federal tax bracket of 40%.
9. MacroTech has a history of maintaining an average inventory balance of
$80,000. Since MacroTech first appeared on the market for sale, the owner has not purchased
any new inventory and has allowed the level to drop to $65,000, thus suggesting that the buyer
will probably need to invest $15,000 in inventory just to bring the firm’s inventory back up to a
safe level. This would occur in the first year of operations.
10. Assume this firm finances itself using 25% debt. The cost of debt has been estimated to be
15% while the cost of equity has been estimated to be 27%.
Course Project2
Due on
To complete Course Project 2, you will need to estimate future cash flows for a firm and then
apply discounted cash flow analysis to estimate the value of a firm. You will then need to decide
if you would sell the firm at a specific price.
This project must be submitted as an Excel file. You have until midnight on the due date to
turn in the project by Canvas. Any late project will be accepted at my discretion depending
on the degree of lateness. Your work must be submitted as a readable Excel file.
The project is worth a total of 25 points. To receive all 25 points, you should make sure to fully
complete the Excel file while following all directions. You should only hard code (aka type
directly) into the grey cells. All of the other cells (blue, green, orange) should only include
formulas and cell references. Failure to follow this will result in a significant or total loss of
points. All work should be 100% your own, and any suspicion of academic dishonesty will be
fully pursued according to university policies in addition to receiving a 0/25 on this project. See
below for the point breakdown and rubric.
Project Problem
1. Organic Micros was established 8 years ago by Mary Mendoza. The firm reported EBIT
this year of $80,000. Mary typically reports about $2,000 in personal expenses through
the firm each year. She also pays herself a salary of $60,000 despite the market rate for
such a position being closer to $30,000.
2. The firm currently reports $10,000 in depreciation expenses every year.
3. The firm will need $25,000 of inventory replaced in year 1 and will also require the
replacement of equipment during year 4 in an amount totaling $50,000.
4. The firm currently has outstanding debt of $400,000. The firm has been historically
financed in a way that 30% of funds are a result of debt financing. The cost of this debt
financing is estimated to be 12% while the cost of equity financing is estimated at 22%.
5. Mary originally invested $120,000 of her own funds in the firm which establishes a cost
basis for the firm of $120,000. Mary typically falls in a tax bracket that results in a total
tax rate of 40%.
6. Based on the firm’s maturity and the state of the industry, it is estimated the EBIT will
increase by 9% each year for the next 5 years. Beyond this time frame, a reasonable
estimate of growth is neither possible nor relevant.
Point Breakdown
Free Cash Flows
NPV
Net proceeds
Sell decisions
Name
10 pts
6 pts
6 pts
2 pts
1 pt
Point Deductions
Missing formula/reference
No formulas/references
Wrong calculation
Wrong sell decision
No name
No Excel file
Late (if accepted)
-1 (per)
-25
-2 (per)
-1 (per)
-1
-25
-5
NAME
Official EBIT
Owner’s Salary
Reasonable Salary
Depreciation
Personal Expenses
Estimated EBITDA
New Equipment
Year 0
Year 1
Year 2
Year 3
Year 4
Inventory Investment
Free Cash Flow
Current EBIT
Owner’s Salary
Reasonable Salary
Depreciation
Personal Expenses
New Equipment
Inventory Investment
Estimated Growth Rate
Tax Rate
Terminal Value
CF1
CF2
CF3
CF4
CF5
NPV
Weight Of Debt
Weight Of Equity
Cost Of Debt
Cost Of Equity
WACC
Value Of Firm
Basis Of Firm
Gain
Taxes Owed
Value Of Debt
Net Proceeds
Sell For $900k?
Sell For $700k?
Year 5
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
