Excel document (2-3 pages)
Details: Incorporate an ESO plan into a company's valuation.
The c9mpany is Starbucks
For your company (the company you used for the first two Units), incorporate the effect of the Employee Stock Option (ESO) plan into the common equity valuation. Be sure to consider both the forecasted ESO grants and outstanding ESOs. Perform your valuation in Excel; use appropriate formulas/equations.
To incorporate an ESO plan into the common equity valuation of Starbucks in an Excel document, follow these steps:
1. Gather the necessary data:
a. Obtain the forecasted ESO grants: These are the estimated number of Employee Stock Options that will be granted to Starbucks employees in the future. You can find this information in the company's financial statements, annual reports, or analyst reports.
b. Determine the outstanding ESOs: These are the Employee Stock Options that have already been granted but have not yet been exercised or expired. Again, this information can be found in the company's financial statements or annual reports.
2. Set up the Excel document:
a. Create a new Excel spreadsheet and title it accordingly.
b. Set up column headings, such as "Year," "Revenue," "Operating Expenses," "Net Income," "Diluted Shares Outstanding," etc.
c. In the first row under the "Year" column, input the projected years for your valuation (e.g., 2021, 2022, 2023).
d. Fill in the rest of the data for revenue, operating expenses, net income, and diluted shares outstanding for each year. This data can be obtained from financial statements or estimates.
3. Calculate the effect of ESO grants:
a. Determine the value of each ESO granted: You may need to estimate the value of each Employee Stock Option granted, based on factors such as the stock price, exercise price, and expiration date. Use a financial model or valuation techniques to compute this value.
b. Multiply the forecasted ESO grants by the estimated value of each option to determine the total value of future stock options granted in each year. This will give you an estimate of the potential dilution from future grants.
4. Incorporate the outstanding ESOs:
a. Determine the exercise price of the outstanding ESOs: This is the price at which employees can purchase the stock when they exercise their options. This information can be found in the company's financial statements.
b. Calculate the number of shares that would be issued if all outstanding ESOs were exercised: Multiply the number of outstanding ESOs by the exercise price to estimate the potential dilution from outstanding options.
c. Add the value of the outstanding ESOs to the total value of future grants to get the total potential dilution from ESOs in each year.
5. Adjust the diluted shares outstanding:
a. Subtract the total potential dilution from ESOs in each year from the diluted shares outstanding to get the adjusted diluted shares outstanding.
b. This adjusted diluted shares outstanding represent the common equity available to shareholders after accounting for potential dilution from Employee Stock Options.
6. Perform the common equity valuation:
a. Use appropriate valuation formulas/equations to calculate the common equity value based on the adjusted diluted shares outstanding and other input parameters.
b. You can use valuation techniques such as discounted cash flow (DCF), price-to-earnings (P/E) ratio, or other appropriate methods depending on the specific requirements or preferences.
By following these steps, you can incorporate the effect of the Employee Stock Option plan into the common equity valuation of Starbucks in an Excel document.