Homework 6

Forecasting:

The lemonade stand Scarlet founded her freshman year is thriving, and is now looking to expand into new markets. After looking at her desired growth trajectory (and remembering what she learned from her Finance for Entrepreneurs class), she realizes that she will need external financing in order to make her dream come true. Scarlet realizes that her investors will want to see the businesss revenue projections for 2017.

Project sales for Scarlets business using the 5 methods we discussed in class, and given the information below. Please fill out this table in the accompanying Excel file (HW 6) so I can see how you calculated your answers. For Historical Sales, take an average of the year-over-year growth from 2014-2015, and 2015-2016.

Given information:

Here are historical results for the past few years:

	2016	2015	2014
Revenue	20,000	15,000	10,000

Scarlets friend Jackie Blackbeard at Seton Hall was able to grow her iced tea business 20% per year.

Scarlet believes that she will capture 5% market share in New Brunswicks $500,000 beverage market next year.

Scarlet won the coveted exclusive contract to supply lemonade for all football games in 2017. This should be at least 5x as profitable as her basketball contract, which accounted for about of her sales last year. Scarlet believes that football will drive approximately of all her sales next year.

Scarlet expects to sell 10,000 glasses of lemonade next year for $3.00 each.

a. Fill out the table below within Excel (see the accompanying Excel sheet

	2017 Sales Forecast	Calculations/Assumptions
Historical Sales	FILL THIS OUT IN THE ACCOMPANYING EXCEL WORKBOOK
Competitor Analysis
Market Size/Share
Existing Sales/Contracts
Specific Figures

Which do you think is the most appropriate method to use, and why?

Scarlets 2017 has come and gone, and her financial statements look like those attached in the accompanying Excel Workbook (tab labeled Fin Stmts).

Using a Percentage of Sales method from 2017 results and the Given Information below, derive the 2018 Pro Forma (forecast) Income Statement and Balance Sheet (i.e. fill in the 2018 cells with the appropriate amounts).

Is there a plug needed? If so, how much is it? What does it tell you about Scarlets forecasted 2018 financial projections and what could Scarlet do to remedy the situation?

Based on Scarlets 2017 & 2016 financials, what is her businesss Sustainable Growth Rate (SGR)?

Given Information:

Scarlet believes that her 2018 Revenue figure will be 12% higher than her 2017 Revenue.

She estimates that her SG&A will go up by $1,000 from 2016-2017 since she plans on getting a part-time employee.

She doesnt intend to pay any dividends for 2018.

For her Balance Sheet forecasted items she uses only 2017 results, rather than taking an average of the results (average of the % of sales) from 2016 and 2017.

Scarlet assumes Interest Expense, Curr Portion of LT Debt, and Bank Loan Payable will remain the same in 2018 as they were in 2017.

Scarlet plans on issuing $1,000 of (new) Common Stock in 2018.

Scarlet determines that she will need to spend $3,700 for a new Lemon Press machine, which will be her entire CAPEX for 2018. We will assume depreciation will be $200 for this item for 2018, and the yearly depreciation/amortization expense for the rest of Scarlets PPE will remain the same as it was in 2017 (and assume no asset sales were made in 2017).

Intangibles and Goodwill will remain unchanged in 2018.

She is not planning on disposing of any of her assets (i.e. no asset sales).

Return on Investment (ROI) / Capital Budgeting

Scarlet is considering investing in the following three projects:

Buying a new storefront location for $30,000, which she estimates will generate net income per year of $5,300, indefinitely.

Buying an online ordering system + app for $20,000 that Scarlet believes will earn her a net $3,700 per year for 15 years, before she believes the technology will become obsolete and need to be re-designed/re-purchased.

Buying a bond as an investment for $15,000, which pays no interim cash flows but does pay one lump sum of $30,000 after 3 years.

Buying a bond as an investment for $15,000, which pays no interim cash flows but does pay one lump sum of $30,000 after 3 years.

Scarlets capital structure consists of 40% equity, for which her investors expect a return of 18%, and 60% debt, mostly a loan that costs her 8% in interest expense per year. Assume her tax rate is 35% and needs to be factored into her WACC.

Given the above information , answer the questions below. Use tab ROI in the Excel Spreadsheet to do your calculations and to fill in your answers:

What would Scarlets minimum hurdle rate be (WACC)?

Assume Scarlets discount rate for evaluating any project is her WACC:

Calculate the payback period for each of the three projects. Based on the payback period methodology alone, which of the three projects should Scarlet choose?

Calculate the NPV of each of the three projects. Based on the NPV methodology alone, which of the three projects should Scarlet choose?

Assuming she had sufficient funds and according to the NPV methodology, which projects (i.e. more than one allowed) should Scarlet undertake?

Are there any reasons Scarlet might want to choose one project over the others for alternative reasons?

Extra Credit: Calculate the IRR of each of the three projects. Based on the IRR methodology, which of the three projects should Scarlet choose? You will need to use the Goal Seek functionality within Excel to back into the assumed discount rate for at least the first two Projects, and you can use the same methodology for all three Projects if you want.

Income Statement
	2018	2017
Sales / Revenue	?	41,600
Cost of Goods Sold (COGS)	?	24,960
Gross Profit	?	16,640
Selling, General & Administrative (SG&A)	?	12,940
Operating Profit	?	3,700
Interest Expense	?	2,000
Income Before Taxes	?	1,700
Income Tax Expense	?	595
Net Income	?	1,105
Assume No Dividends Paid

Balance Sheet
Assets
Current Assets:	2018	2017	2016
Cash	?	2,500	5,495
A/R	?	800	1,300
Inventory	?	2,900	1,600
Prepaid Rent	?	2,200	1,800
Prepaid Insurance	?	1,400	1,900
Tot Curr Assets	?	9,800	12,095
LT Assets
PPE, Gross	?	9,500	5,500
Accumul Depr	?	2,800	2,000
PPE, Net	?	6,700	3,500
Intangibles	?	3,000	3,000
Goodwill	?	1,000	1,000
Total Assets	?	20,500	19,595

Liabilities and Stockholders' Equity
Current Liabilities:	2018	2017	2016
A/P	?	1,100	300
Deferred Revenue	?	700	1,000
Curr Portion of LT Debt	?	2,200	5,300
Wages Payable	?	2,600	4,600
Tot Curr Liab	?	6,600	11,200
LT Liab
Bank Loan Payable	?	5,500	3,300
Tot Liab	?	12,100	14,500
Stockholders' Equity
Common Stock	?	3,300	1,100
Retained Earnings	?	5,100	3,995
Total Stockholders' Equity	?	8,400	5,095
Total Liab + Stockholders' Equity	?	20,500	19,595
PLUG (if necessary):	?
What it tells you:	?
Sustainable Growth Rate (SGR):	?

		2016	2015	2014
	Revenue	20,000	15,000	10,000
Average
1a.		2017 Sales Forecast	Calculations / Assumptions
	Historical Sales	?	?
	Competitor Analysis	?	?
	Market Size / Share	?	?
	Existing Sales / Contracts	?	?
	Specific Figures	?	?
1b.	Which is the most appropriate and why?
	if you have enough money you take the project, because it will increase value of company

3a.					Project 1	Project 2	Project 3
Cost of Debt:	?			Initial Cost	?	?	?
% of Debt:	?			Payments per yr	?	?	?
Tax Rate:	?			# of Years	?	?	?
				Final pmt (FV)	?	?	?
Cost of Equity:	?								Which Project is the best?
% of Equity:	?		3b.	Payback Period	?	?	?		?
WACC	?		3c.	NPV	?	?	?		?
			3f. (opt)	IRR	?	?	?		?
			3d.	Answer:	?
			3e.	Answer:	?