Book: Financial Analysis with Microsoft Excel, 9th edition by Timothy R. Mayes

C05-P01: Using the data in the student spreadsheet file Ethan Allen Financials.xlsx (to find the student spreadsheets for Financial Analysis with Microsoft Excel, eighth edition, go to www.cengagebrain.com) forecast the June 30, 2019 income statement and balance sheet for Ethan Allen. Use the percent of sales method and the following assumptions:

(1) sales in FY 2019 will be $761.20;

(2) the tax rate will be 25%;

(3) each item that changes with sales will be the five-year average percentage of sales;

(4) Property, Plant & Equipment - Gross will increase to $650; and

(5) the dividend will be $0.90 per share. Use your judgment on all other items.

a. What is the DFN in 2019? Is this a surplus or deficit?

b. Assume that the DFN will be absorbed by long-term debt and that the interest rate is 4% of LTD. Set up an iterative worksheet to eliminate it.

c. Create a chart of cash versus sales and add a trend line. Is cash a consistent percentage of sales? Does this fit your expectations?

d. Use the regression tool to verify your results from part c. Is the trend statistically significant? Use at least three methods from the regression output to show why or why not.

e. Turn off iteration and use the Scenario Manager to set up three scenarios:

1) Best CaseSales are 5% higher than expected.

2) Base CaseSales are exactly as expected.

3) Worst CaseSales are 5% less than expected.

What is the DFN under each scenario?

Ethan Allen Interiors Inc.
Income Statement (Industrial)
All figures in millions of U.S. Dollar, except per share items	Actual
	Jun '18	Jun '17	Jun '16	Jun '15	Jun '14			Jun '19
Sales	766.784	763.385	794.202	754.600	746.659			746.684
Cost of Goods Sold (COGS) incl. D&A	350.820	343.662	351.966	343.437	340.163			337.193
Gross Income	415.964	419.723	442.236	411.163	406.496			409.491
SG&A Expense	367.097	361.773	353.057	345.229	336.860			357.164
EBIT (Operating Income)	48.867	57.950	89.179	65.934	69.636			52.327
Interest Expense	0.325	1.223	1.618	5.957	7.540			0.087
Other Income - Net	0.525	0.268	0.395	1.206	0.306			0.000
Unusual Expense - Net	0.000	0.000	0.000	4.500	0.000			18.380
Pretax Income	49.067	56.995	87.956	56.683	62.402			33.860
Income Taxes	12.696	20.801	31.319	19.541	19.471			8.162
Net Income	36.371	36.194	56.637	37.142	42.931			25.698
EPS (recurring)	1.32	1.29	2.00	1.38	1.47
EPS (diluted)	1.32	1.29	2.00	1.27	1.47			0.96
Diluted Shares Outstanding	27.63	27.96	28.32	29.18	29.28			26.75
Total Shares Outstanding	26.53	27.45	27.75	28.41	28.93
Dividends per Share	0.76	0.74	0.62	0.50	0.40			0.76
Payout Ratio	57.72	57.36	31.00	39.37	27.21
EBITDA	68.70	78.07	108.53	85.08	87.57			71.93
EBIT	48.87	57.95	89.18	65.93	69.64			33.95
Depreciation & Amortization Expense	19.83	20.12	19.35	19.14	17.93			19.60
Source : FactSet Fundamentals

Ethan Allen Interiors Inc.
Balance Sheet (Industrial)
All figures in millions of U.S. Dollar, except per share items			Actual
	Jun '18	Jun '17	Jun '16	Jun '15	Jun '14			Jun '19
Assets
Cash Only	22.363	57.701	52.659	76.182	109.176			20.824
Total Short Term Investments	0.000	0.000	0.000	2.198	18.153			0.000
Short-Term Receivables	12.364	12.293	9.467	12.547	12.426			14.247
Inventories	163.012	149.483	162.323	151.916	146.275			162.389
Other Current Assets	16.686	23.621	23.755	27.831	19.599			18.830
Total Current Assets	214.425	243.098	248.204	270.674	305.629			216.290
Property, Plant & Equipment - Gross	611.027	605.219	598.264	594.912	601.709
Accumulated Depreciation	343.124	335.021	324.649	317.877	313.553
Net Property, Plant & Equipment	267.903	270.198	273.615	277.035	288.156			245.246
Other Long-Term Assets	48.105	54.926	55.590	59.599	60.649
Total Assets	530.433	568.222	577.409	607.308	654.434			510.351
Liabilities & Shareholders' Equity
ST Debt & Curr. Portion LT Debt	0.584	2.731	3.001	3.341	0.501			0.550
Accounts Payable	18.768	16.961	15.437	18.946	24.320			35.485
Other Current Liabilities	101.908	106.753	104.909	118.682	111.226			86.791
Total Current Liabilities	121.260	126.445	123.347	140.969	136.047			122.826
Long-Term Debt	1.096	11.608	38.837	74.227	130.411			0.516
Other Liabilities	24.207	29.273	23.023	21.577	20.509			23.080
Total Liabilities	146.563	167.326	185.207	236.773	286.967			146.422
Common Stock Par/Carry Value	0.490	0.490	0.489	0.489	0.486
Additional Paid-In Capital/Capital Surplus	376.950	377.550	374.972	370.914	365.733
Retained Earnings	669.013	661.976	646.315	607.079	584.395
Treasury Stock and Other Deductions from Equity	662.722	639.310	629.574	607.947	583.147
Total Shareholders' Equity	383.731	400.706	392.202	370.535	367.467			363.866
Total Liabilities & Shareholders' Equity	530.433	568.222	577.409	607.308	654.434			510.351
Source : FactSet Fundamentals

NB: I am not a student of finance. I am completely new to this and also excel. IF you could please show all the steps. I would really appreciate it.