Landon Enterprises manufactures and sells solid and veneered wood furniture. The target customer audience is driven to purchase above average quality furniture and is willing to pay a slight premium price to acquire the limited edition pieces. Landon is considering an expansion of facilities and inventory product lines to broaden its customer base. The firm is concerned that weakening economic conditions may cause a decline in sales growth even though the target audience may be slightly less affected (due to higher average income) than the population as a whole.

An income statement and a balance sheet for the year ended and as of December 31, 2015 are presented on page 3. The key ratio values (e.g. contribution margin ratio) have held steady for the past three years or so but are expected to change in the coming years. Specifically, the following moving forward values are expected by senior management:

The variable cost ratio is expected to increase to 65% (it was 62% in 2015) for each of the next three years (2016, 2017, 2018) whether the expansion occurs or not. This increase is driven primarily by the growing cost of skilled craftsman labor and the rising cost of the specialized woods necessary to build the premium products. Landon will attempt to offset these rising costs by developing an international sales presence. As a first pass, use the 66% expected variable cost ratio for the three year planning period. Landon does not believe it can pass on cost increases to customers.

Fixed operating costs (excluding depreciation) are expected to increase by 7% annually for each of the next three years (over 2015 as the base year) if the expansion occurs and 3% annually if it does not occur.

New capital asset additions are expected to be depreciated over a blended life of 10 years. The firm uses the straight line method of depreciation. Use 2015 depreciation expense as the base amount that will continue over the next three years and increase that amount by additional depreciation on any new capital asset additions (depreciate over the 10 year blended life). Assume that any new capital additions occur at the beginning of 2016. Use a full year of depreciation each year (no partial year in 2016).

The blended interest rate on new borrowed funds, above the existing 12/31/2015 amount, is expected to be 7%. The 7% applies to additional debt financing. The interest rate on the existing debt financing remains at 6% annually.

The Board of Directors plans a dividend payout ratio of 40% (40% of EPS) annually in 2016 and beyond.

Management expects the income tax rate to remain steady at 40%.

If earnings per share (EPS) increase by more than 10% during the year, then the PE ratio is expected to increase to 16, if EPS increases between 5% and 10% the PE ratio is expected to stay at 12, and if EPS does not grow by 5% the PE is expected to fall to 9.

The BOD is considering an expansion to the asset base of $4,000,000. Approximately $2,400,000 will be in additional fixed assets (subject to depreciation) and approximately $1,600,000 in current assets (primarily inventory and AR). The expanded asset base is expected to increase sales by 16% annually. If the expansion is not undertaken, sales are expected to increase by 6% annually. Management is considering three different financing options to fund the expansion.

Issue $4,000,000 of new interest bearing debt (7%)

Issue $4,000,000 of new common stock at a market price of $26.40.

Issue a 50/50 mix of new debt (7%) and new common stock ($26.40 per share).

The BOD needs your expertise to help it determine the likely financial outcomes from the expansion. Specifically the BOD wants you to deliver the following within your report:

Prepare forecasted income statements for the next three years (2016, 2017, 2018) assuming that the expansion does not take place. For each year, present the forecasted income statement, the breakeven point (treat interest expense as a fixed cost for the purpose of the breakeven calculation), the earnings per share of common stock, the dividends per share of common stock, and the forecasted stock price given the Price Earnings ratio guidelines presented above. Create an effective MS Excel model (landscape) that shows 2015 (the last actual year) and 2016, 2017, and 2018 (the forecasted years).

You found the beginnings of an MS Excel model on the previous accountant's company laptop. You might want to begin with this model or create your own. Be sure to thoroughly proof the model that you found. You are not sure of the other accountant's skill set. Review existing formulas and add your own as needed. The draft model is attached. If you see any data or guidance conflicts between this commentary and the draft model you are to follow this commentary.

Prepare forecasted income statements for the next three years (2016,2017,2018), under each financing option, assuming that the expansion does take place. Use the 2015, 2016, 2017, 2018 landscape format that you have in place. As you did above, present the forecasted income statement, the breakeven point (treating interest as a fixed operating cost for this purpose), the earnings per share, the dividends per share, and the forecasted stock price for each year.

In total, you will be preparing 12 forecasted income statements (3 in part 1 (expansion does not take place) and 9 in part 2 (expansion does take place - 3 for each financing option). Setting up an effective MS Excel model will substantially reduce your preparation and analysis time.

Prepare approximately 3 pages of commentary in which you provide analysis and reflection upon the BODs potential actions (to do or not do the expansion and if expansion, how to finance). I want an efficient comprehensive reflection (worthy of Masters level work) on the key outcomes that your forecasts present. Your analysis / reflection should address quantitative and qualitative factors. Be sure to include reflection upon the risk of the various decisions and related outcomes. Consider which key assumptions are most susceptible to being "flawed".

Begin your report with an intro to the BOD summarizing your approach and your key findings and conclusions. Reference key results presented in your forecast models. The MS Excel Model needs to be a separate attachment. One MS Word file with intro / commentary / conclusion and one MS Excel file (either multiple pages or Im fine with one large page). Im looking for a very effectively designed format. This influences the grade.

A condensed full year projected income statement for 2015 is as follows:

Sales $8,000,000

Variable Costs 4,960,000 (Production labor and materials)

Contribution Margin 3,040,000

Fixed Operating Costs 1,200,000 (Requiring cash payments)

Depreciation Cost 375,000 ($3,000,000 divided by the 8 year depreciation life)

EBIT 1,465,000

Interest 180,000 (6% on interest bearing debt = 3,000,000 times 6%)

Earnings before tax 1,285,000

Tax 514,000 (Federal and State income tax = 40% of Earnings before tax)

Earnings after tax $ 771,000 (The earnings amount available to common stockholders)

Outstanding shares of common stock 350,000 (for 2015)

Earnings per share $2.20 (the $771,000 divided by 350,000 shares)

Dividends per share $ .88 (40% of the $2.20 EPS)

Average P/E ratio for 2015 was 12 (average selling price was approximately $26.40 per share = 12 times $2.20 EPS - note that minor rounding may occur with calculations and displayed results)

See the firm's projected December 31, 2015 and January 01, 2016 balance sheets below. As unrealistic as it is, assume that all expansion related impacts on the balance sheet occur in the afternoon on January 1, 2016.

The firms balance sheets as of December 31, 2015 and January 01, 2016 are expected to be as follows:

	12/31/2015	No Expansion 01/01/2016	Expansion All Debt 01/01/16	Expansion All Equity 01/01/16	Expansion 50/50 Mix 01/01/16
Assets
Cash	$400,000	400,000	400,000	400,000	400,000
AR	800,000	800,000	1,000,000	1,000,000	1,000,000
Inventory	2,600,000	2,600,000	3,600,000	3,600,00	3,600,000
Plant/Equipment/Vehicle(gross)	3,000,000	3,000,000	5,400,000	5,400,000	5,400,000
Plant and Equipment (accum depr)	(1,200,000)	(1,200,000)	(1,200,000)	(1,200,000)	(1,200,000)
Land	1,400,000	1,400,000	1,800,000	1,800,000	1,800,000
Total Assets	$7,000,000	7,000,000	11,000,000	11,000,000	11,000,000
Liabilities and Equity
Accounts Payable	$1,800,000	1,800,000	1,800,000	1,800,000	1,800,000
Interest Bearing Debt	3,000,000	3,000,000	7,000 000	3,000,000	5,000,000
Total Liabilities	4,800,000	4,800,000	8,800,000	4,800,000	6,800,000
Paid in Capital (Common Stock)	1,000,000	1,000,000	1,000,000	5,000,000	3,000,000
Total Equity	2,200,000	2,200,000	2,200,000	6,200,000	4,200,000
Total Liabilities and Equity	$7,000,000	7,000,000	11,000,000	11,000,000	11,000,000

And below is the Excel sheet too.

Key Assumptions:		Bold amounts require user input if variable values are changed
		Annual sales growth rate without expansion						6.0%
		Annual sales growth rate with expansion						16.0%
		Annual variable cost ratio						65.0%
		Annual fixed cash operating cost increase without expansion						3.0%
		Annual fixed cash operating cost increase with expansion ($100,000 in 2015 and then						4.0%	thereafter
		Capital asset depreciation life						8	years
		Interest rate on existing debt						6.0%
		Interest rate on new debt						7.0%
		Dividend payout ratio						40.0%
		Forecasted tax rate						40.0%
		Stock price projections: If EPS growth > 10% PE ratio					10%	16	times earnings
		Stock price projections: If EPS growth 5 - 10% PE ratio					5%	12	times earnings
		Stock price projections: IF EPS growth < 5% PE ratio						9	times earnings
		Cost of expansion						$ 4,000,000
		Depreciable asset additions with expansion						$ 2,400,000
		Expansion financed with				100.0%	New Debt	$ 4,000,000	Paid in Cap	-
		Expansion financed with				0.0%	New Debt	-	Paid in Cap	4,000,000
		Expansion financed with				50.0%	New Debt	$ 2,000,000	Paid in Cap	2,000,000
		Existing Common Shares Outstanding						350,000
		Selling price new issue common shares						$ 26.40
		New Common Shares Issued - All equity						151,515
		New Common Shares Issued - Debt / equity mix						75,758
Forecasted Income Statement Data - No Expansion
			2015	2016	2017	2018
			Actual	Forecasted	Forecasted	Forecasted
Sales			$ 8,000,000	8,480,000
Variable Costs			4,960,000	5,512,000
Contribution Margin			3,040,000	2,968,000
Fixed Operating Cost (cash)			1,200,000	1,236,000
Depreciation			375,000	375,000
Earnings before Int and tax			1,465,000	1,357,000
Interest			180,000	180,000
Earnings before tax			1,285,000	1,177,000
Tax			514,000	470,800
Earnings after tax			$ 771,000	$ 706,200
Common Shares Outstanding			350,000	350,000
Earnings Per Share			$ 2.20	$ 2.02
Dividends Per Share			$ 0.88	$ 0.81
EPS Growth Rate				-8.4%
PE Ratio based upon EPS growth			12	9
Forecasted Stock Price			$ 26.43	$ 18.16
Breakeven Revenue			$ 4,618,421	$ 5,117,143
Forecasted Income Statement Data - With Expansion All Debt
			2015	2016	2017	2018
			Actual	Forecasted	Forecasted	Forecasted
Sales			$ 8,000,000	9,280,000
Variable Costs			4,960,000	6,032,000
Contribution Margin			3,040,000	3,248,000
Fixed Operating Cost (cash)			1,200,000	1,300,000
Depreciation			375,000	675,000
Earnings before Int and tax			1,465,000	1,273,000
Interest			180,000	460,000
Earnings before tax			1,285,000	813,000
Tax			514,000	325,200
Earnings after tax			$ 771,000	$ 487,800
Common Shares Outstanding			350,000	350,000
Earnings Per Share			$ 2.20	$ 1.39
Dividends Per Share			$ 0.88	$ 0.56
EPS Growth Rate				-36.7%
PE Ratio based upon EPS growth			12	9
Forecasted Stock Price			$ 26.43	$ 12.54
Breakeven Revenue			$ 4,618,421	$ 6,957,143
Forecasted Income Statement Data - With Expansion All Equity
			2015	2016	2017	2018
			Actual	Forecasted	Forecasted	Forecasted
Sales			$ 8,000,000	9,280,000
Variable Costs			4,960,000	6,032,000
Contribution Margin			3,040,000	3,248,000
Fixed Operating Cost (cash)			1,200,000	1,300,000
Depreciation			375,000	675,000
Earnings before Int and tax			1,465,000	1,273,000
Interest			180,000	180,000
Earnings before tax			1,285,000	1,093,000
Tax			514,000	437,200
Earnings after tax			$ 771,000	$ 655,800
Common Shares Outstanding			350,000	501,515
Earnings Per Share			$ 2.20	$ 1.31
Dividends Per Share			$ 0.88	$ 0.52
EPS Growth Rate				-40.6%
PE Ratio based upon EPS growth			12	9
Forecasted Stock Price			$ 26.43	$ 11.77
Breakeven Revenue			$ 4,618,421	$ 6,157,143
Forecasted Income Statement Data - With Expansion Debt & Equity Mix
			2015	2016	2017	2018
			Actual	Forecasted	Forecasted	Forecasted
Sales			$ 8,000,000	9,280,000
Variable Costs			4,960,000	6,032,000
Contribution Margin			3,040,000	3,248,000
Fixed Operating Cost (cash)			1,200,000	1,300,000
Depreciation			375,000	675,000
Earnings before Int and tax			1,465,000	1,273,000
Interest			180,000	320,000
Earnings before tax			1,285,000	953,000
Tax			514,000	381,200
Earnings after tax			$ 771,000	$ 571,800
Common Shares Outstanding			350,000	425,758
Earnings Per Share			$ 2.20	$ 1.34
Dividends Per Share			$ 0.88	$ 0.54
EPS Growth Rate				-39.0%
PE Ratio based upon EPS growth			12	9
Forecasted Stock Price			$ 26.43	$ 12.09
Breakeven Revenue			$ 4,618,421	$ 6,557,143