(solution) Hi, Thank you for your previous work. Can you do a DuPont

(solution) Hi, Thank you for your previous work. Can you do a DuPont

Hi,

Thank you for your previous work.  Can you do a DuPont analysis for the data?

Ratio calculation HES Company Financial Statements Income Statements 2011 2011 2012 2013 $1,976,000 $2,074,800 $2,178,540 $2,287,467 $2,401,840 Cost of Sales 1,213,659 1,274,342 1,338,059 1,404,962 1,475,210 Gross Profit $762,341 $800,458 $840,481 $882,505 $926,630 6,000 6,300 6,615 6,946 7,293 Advertising & Promotion 15,000 12,360 12,731 13,113 Bank Charges 41,496 43,571 45,749 48,037 50,439 246,643 254,042 261,663 269,513 277,598 Consulting Fees 2,400 0 0 0 0 Insurance 1,000 1,050 1,103 1,158 1,216 336,000 336,000 336,000 336,000 500 500 500 500 500 Licenses & Fees 500 510 520 531 541 Maintenance 600 612 624 637 649 Miscellaneous 1,800 1,836 1,873 1,910 1,948 Office supplies 2,700 2,754 2,809 2,865 742 764 787 810 Telephone 1,800 1,836 1,873 1,910 1,948 Utilities 4,200 4,410 4,631 4,862 5,105 Website 1,800 1,800 1,800 1,800 1,800 Total Operating Exp. 663,159 668,323 679,255 690,569 702,276 EBIDTA $99,182 $132,135 $161,226 $191,936 $224,354 4,916 4,916 4,916 4,916 4,916 $94,266 $127,219 $156,310 $187,020 $219,438 Interest Expense 43,199 40,274 37,059 33,524 29,639 Earnings Before Taxes 51,067 86,945 119,251 153,496 189,799 Income Taxes 17,873 30,431 41,738 53,724 66,429 $56,514 $77,513 $99,772 $123,370 2015
2014 (current yr) NA
NA
9.00 8.18
2.01
5.15 4.75
1.71
2.87 4.89
1.85
1.69 5.13
2.09
1.04 NA
2.86
58.38 NA
3.00
71.71 NA
3.15
90.70 NA
3.15
119.74 NA
3.15
169.31 0.90
9.00
2.18 0.74
5.15
3.16 0.59
2.87
4.22 0.50
1.69
5.58 0.41
1.04
7.40 63.83%
63.83%
1.68% 66.34%
66.34%
2.72% 54.70%
54.70%
3.56% 45.51%
45.51%
4.36% 38.67%
38.67%
5.14% 2,923 720 2013 336,000 Legal & Professional 2012 13,506 Revenues Accounting Compensation & Benefits Lease – Facilities Security Depreciation
Operating Profit Net Income $33,194 2014 2015 (current yr) Liquidity ratio
Current ratio
Quick ratio
Debt to equity ratio
Asset management ratio
Receivable turnover
Inventory turnover
Fixed asset turnover
Solvency ratio
debt to total asset
debt to equity
times interest earned
Profitability ratio
ROE
RNOA
Profit margin Balance Sheets
2010 2011 2012 2013 2014 2015 Cash $33,150 $110,697 $210,697 $243,259 $293,834 $363,370 Inventory 425,000 425,000 424,781 446,020 468,321 491,737 28,000 28,000 28,000 28,000 28,000 28,000 $486,150 $563,697 $663,478 $717,279 $790,155 $883,107 33,850 33,850 33,850 33,850 33,850 33,850 0 4,916 9,832 14,747 19,664 24,579 33,850 28,934 24,018 19,103 14,186 9,271 $520,000 $592,631 $687,496 $736,382 $804,341 $892,378 0 68,908 139,654 146,637 153,968 161,667 Long Term Debt 468,000 438,529 406,133 370,522 331,377 288,346 Total Liabilities 468,000 507,437 545,787 517,159 485,345 450,013 52,000 52,000 52,000 52,000 52,000 52,000 0 33,194 89,709 167,223 266,996 390,365 $520,000 $592,631 $687,496 $736,382 $804,341 $892,378 Prepaid Leases Total Current Assets
Fixed Assets
Less: Depreciation
Net Fixed Assets
Total Assets
Accounts Payable Owner's Equity Paid-in Capital Retained Earnings
Total Liabilities & Equity 2011
1,976,000
1,213,659
762,341 2012
2,074,800
1,274,342
800,458 2013
2,178,540
1,338,059
840,481 6,000
15,000
41,496
246,643
2,400
1,000
336,000
500
500
600
1,800
2,700
720
1,800
4,200
1,800
663,159
99,182
4,916
94,266
43,199
51,067
17,873
33,194 6,300
12,360
43,571
254,042
0
1,050
336,000
500
510
612
1,836
2,754
742
1,836
4,410
1,800
668,323
132,135
4,916
127,219
40,274
86,945
30,431
56,514 6,615
12,731
45,749
261,663
0
1,103
336,000
500
520
624
1,873
2,809
764
1,873
4,631
1,800
679,255
161,226
4,916
156,310
37,059
119,251
41,738
77,513 6,946
13,113
48,037
269,513
0
1,158
336,000
500
531
637
1,910
2,865
787
1,910
4,862
1,800
690,569
191,936
4,916
187,020
33,524
153,496
53,724
99,772 7,293
13,506
50,439
277,598
0
1,216
336,000
500
541
649
1,948
2,923
810
1,948
5,105
1,800
702,276
224,354
4,916
219,438
29,639
189,799
66,429
123,370 Cash
Inventory
Prepaid Leases Total Current Assets 2010
33,150
425,000
28,000
486,150 2011
110,697
425,000
28,000
563,697 2012
210,697
424,781
28,000
663,478 2013
243,259
446,020
28,000
717,279 2014
293,834
468,321
28,000
790,155 2015
363,370
491,737
28,000
883,107 Fixed Assets
Less: Depreciation
Net Fixed Assets
Total Assets 33,850
0
33,850
520,000 33,850
4,916
28,934
592,631 33,850
9,832
24,018
687,496 33,850
14,747
19,103
736,382 33,850
19,664
14,186
804,341 33,850
24,579
9,271
892,378 Accounts Payable
Long Term Debt
Total Liabilities
Owner's Equity Paid-in Capital Retained Earnings
Total Liabilities & Equity 0
468,000
468,000 68,908
438,529
507,437 139,654
406,133
545,787 146,637
370,522
517,159 153,968
331,377
485,345 161,667
288,346
450,013 52,000
0
520,000 52,000
33,194
592,631 52,000
89,709
687,496 52,000
167,223
736,382 52,000
266,996
804,341 52,000
390,365
892,378 Working Capital Investment
Cash From Operations
Interest Expense
Income Taxes
Net Cash Before Debt Pmt
Debt Payment
Change in Cash 2011
94,266
4,916
99,182
-68,908
168,090
43,199
17,873
107,018
29,471
77,547 2012
127,219
4,916
132,135
-70,966
203,101
40,274
30,431
132,396
32,396
100,000 2013
156,310
4,916
161,226
14,256
146,970
37,059
41,738
68,173
35,611
32,562 2014
187,020
4,916
191,936
14,968
176,968
33,524
53,724
89,720
39,145
50,575 2015
219,438
4,916
224,354
15,719
208,635
29,639
66,429
112,567
43,031
69,536 Cash Reconciliation
Beginning Cash
Change in Cash
Ending Cash 33,150
77,547
110,697 110,697
100,000
210,697 210,697
32,562
243,259 243,259
50,575
293,834 293,834
69,536
363,370 Cash from operations to total debt = 0.7683974166
2.2959327762
0.3833041828 1.4032378209
3.2809008293
0.5000849475 2.0916106749
4.3505221404
0.3966566088 2.9761364992
5.7253311061
0.5340382706 4.1624211343
7.5695536287
0.723557809 PE ratio =
Price to sales ratio =
Market to book value ratio = 1.5665481714
0.0263157895
1 0.9201259865
0.0410613071
1.6383461538 0.6708552114
0.0650476925
2.7251730769 0.5211883093
0.0958365738
4.2158269231 0.4214963119
0.1328131766
6.1345384615 5%
70.25%
13.97% 5%
37.16%
16.01% 5%
28.72%
7.11% 5%
23.65%
9.23% Revenues
Cost of Sales
Gross Profit
Accounting
Advertising & Promotion
Bank Charges
Compensation & Benefits
Consulting Fees
Insurance
Lease – Facilities
Legal & Professional
Licenses & Fees
Maintenance
Miscellaneous
Office supplies
Security
Telephone
Utilities
Website Total Operating Exp.
EBIDTA
Depreciation
Operating Profit
Interest Expense
Earnings Before Taxes
Income Taxes
Net Income 2014 2015 (current yr)
2,287,467
2,401,840
1,404,962
1,475,210
882,505
926,630 Balance Sheets Cash Flow Statements
Operating Profit
Add: Depreciation Times interest earned =
EBITDA coverage ratio = Sales growth ratio =
Income growth ratio =
Asset growth ratio = – Calculating z score:
A=Working capitl/Total assets
B=Retained Earnings/Total assets
C=EBIT/Total assets
D=Market value of equity/Total Liabilities
E=Sales/Total assets
Z score=1.2A+1.4B+3.3C+0.6D+1.0E -0.1162747139
0.0560112448
0.1590635657
0.1437555578
3.3342838967
3.8843330842 -0.1032238733
0.1304865774
0.1850468948
0.2061233811
3.0179084678
3.8110498097 0.0193595172
0.2270872998
0.2122675459
0.2977028227
2.9584373328
4.1786955683 0.0186090228
0.33194379
0.2325133246
0.3965929873
2.8439020266
4.3362039235 0.0176147328
0.4374435497
0.2459025211
0.4957148204
2.6915051693
4.4339710302