IS Excel Participant - Simplified v2 (3)
xlsx
keyboard_arrow_up
School
Guilford Technical Community College *
*We aren’t endorsed by this school
Course
100
Subject
Finance
Date
Jan 9, 2024
Type
xlsx
Pages
9
Uploaded by UltraCrocodile1962
Participant name:
Last Name, First Name
Davis Industries - Financial Summary
FYE July 31,
$ in millions
2015
2016
2017
Revenue
$353.1
$415.8
17.7%
-100.0%
Cost of Goods Sold
188.0
215.2
0.0
Gross Profit
165.1
200.6
Gross margin
46.8%
48.3%
#DIV/0!
Total operating expenses
116.3
142.6
0.0
Operating income
48.8
58.0
Operating margin
13.8%
14.0%
#DIV/0!
Net income
27.8
34.9
Net profit margin
7.9%
8.4%
#DIV/0!
Cash and cash equivalents
$19.8
$26.8
Accounts receivable
43.5
58.4
Inventory
45.1
57.3
Total assets
365.0
443.3
501.1
Accounts payable
10.1
16.4
Total debt
78.5
116.0
Equity
224.6
249.7
Liabilities and equity
365.0
443.3
501.1
Income Statement
:
% growth
Balance Sheet
:
FYE July 31,
$ in millions
2015
2016
2017
Cash flow from operations
$39.1
$49.0
Capital expenditures
(7.9)
(11.4)
Free Cash Flow
31.1
37.5
0.0
Acquisitions
(20.4)
(65.0)
Dividends paid
(2.6)
(3.1)
Adjusted EBITDA
$70.2
$86.3
$99.8
Total Debt / EBITDA
1.1x
1.3x
1.3x
Debt to total capitalization
25.9%
31.7%
30.3%
FCF / Total Debt
39.7%
32.4%
34.2%
Cash Flow Statement
:
EBITDA calculation
:
Credit ratios
:
Sources ($mm)
Before Tran
New Revolving Credit Facility
126.0
($mm)
Amount
Total Sources
$126.0
Tranche A
80.0
Tranche B
46.0
Uses ($mm)
New Revolving Credit Facility
- Repay Tranche A
80.0
Total Debt
126.0
Repay Tranche B
46.0
Total Uses
$126.0
Adjusted EBITDA
$99.8
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
nsaction
After Transaction
x EBITDA
Amount
0.8x - 0.5x - - - 1.3x 0.0 - $99.8 EBITDA
Participant name:
Upton, Grace Collateral description
Eligible collateral value ($k)
Authorized advance rate (%)
Accounts receivable
69,078.00
80%
Inventories
61,795.00
50%
Property and equipment, net
55,211.00
50%
Total collateral available
Less: (commitments)
Excess / (deficiency)
Available collateral ($k)
55,262.40
30,897.50
27,605.50
113,765.40
126,000.00
-12,234.60
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Audited Management Projections
JPMorgan Extrapolated
CAGR
Base Case
2015A
2016A
2017A
2018P
2019P
2020P
2021P
2022P
2023P
2024P
2025P
2026P
2027P
CAGR: 15-17
CAGR: 18-22
x
Revenue
353.1 415.8 480.3 525.0 588.0 646.8 646.8 646.8 646.8 646.8 646.8 646.8 646.8 x
% Growth
17.8%
15.5%
9.3%
12.0%
10.0%
0.0%
0.0%
0.0%
0.0%
0.24%
0.17%
(-) Cost of Goods Sold
188.0 215.2 242.5 267.8 299.9 329.9 - - - - - - - % of Revenue
53.3%
51.8%
50.5%
51.0%
51.0%
51.0%
0.0%
0.0%
0.0%
0.0%
0.20%
-100.00%
x Gross Profit Margin
165.1 200.6 237.7 257.3 288.1 316.9 646.8 646.8 646.8 646.8 646.8 646.8 646.8 x GPM Margin %
46.8%
48.3%
49.5%
49.0%
49.0%
49.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
x
(-) Operating Expenses
94.9 114.3 137.9 152.3 170.5 187.6 - - - - - - - x
% of Revenue
26.9%
27.5%
28.7%
29.0%
29.0%
29.0%
0.0%
0.0%
0.0%
0.0%
0.29%
-100.00%
EBITDA
70.2 86.3 99.8 105.0 117.6 129.4 646.8 646.8 646.8 646.8 646.8 646.8 646.8 EBITDA Margin %
19.9%
20.8%
20.8%
20.0%
20.0%
20.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
Income Taxes
16.4 19.8 19.9 20.94 23.45 25.80 129.00 129.00 129.00 129.00 129.00 129.00 129.00 x
% of EBITDA
23.4%
22.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
19.9%
x
Cash Flow
x
Capital Expenditures
7.9 11.4 16.8 18.4 20.6 22.6 - - - 22.6 22.6 22.6 22.6 % of Revenue
2.3%
2.8%
3.5%
3.5%
3.5%
3.5%
3.5%
3.5%
3.5%
3.5%
0.59%
-100.00%
Dividends
2.65 3.12 3.60 4.2 4.7 5.2 - - - 5.2 5.2 5.2 5.2 % of Revenue
0.8%
0.8%
0.8%
0.8%
0.8%
0.8%
0.8%
0.8%
0.8%
0.8%
0.24%
-100.00%
x
Balance Sheet
x
Accounts Receivable
43.5 58.4 69.1 75.5 84.6 93.0 - - - - - - - DSO
45.0 51.3 52.5 52.5 52.5 52.5 0.0%
0.0%
0.0%
0.0%
0.36%
-100.00%
Inventory
45.1 57.3 61.8 68.2 76.4 84.0 - - - - - - - DIO
87.5 97.3 93.0 93.0 93.0 93.0 0.0%
0.0%
0.0%
0.0%
0.25%
-100.00%
Accounts Payable 10.12 16.45 17.17 18.96 21.23 23.36 - - - - - - - DPO
19.6 27.9 25.8 25.8 25.8 25.8 0.0%
0.0%
0.0%
0.0%
0.42%
-100.00%
x
Δ in NWC
(14.43) (11.08) (14.97) (13.98) 153.73 - - - - - - x
CAGR: 23-27
Year Ended July 31,
FCF Model ($mm)
2017PF
2018P
2019P
2020P
2021P
2022P
2023P
2024P
2025P
2026P
2027P
Revenue
480.3 525.0 588.0 646.8 646.8 646.8 646.8 646.8 646.8 646.8 646.8 % Growth
15.5%
9.3%
12.0%
10.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
0.0%
EBITDA
99.8 105.0 117.6 129.4 646.8 646.8 646.8 646.8 646.8 646.8 646.8 % Margin
20.8%
20.0%
20.0%
20.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
100.0%
Interest Expense
(5.4) (3.8) (2.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0) (1.0)
Cash Tax Expense
(20.9) (23.5) (25.8) (129.0) (129.0) (129.0) (129.0) (129.0) (129.0) (129.0)
Δ in NWC
(11.1) (15.0) (14.0) 153.7 - - - - - - Capex
(18.4) (20.6) (22.6) - - - (22.6) (22.6) (22.6) (22.6)
Dividend payments
(4.2) (4.7) (5.2) - - - (5.2) (5.2) (5.2) (5.2)
Free cash flow
46.6 51.9 60.8 670.5 516.8 516.8 489.0 489.0 489.0 489.0 Cumulative free cash flow
46.6 98.6 159.3 829.9 1,346.7 1,863.5 2,352.5 2,841.5 3,330.4 3,819.4 1867.0%
7-yr Payout
Mandatory debt repayments
- - - - - - - - - - Cash available for debt service
46.6 51.9 60.8 670.5 516.8 516.8 489.0 489.0 489.0 489.0 Cumulative free cash flow for debt service
46.6 98.6 159.3 829.9 1,346.7 1,863.5 2,352.5 2,841.5 3,330.4 3,819.4 Year Ended July 31,
Covenant Projections ($mm)
2017PF
2018E
2019E
2020E
2021E
2022E
2023E
2024E
2025E
2026E
2027E
R/C facility
126.0 79.4 27.4 - - - - - - - - Term Loan A
- - - - - - - - - - - Total Debt
126.0 79.4 27.4 - - - - - - - - Leverage Ratio
1.26x
0.76x
0.23x
0.00x
0.00x
0.00x
0.00x
0.00x
Leverage Covenant
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
EBITDA Cushion ($)
63.78 82.32 109.76 129.36 646.80 646.80 646.80 646.80 EBITDA Cushion (%)
63.92%
78.40%
93.33%
100.00%
100.00%
100.00%
100.00%
100.00%
Interest Coverage Ratio
18.44x
27.79x
59.99x
129.36x
646.80x
646.80x
646.80x
646.80x
Interest Coverage Covenant
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
3.50x
EBITDA Cushion ($)
80.84 91.78 110.74 125.86 643.30 643.30 643.30 643.30 EBITDA Cushion (%)
81.02%
87.41%
94.17%
97.29%
99.46%
99.46%
99.46%
99.46%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Documents
Related Questions
A company reported the following data for the year ending 2018:
Description
Amount
Sales
Sales discount
Sales returns and allowances
$400,000
$16,000
$13,000
$117,000
$153,000
|Cost of goods sold
|Operating expense
Income tax expense
$23,750
Compute the amount of gross profit to be reported on the income statement.
a. $101,000
b. $117,000
c. $254,000
d. $124,750
Answer
O O O O
arrow_forward
Required information
For Year Ended December 31
Sales
Cost of goods sold
Other operating expenses
Current Year
$ 411, 225
209,550
12, 100
9,525
$ 673,500
642, 400
$31,100
$ 1.90
:. -. - == .......
9 US
Sa
M
arrow_forward
Operating and Nonoperating Items in Boston Scientific's Income Statement
EXHIBIT 3.6
For Year Ended December 31, S mllllons
2018
2017
2016
Net sale .
Cost of products sold
$9,823
$9,048
2,593
$8,386
2,424
2,813
Gross profit.
Operating expenses
Selllng, general and admlnIstratlve expenses.
Research and development expenses
Royalty expense
Amortlzatlon expense
Intanglble asset Impalrment charges
ContIngent conslderatlon expense (beneflt) .
RestructurIng charges.
LItigatlon-related charges
7,010
6,455
5,962
3,569
1,113
3,294
3,099
997
920
70
68
79
599
565
545
35
4
11
(21)
(80)
29
36
37
28
103
285
804
Operating expenses -
5,504
5,170
5,515
Operating income .
Other expense (income)
Interest expense
1,506
1,285
447
241
229
233
Other expense (income), net.
(156)
124
37
Income before income taxes
1,422
932
177
Income tax expense (benefit) .
(249)
828
(170)
Net income.
$1,671
$
104
$ 347
Calculate the operating expense margin ratio for 2016
arrow_forward
expert of general account answer
arrow_forward
Russell Department Stores, Inc.
Income Statement Compared with Industry Average
Year Ended December 31, 2018
Russell
Industry Average
Net sales revenue
$780,000
100.0 %
Cost of goods sold
524,940
65.8
Gross Profit
255,060
34.2
Operating Expenses
162,240
19.7
Operating Income
92,820
14.5
Other Expenses
7,800
0.4
Net Income
85,020
14.1 %
MORE INFO: Russell/industry average
Total Assets
$480,000
100.0 %
total Liabilities
327,360
64.7
total Liabilities and Stockholders' Equity $480,000 100.0 %
SOLVE for % of total (round total to one decimal place x.x%)
Amount
Percent of total
Net Sales Revenue
780,000
Cost of goods sold
524,940
gross profit
255,060
operating expenses
162240
operating income
92820
other expenses
7800
arrow_forward
please explain the result of income statement that provided in image below
arrow_forward
Give correct gross margin
arrow_forward
Operating and Nonoperating Items in Boston Scientific's Income Statement
EXHIBIT 3.6
For Year Ended December 31, S mllllons
2018
2017
2016
$9,823
2,813
$9,048
2,593
$8,386
2,424
Net sales
Cost of products sold
Gross profit.
Operating expenses
Selllng, general and admlnlstratlve expenses.
Research and development expenses
Royalty expense
Amortlzatlon expense
Intanglble asset Impalrment charges.
ContIngent conslderatlon expense (beneflt)
Restructurlng charges.
Litigatlon-related charges
7,010
6,455
5,962
3,569
3,294
3,099
1,113
997
920
70
68
79
599
565
545
35
11
(21)
(80)
29
36
37
28
103
285
804
Operating expenses
5,504
5,170
5,515
Operating income
Other expense (income)
Interest expense
1,506
1,285
447
241
229
233
Other expense (income), net.
(156)
124
37
1,422
(249)
Income before income taxes
932
177
Income tax expense (benefit) .
828
(170)
Net income
$1,671
24
104
2$
347
Calculate the profit margin ratio for 2016
arrow_forward
2014
2013
Revenue
$14,147,293
$13,566,296
Cost of goods sold
$-8,447,560
$-8,131,586
Selling, general, andadministrative expenses
$-998,843
$-980,283
Depreciation
$-1,497,919
$-1,472,544
EBIT
$3,202,971
$2,981,883
Interest expense
$-376,259
$-355,627
Taxes
$-1,074,151
$-997,977
Net income
$1,752,561
$1,628,279
Right-click on the table and select Copy to Clipboard and then right-click the
highlighted texts in the popup dialogue box and select Copy in order to paste its
contents into a
spreadsheet.
Tyler Toys, Inc.
Balance Sheet as of December 31, 2013 and 2014
ASSETS
2014
2013
LIABILITIES
2014
2013
Current assets
Current liabilities
Cash
$191,068
$187,833
Accounts payable
$1,546,427
$1,455,115
Investments…
arrow_forward
KORBIN COMPANY
Comparative Income Statements
For Years Ended December 31, 2019, 2018, and 2017
2019
2018
2017
Sales
$
459,716
$
352,180
$
244,400
Cost of goods sold
276,749
223,282
156,416
Gross profit
182,967
128,898
87,984
Selling expenses
65,280
48,601
32,261
Administrative expenses
41,374
30,992
20,285
Total expenses
106,654
79,593
52,546
Income before taxes
76,313
49,305
35,438
Income tax expense
14,194
10,108
7,194
Net income
$
62,119
$
39,197
$
28,244
KORBIN COMPANY
Comparative Balance Sheets
December 31, 2019, 2018, and 2017
2019
2018
2017
Assets
Current assets
$
58,718
$
39,300
$
52,535
Long-term investments
0
500
4,280
Plant assets, net
109,047
99,561
58,646
Total assets
$
167,765
$
139,361
$
115,461
Liabilities and Equity
Current liabilities
$
24,494…
arrow_forward
Refer to the following mentioned data.
Net sales
Cost of products sold
Gross margin
2017
2016
2015
Required:
a. Calculate the gross profit ratio for each of the past three years. (Round your answers to 2
decimal places.)
(In millions)
2016
2017
2015
$34,894
$30,411
$26,113
14,318
13,376
13,767
$20,576 $17,035 $12,346
Cost of goods sold
Gross profit
58.94 %
%
%
b. Assume that Campbell's net sales for the first four months of 2018 totaled $12.79 billion.
Calculate an estimated cost of goods sold and gross profit for the four months, using the gross
profit ratio for 2017. (Round Intermediate calculations to 2 decimal places. Enter your answers
in millions rounded to nearest whole number (1.e., 5,000,000 should be entered as 5).)
million
million
arrow_forward
Net sales from Following inform sttement
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you

Financial & Managerial Accounting
Accounting
ISBN:9781285866307
Author:Carl Warren, James M. Reeve, Jonathan Duchac
Publisher:Cengage Learning

Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning
Related Questions
- A company reported the following data for the year ending 2018: Description Amount Sales Sales discount Sales returns and allowances $400,000 $16,000 $13,000 $117,000 $153,000 |Cost of goods sold |Operating expense Income tax expense $23,750 Compute the amount of gross profit to be reported on the income statement. a. $101,000 b. $117,000 c. $254,000 d. $124,750 Answer O O O Oarrow_forwardRequired information For Year Ended December 31 Sales Cost of goods sold Other operating expenses Current Year $ 411, 225 209,550 12, 100 9,525 $ 673,500 642, 400 $31,100 $ 1.90 :. -. - == ....... 9 US Sa Marrow_forwardOperating and Nonoperating Items in Boston Scientific's Income Statement EXHIBIT 3.6 For Year Ended December 31, S mllllons 2018 2017 2016 Net sale . Cost of products sold $9,823 $9,048 2,593 $8,386 2,424 2,813 Gross profit. Operating expenses Selllng, general and admlnIstratlve expenses. Research and development expenses Royalty expense Amortlzatlon expense Intanglble asset Impalrment charges ContIngent conslderatlon expense (beneflt) . RestructurIng charges. LItigatlon-related charges 7,010 6,455 5,962 3,569 1,113 3,294 3,099 997 920 70 68 79 599 565 545 35 4 11 (21) (80) 29 36 37 28 103 285 804 Operating expenses - 5,504 5,170 5,515 Operating income . Other expense (income) Interest expense 1,506 1,285 447 241 229 233 Other expense (income), net. (156) 124 37 Income before income taxes 1,422 932 177 Income tax expense (benefit) . (249) 828 (170) Net income. $1,671 $ 104 $ 347 Calculate the operating expense margin ratio for 2016arrow_forward
- expert of general account answerarrow_forwardRussell Department Stores, Inc. Income Statement Compared with Industry Average Year Ended December 31, 2018 Russell Industry Average Net sales revenue $780,000 100.0 % Cost of goods sold 524,940 65.8 Gross Profit 255,060 34.2 Operating Expenses 162,240 19.7 Operating Income 92,820 14.5 Other Expenses 7,800 0.4 Net Income 85,020 14.1 % MORE INFO: Russell/industry average Total Assets $480,000 100.0 % total Liabilities 327,360 64.7 total Liabilities and Stockholders' Equity $480,000 100.0 % SOLVE for % of total (round total to one decimal place x.x%) Amount Percent of total Net Sales Revenue 780,000 Cost of goods sold 524,940 gross profit 255,060 operating expenses 162240 operating income 92820 other expenses 7800arrow_forwardplease explain the result of income statement that provided in image belowarrow_forward
- Give correct gross marginarrow_forwardOperating and Nonoperating Items in Boston Scientific's Income Statement EXHIBIT 3.6 For Year Ended December 31, S mllllons 2018 2017 2016 $9,823 2,813 $9,048 2,593 $8,386 2,424 Net sales Cost of products sold Gross profit. Operating expenses Selllng, general and admlnlstratlve expenses. Research and development expenses Royalty expense Amortlzatlon expense Intanglble asset Impalrment charges. ContIngent conslderatlon expense (beneflt) Restructurlng charges. Litigatlon-related charges 7,010 6,455 5,962 3,569 3,294 3,099 1,113 997 920 70 68 79 599 565 545 35 11 (21) (80) 29 36 37 28 103 285 804 Operating expenses 5,504 5,170 5,515 Operating income Other expense (income) Interest expense 1,506 1,285 447 241 229 233 Other expense (income), net. (156) 124 37 1,422 (249) Income before income taxes 932 177 Income tax expense (benefit) . 828 (170) Net income $1,671 24 104 2$ 347 Calculate the profit margin ratio for 2016arrow_forward2014 2013 Revenue $14,147,293 $13,566,296 Cost of goods sold $-8,447,560 $-8,131,586 Selling, general, andadministrative expenses $-998,843 $-980,283 Depreciation $-1,497,919 $-1,472,544 EBIT $3,202,971 $2,981,883 Interest expense $-376,259 $-355,627 Taxes $-1,074,151 $-997,977 Net income $1,752,561 $1,628,279 Right-click on the table and select Copy to Clipboard and then right-click the highlighted texts in the popup dialogue box and select Copy in order to paste its contents into a spreadsheet. Tyler Toys, Inc. Balance Sheet as of December 31, 2013 and 2014 ASSETS 2014 2013 LIABILITIES 2014 2013 Current assets Current liabilities Cash $191,068 $187,833 Accounts payable $1,546,427 $1,455,115 Investments…arrow_forward
- KORBIN COMPANY Comparative Income Statements For Years Ended December 31, 2019, 2018, and 2017 2019 2018 2017 Sales $ 459,716 $ 352,180 $ 244,400 Cost of goods sold 276,749 223,282 156,416 Gross profit 182,967 128,898 87,984 Selling expenses 65,280 48,601 32,261 Administrative expenses 41,374 30,992 20,285 Total expenses 106,654 79,593 52,546 Income before taxes 76,313 49,305 35,438 Income tax expense 14,194 10,108 7,194 Net income $ 62,119 $ 39,197 $ 28,244 KORBIN COMPANY Comparative Balance Sheets December 31, 2019, 2018, and 2017 2019 2018 2017 Assets Current assets $ 58,718 $ 39,300 $ 52,535 Long-term investments 0 500 4,280 Plant assets, net 109,047 99,561 58,646 Total assets $ 167,765 $ 139,361 $ 115,461 Liabilities and Equity Current liabilities $ 24,494…arrow_forwardRefer to the following mentioned data. Net sales Cost of products sold Gross margin 2017 2016 2015 Required: a. Calculate the gross profit ratio for each of the past three years. (Round your answers to 2 decimal places.) (In millions) 2016 2017 2015 $34,894 $30,411 $26,113 14,318 13,376 13,767 $20,576 $17,035 $12,346 Cost of goods sold Gross profit 58.94 % % % b. Assume that Campbell's net sales for the first four months of 2018 totaled $12.79 billion. Calculate an estimated cost of goods sold and gross profit for the four months, using the gross profit ratio for 2017. (Round Intermediate calculations to 2 decimal places. Enter your answers in millions rounded to nearest whole number (1.e., 5,000,000 should be entered as 5).) million millionarrow_forwardNet sales from Following inform sttementarrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- Financial & Managerial AccountingAccountingISBN:9781285866307Author:Carl Warren, James M. Reeve, Jonathan DuchacPublisher:Cengage LearningCornerstones of Financial AccountingAccountingISBN:9781337690881Author:Jay Rich, Jeff JonesPublisher:Cengage Learning

Financial & Managerial Accounting
Accounting
ISBN:9781285866307
Author:Carl Warren, James M. Reeve, Jonathan Duchac
Publisher:Cengage Learning

Cornerstones of Financial Accounting
Accounting
ISBN:9781337690881
Author:Jay Rich, Jeff Jones
Publisher:Cengage Learning