Question

Assignment 2 Home Insert Draw Page Layout Formulas Data Review View Share t Wrap TextGeneral E Marge & Conter. Paste Format B 1 υν 田v 오 v 스 롤ニーーー $ v % , 'il 웨 conditional Forable Styles i nscrt Delete Format Sort & X Clear v A175 x f What this tells me about the change in profitability and asset utilization Joshua& White Technologies: December 31 Balance Sheets Joshua&White Technologies Decomber 31 Income Statementas COGS except excluding depr. and amort. meres Expense 20,000 4,000 Sinking fund payment(Thous ands of Dolars$,000$5,000 e payment(Thous ands of Dolars 困回凹 2
Consider the following scenario: Your boss has come to you for advice on the current finances of the company and needs you to create an extended DuPont analysis, common size analysis, and percentage change analysis. Your boss wants you to fill out the attached Excel file because he/she has no idea what she is talking about and is going to use your report as her work.

Remember to answer all the questions in the attached document.

• Has J&W's liquidity position improved or worsened? Explain
• Has J&W's ability to manage its assets improved or worsened? Explain
• How has J&W's profitability changed during the last year?
• Perform an extended DuPont analysis for J7W for 2015 and 2016. What do these results tell you?
• Perform a common size analysis. What has happened to the composition (That is, the percentage in each category) of assets and liabilities?
• Perform a percentage change analysis. What does this tell you about the change in profitability and asset utilization?

Answer 1

Extended DuPont Equation Analysis:

ROE=Profit Margin*Total Asset Turnover*Equity multiplier or Financial Leverage

ROE=(Net Income/Sales)*(Sales/Total assets)*(Total Assets/Total Equity)

Splitting the 1st ratio further,

we can have an extened DuPont equation for ROE , to facilitate easy analysis

Profit Margin=Tax Burden*Int. Burden*EBIT/Opg. Income Margin

Stating together,

ROE=(Net Income/EBT)*(EBT/EBIT)*(EBIT/Sales)*(Sales/Total assets)*(Total Assets/Total Equity)

Putting the values,

ROE (2015) =(34524/57540)*(57540/62000)*(62000/400000)*(400000/327240)*(327240/218440)=

0.6*0.9281*0.155*1.2223*1.4981=

15.81%

ROE (2016) =(40200/67000)*(67000/72740)*(72740/420000)*(420000/379659)*(379659/245868)=

0.6*0.9211*0.1732*1.1063*1.5442=

16.35%

Comparing 2015 & 2016

Tax burden remains the same .

Interest burden is also almost the same

Operating margin has increased

So, Profit margin has increased indicating operational efficiency .

Asset utilisation has come down---as can be seen that $ sales generated per $ of asset employed has come down from 1.22 in 2015 to 1.10 in 2016.

Increase in assets is greater than increase in sales.

Equity multiplier has increased from 1.50 (rounded-off) in 2015 to 1.54 in 2016.

Due to more than proportionate increase in assets than the increase in equity(denominator)---so concluded because interest burden on pre-tax income remains almost same in 2016 --

Conclusion: main reason for increase in ROE is due to the increase in operating margin , indicating operational efficiency.

Answers as per Format

RATIO ANALYSIS
Liquidity Ratios
Current ratio ( Current assets/Current Liabilities
	2015	2016	Ind. Av.
1.Current assets	127240	161259
2.Current Liabilities	50480	66129
3. Current Ratio(1/2)	2.52	2.44	2.58
Quick ratio= (Current assets-Inventories)/Current Liabilities
1.Current assets	127240	161259
2.Inventories	56000	84000
3.Quick assets(1-2)	71240	77259
4.Current Liabilities	50480	66129
5. Quick ratio (3/4)	1.41	1.17	1.53
Asset Management Ratios
Inventory Turnover(Total COGS/Inventories)	298000/56000=	300000/84000=
(Times in a Year)	5.32	3.57	7.69
Days Sales O/s (365/(Credit Sales/A/cs. Receivables))	365/(400000/48000)=	365/(420000/52500)=
(Days)	43.80	45.63	47.45
Fixed Assets Turnover(Sales/Total Fixed assets)	400000/200000=	420000/218400=
	2	1.92	2.04
Total Assets turnover(Sales/Total Assets)	400000/327240=	420000/379659=
	1.22	1.11	1.23
Debt Management Ratios
Debt ratio(total debt-to-assets)	58320/327240=	67662/379659=
	17.82%	17.82%	20%
Liabilities-to-assets ratio (total Liabilities/total assets	108800/327240=	133791/379659=
	33.25%	35.24%	32.1%
Times-interest -earned ratio(EBIT/Interest Expense)	62000/4460=	72740/5740=
	13.90	12.67	15.33
EBITDA coverage ratio(EBITDA + Lease Payments)/Interest Payments + Principal Repayments + Lease Payments
EBITDA:
Net Income	34524	40200
Add:Interest expense	4460	5740
Add: Taxes	23016	26800
Add:Depn. Amortsn.	18000	19660
EBITDA	80000	92400
EBITDA coverage ratio	(80000+20000)/(4460+5000+20000)=	(92400+20000)/(5740+5000+20000)=
	3.39	3.66	4.18
Profitability Ratios:
Profit Margin(net Income/Sales)	34524/400000=	40200/420000=
	8.63%	9.57%	8.80%
Basic Earning Power (EBIT/Total assets)	62000/327240=	72740/379659=
	18.95%	19.16%	19.48%
Return on Assets(Net income/Total assets)	34524/327240=	40200/379659=
	10.55%	10.59%	10.03%
Return on Equity(net Income/Total Equity)	34524/218440=	40200/245868=
	15.80%	16.35%	16.10%
Market Value Ratios:
Earnings per share (net Income/Total no.of common shares o/s	34524/4000=	40200/4052=
	8.63	9.92	NA
Price-to Earnings ratio(market price per share/Earnings per share	96/8.63=	90/9.92=
	11.12	9.07	10.65
Cash flow per share
Price-to-cash flow rate
Book value per share(stockholders'equity/No.of shares o/s)	218440/4000=	245868/4020=
	54.61	61.16	NA
Market to Book ratio (market price/book value )per share	96/54.61=	90/61.16=
	1.76	1.47	1.72

a. Liquidity position:Compared to the Industry average, both the current & quick ratios have been less in both the years. Both ratios have worsened in 2016 , due to increase in Notes payable.
b. Asset management ratios:
Inventory turnover is very low (compared to industry average),in both years-decreasing even more in 2016--on the whole,indicating excess inventory held , for the type of industry.
Receivables collection are better than the industry average--as indicated by the lesser no.of days , it had taken to be collected in both the years.
$ sales generated per $ of fixed assets , has decreased in 2016 , after being on par with the industry in 2015.
$ sales generated per $ of total assets , also has decreased in 2016 , after being on par with the industry in 2015.
Investment in inventory & fixed assets seems to be on the higher side compared to industry.
c. Profitabilty ratios:
Overall profit margin , better than industry avearge.
Basic earning power has been less(compared to the industry average) in both the years, probably due to more investment in fixed assets
ROA is better than the industry average in both the years
ROE is almost on par in 2015 & increased in 2016
Overall, profitability is good.

d. DuPont equation Analysis:
	ROE=	Profit margin*	Total assets turnover*	Equity Multiplier
2015		34524/400000=	400000/327240=	327240/218440=
		8.63%	1.22	1.4981
	15.80%
2016		40200/420000=	420000/379659=	379659/245868=
		9.57%	1.11	1.5442
	16.35%

Joshua & White Technologies ,Dec.31, Balance sheets
(in '000s of $)
e. Common- size balance sheet	2015	% to Total assets	2016	% to Total assets	Effect
Assets
Cash & Cash equivalents	20000	6.11%	21000	5.53%	Decrease
Short-term Investments	3240	0.99%	3759	0.99%	Same
Accounts Receivable	48000	14.67%	52500	13.83%	Decrease
Investments	56000	17.11%	84000	22.13%	Increase
Total Current assets	127240	38.88%	161259	42.47%	Increase
Net Fixed assets	200000	61.12%	218400	57.53%	Decrease
Total Assets	327240	100.00%	379659	100.00%
Liabilities & Equity
Accounts payable	32000	9.78%	33600	8.85%	Decrease
Accruals	12000	3.67%	12600	3.32%	Decrease
Notes payable	6480	1.98%	19929	5.25%	Increase
Total Current liabilities	50480	15.43%	66129	17.42%	Increase
Long-term debt	58320	17.82%	67662	17.82%	Same
Total Liabilities	108800	33.25%	133791	35.24%	Increase
Common stock	178440	54.53%	183793	48.41%	Decrease
Retained Earnings	40000	12.22%	62075	16.35%	Increase
Total Common equity	218440	66.75%	245868	64.76%	Decrease
Total Liabilities & Equity	327240	100.00%	379659	100.00%
Income statement
Sales	400000	100.00%	420000	100.00%
COGS	298000	74.50%	300000	71.43%	Decrease
Depn. & Amortsn.	18000	4.50%	19660	4.68%	Increase
Other Opg. Exp.	22000	5.50%	27600	6.57%	Increase
EBIT	62000	15.50%	72740	17.32%	Increase
Interest expense	4460	1.12%	5740	1.37%	Increase
EBT	57540	14.39%	67000	15.95%	Increase
Taxes at 40%	23016	5.75%	26800	6.38%	Increase
Net Income	34524	8.63%	40200	9.57%	Increase
Common dividends (as % of net Income )	17262	50%	18125	45%	Decrease
Addition to retained earnings (as % of net Income)	17262	50%	22075	55%	Increase

Joshua & White Technologies ,Dec.31, Balance sheets
(in '000s of $)
f. % change in 2016 from 2015	2015(Base)	2016	(2016-2015)/2015(%)
Assets
Cash & Cash equivalents	20000	21000	5.00%
Short-term Investments	3240	3759	16.02%
Accounts Receivable	48000	52500	9.38%
Investments	56000	84000	50.00%
Total Current assets	127240	161259	26.74%
Net Fixed assets	200000	218400	9.20%
Total Assets	327240	379659	16.02%
Liabilities & Equity
Accounts payable	32000	33600	5.00%
Accruals	12000	12600	5.00%
Notes payable	6480	19929	207.55%
Total Current liabilities	50480	66129	31.00%
Long-term debt	58320	67662	16.02%
Total Liabilities	108800	133791	22.97%
Common stock	178440	183793	3.00%
Retained Earnings	40000	62075	55.19%
Total Common equity	218440	245868	12.56%
Total Liabilities & Equity	327240	379659	16.02%
Income statement
Sales	400000	420000	5.00%
COGS	298000	300000	0.67%
Depn. & Amortsn.	18000	19660	9.22%
Other Opg. Exp.	22000	27600	25.45%
EBIT	62000	72740	17.32%
Interest expense	4460	5740	28.70%
EBT	57540	67000	16.44%
Taxes at 40%	23016	26800	16.44%
Net Income	34524	40200	16.44%
Common dividends (as % of net Income )	17262	18125	5.00%
Addition to retained earnings (as % of net Income)	17262	22075	27.88%
% change(increase) in assets & % change in profitability (Increase) are almost equal---a little higher than 16%