Financial Statement Analysis Hydrogenics case Assignment I have a homework. is a case from the book. it`s continued for other cases. I attached below the c

Financial Statement Analysis Hydrogenics case Assignment I have a homework. is a case from the book. it`s continued for other cases. I attached below the case and the template for old cases. read the cases carefully then do it Printout
Tuesday, April 30, 2019
3:40 PM
Quick Notes Page 1
Quick Notes Page 2
Quick Notes Page 3
The information on this page is intended to make the template easier to use. It describes requirements, specific design features, and hints for
overcoming challenges the designers encountered during development that resulted from the variety of ways financial statements are presented in
annual reports and/or 10K filings at www.sec.gov.
There are three requirements for making this template function properly:
1) Enable content (macros) by single-clicking on the
button displayed when opening the template file.
2) All financial statement data must be entered. If a line item does not have an explicit template line item match, include the amount as part
of a formula on an ‘Other…’ line item such as ‘Other current assets’ or ‘Other operating expenses’.
3) Ensure Analysis ToolPak add-in is checked/active.
Verify by typing Alt+T (Tools), then I (Add-Ins) is the easiest method. Alternatively, from the File tab, select Options, then Add-Ins, then
click
on the
button with ‘Excel Add-ins’ displayed Manage: drop-down list.
NOTE: If the Add-Ins option is unavailable, it will have to be installed from your original Microsoft Office/Excel CD.
Recommendations:
Set monitor resolution to 1024 x 768 or higher to minimize scrolling during data entry.
Use a formula statement [= n + n + n ] rather than a hand-calculated summary amount when combining multiple financial statement lines
into a single template cell. This will aid when troubleshooting entry errors that could result in mismatched check figures.
Use the TAB key to navigate between cells. The active cell is automatically moved to the next cell which accepts data.
Three buttons are common to each of the enterable financial statement worksheets –
It is important to understand their function because all sheets are protected to preserve the integrity of total totals and ratio formulas.
– Allows entry of data into appropriate cells and displays entry hints
Edit
Review
– Displays printable versions of financial statements and hides entry hints
– Clears values from all enterable cells and prepares the worksheet for data entry
Clear
The
worksheet should be completed first. It is enterable and also contains a
Clear
button.
The remaining worksheets –
are automatically generated by the template. No entry is required or permitted.
The template is flagged as ‘read-only’ to permit reuse. If you save your work, you will be prompted to rename the file.
worksheet supplemental information
Colored fields have special significance. Only cells with borders permit data entry.
– financial statement header information
– special data needed for ratios that cannot be found as line items on financial statements
– check figures for financial statement summary comparisons
An unbordered, colored cell on any sheet indicates Cover worksheet data has not been entered or a summary total on a financial statement
does not compare favorably to its associated Cover worksheet check figure.
– Amounts can usually be found in the ‘Notes to consolidated financial statements’ section of a company’s annual report or 10K.
Rent expense amounts are generally discussed under a note heading titled ‘Commitments’ or ‘Commitments and Contingencies’ and sometimes
included under a note heading titled ‘Leases’.
Rent Expense
– Amounts can usually be found on one of following statements: Statement of Stockholders’ Equity, Statement of Cash
Flows, or Statement of Operations. Dividends per share amounts are also often included in the ‘Notes to consolidated financial statements’ section
of a company’s annual report or 10K under note headings titled ‘Selected Financial Data’, ‘Five Year Summary Data’, or ‘Quarterly Financial Data’.
Dividends per Share
The information on this page is intended to make the template easier to use. It describes requirements, specific design features, and hints for
overcoming challenges the designers encountered during development that resulted from the variety of ways financial statements are presented in
annual reports and/or 10K filings at www.sec.gov.
– the template supports researching a company’s year-end stock price when following these steps:
a) Enter the company’s Stock Ticker Symbol
b) Enter the year end dates for the three years covered on the annual report
c) Copy
the contents from one year’s URL cell to the clipboard
End of Year Stock Price
d) Paste
the clipboard contents into an open browser’s Address field (Internet Explorer is shown)
e) Click the
button (or your browser’s equivalent) to access the Marketwatch web site for the company’s stock price
f)
Make sure the Split Adjusted Price (see red arrow below) is used, if available; otherwise, use the Closing Price
g)
If the market was closed on the day supplied by the URL, change the historical quote date (see red arrow below) to the next earlier
weekday until data is available.
h)
Repeat steps c) through g) for the remaining years
– If Gross Profit is not listed on the Income Statement/Statement of Operations, review financial data for COGS information and
enter a formula to net Sales and COGS. If there is no COGS data, enter the Net Sales amount as the Gross Profit amount. Service companies may
not display a Cost of Goods/Cost of Sales amount.
Gross Profit
are alternative titles for the same data
– Do not include Interest Expense or Other Income (Loss) items in this check figure; otherwise, the check figure will not compare
favorably with the Income Statement Operating Profit subtotal. These are non-operating items and should be entered below the operating profit
line.
Cost of Goods / COGS / Cost of Sales / Cost of Revenue
Operating Profit
Operating Profit / Operating Income / Earnings from Operations
are alternative titles for the same data
The information on this page is intended to make the template easier to use. It describes requirements, specific design features, and hints for
overcoming challenges the designers encountered during development that resulted from the variety of ways financial statements are presented in
annual reports and/or 10K filings at www.sec.gov.
worksheet supplemental information
Net Property, Plant, & Equipment (PPE) –
If the Balance Sheet provides only Net PPE, Accumulated Depreciation can usually be found in the ‘Notes
to consolidated financial statements’ section of a company’s annual report or 10K and should be added to Net PPE from the Balance Sheet then
entered in the template as the full book value of Property, plant, and equipment. This treatment permits an entry for Less: accumulated
depreciation (as a positive amount) in the template as well.
worksheet supplemental information
Interest Expense –
This line item must be segregated to ensure correct ratio calculations. Interest Expense is sometimes included in the ‘Other
Income (Expense/Loss)’ line item. In that situation, look in the ‘Notes to consolidated financial statements’ section to find the actual amount of
Interest Expense that should be entered and use a formula to exclude it from the ‘Other Income (Expense/Loss)’ line. If a company has no short- or
long-term debt, there will generally be no Interest Expense.
worksheet supplemental information
Interest Paid and Income Taxes Paid (Refunded) are generally found at
the end of the Cash Flow Statement. If not, look in the ‘Notes to
consolidated financial statements’.
and
worksheets supplemental information
To display the underlying formulas related to ratios, single-click the Show Formulas
To redisplay the ratios, single-click the
Show Ratios
button.
button.
The ‘Analysis ToolPak’ add-in must be installed and active.
Enter data on this sheet before other financial statement information.
Verify using keystroke sequence of Alt-T then I and ensure
‘Analysis ToolPak’ Add-In is checked
Company Name: Hydrogenics Corporation
Stock Ticker Symbol:
Use the ‘TAB’ key to move efficiently between enterable
(bordered) fields
HYGS
U.S. Stock Exchange:
Statement Year-end Dates:
12/31/2013
NASDAQ
Financial Reports Rounded to :
Thousands
Supplemental Ratio Requirements:
Variable
12/31/2012
2012
0
Rent expense (in thousands):
$
845
$
906
$
19.15
$
6.77
http://marketwatch.com/tools/qu http://marketwatch.com/tools/qu
otes/historical.asp?date=12%2 otes/historical.asp?date=12%2
F31%2F2013&symb=HYGS&si F31%2F2012&symb=HYGS&si
teid=mktw
teid=mktw
Check Figures:
Balance Sheet
Current Assets:
$
31,649
$
34,607
Income Statement
Sequence: Current yr (Col.B) to prior yrs (Cols. C & D)
Options: Thousands, Millions, Billions
2013
Dividends per share:
End of year stock price ( adjusted for splits ):
Marketwatch.com URL for stock prices:
Options: NYSE, AMEX, NASDAQ
Total Assets:
$
40,070
$
41,877
Current Liabilities:
$
22,528
$
26,054
Total Stockholders’ Equity:
$
6,161
$
4,307
Type all entries, DO NOT cut & paste values 2013
Split adjusted prices, if applicable, appear under the web
site’s price chart
0 Select a URL cell, copy the contents and paste into your
browser’s Address area for each year. If the market was not
open on the day provided, modify the web site’s quote date to
the previous weekday.
Enter a negative amount to bracket check figures
Gross Profit = Net Sales – Cost of Goods Sold
Gross Profit:
Use net sales amount when cost of goods sold isn’t available
Operating Profit:
Net Profit:
Cash Flow
Net Flows from Operations:
Net Flows from Investing Activity:
Net Flows from Financing Activity:
No enterable fields exist below this point
Martin Luther King Jr. Day 2000
Washington’s Birthday 2000
17-Jan-00
21-Feb-00
Good Friday 2000
21-Apr-00
Memorial Day 2000
29-May-00
Independence Day 2000
Labor Day 2000
4-Jul-00
4-Sep-00
Thanksgiving Day 2000
23-Nov-00
Christmas 2000
25-Dec-00
New Year’s Day 2001
Martin Luther King Jr. Day 2001
Washington’s Birthday 2001
1-Jan-01
15-Jan-01
19-Feb-01
Good Friday 2001
13-Apr-01
Memorial Day 2001
28-May-01
Independence Day 2001
Labor Day 2001
World Trade Center Observance
4-Jul-01
3-Sep-01
11-Sep-01
World Trade Center Observance
12-Sep-01
World Trade Center Observance
13-Sep-01
World Trade Center Observance
14-Sep-01
Thanksgiving Day 2001
22-Nov-01
Christmas 2001
25-Dec-01
New Year’s Day 2002
Martin Luther King Jr. Day 2002
Washington’s Birthday 2002
1-Jan-02
21-Jan-02
18-Feb-02
Good Friday 2002
29-Mar-02
Memorial Day 2002
27-May-02
Independence Day 2002
Labor Day 2002
4-Jul-02
2-Sep-02
Thanksgiving Day 2002
28-Nov-02
Christmas 2002
25-Dec-02
New Year’s Day 2003
Martin Luther King Jr. Day 2003
Washington’s Birthday 2003
1-Jan-03
20-Jan-03
17-Feb-03
Good Friday 2003
18-Apr-03
Memorial Day 2003
26-May-03
Independence Day 2003
Labor Day 2003
4-Jul-03
1-Sep-03
Thanksgiving Day 2003
27-Nov-03
Christmas 2003
25-Dec-03
New Year’s Day 2004
Martin Luther King Jr. Day 2004
President’s Day 2004
Good Friday 2004
1-Jan-04
19-Jan-04
16-Feb-04
9-Apr-04
Memorial Day 2004
31-May-04
Ronald Reagan Day of Mourning
11-Jun-04
Independence Day 2004
Labor Day 2004
5-Jul-04
6-Sep-04
Thanksgiving Day 2004
25-Nov-04
Christmas 2004
24-Dec-04
Martin Luther King Jr. Day 2005
17-Jan-05
Washington’s Birthday 2005
21-Feb-05
Good Friday 2005
25-Mar-05
Memorial Day 2005
30-May-05
Independence Day 2005
Labor Day 2005
4-Jul-05
5-Sep-05
Thanksgiving Day 2005
24-Nov-05
Christmas 2005
26-Dec-05
New Year’s Day 2006
Martin Luther King Jr. Day 2006
Washington’s Birthday 2006
2-Jan-06
16-Jan-06
20-Feb-06
Good Friday 2006
14-Apr-06
Memorial Day 2006
29-May-06
Independence Day 2006
Labor Day 2006
4-Jul-06
4-Sep-06
Thanksgiving Day 2006
23-Nov-06
Christmas 2006
25-Dec-06
New Year’s Day 2007
Gerald Ford Day of Mourning
Martin Luther King Jr. Day 2007
President’s Day 2007
Good Friday 2007
Memorial Day 2007
Independence Day 2007
Labor Day 2007
1-Jan-07
2-Jan-07
15-Jan-07
19-Feb-07
6-Apr-07
28-May-07
4-Jul-07
3-Sep-07
Thanksgiving Day 2007
22-Nov-07
Christmas 2007
25-Dec-07
New Year’s Day 2008
Martin Luther King Jr. Day 2008
President’s Day 2008
1-Jan-08
21-Jan-08
18-Feb-08
Good Friday 2008
21-Mar-08
Memorial Day 2008
26-May-08
Independence Day 2008
Labor Day 2008
4-Jul-08
1-Sep-08
Thanksgiving Day 2008
27-Nov-08
Christmas 2008
25-Dec-08
New Year’s Day 2009
Martin Luther King Jr. Day 2009
President’s Day 2009
1-Jan-09
19-Jan-09
16-Feb-09
Good Friday 2009
10-Apr-09
Memorial Day 2009
25-May-09
Independence Day 2009
Labor Day 2009
3-Jul-09
7-Sep-09
Thanksgiving Day 2009
26-Nov-09
Christmas 2009
25-Dec-09
New Year’s Day 2010
Martin Luther King Jr. Day 2010
President’s Day 2010
Good Friday 2010
Memorial Day 2010
1-Jan-10
18-Jan-10
15-Feb-10
2-Apr-10
31-May-10
Independence Day 2010
5-Jul-10
Labor Day 2010
6-Sep-10
Thanksgiving Day 2010
25-Nov-10
Christmas 2010
24-Dec-10
Martin Luther King, Jr. Day 2011
17-Jan-11
Presidents’ Day 2011
21-Feb-11
Good Friday 2011
22-Apr-11
Memorial Day 2011
30-May-11
Independence Day 2011
Labor Day 2011
4-Jul-11
5-Sep-11
Thanksgiving Day 2011
24-Nov-11
Christmas 2011
26-Dec-11
New Year’s Day 2012
Martin Luther King, Jr. Day 2012
Presidents’ Day 2012
Good Friday 2012
Memorial Day 2012
Independence Day 2012
Labor Day 2012
2-Jan-12
16-Jan-12
20-Feb-12
6-Apr-12
28-May-12
4-Jul-12
3-Sep-12
Thanksgiving Day 2012
22-Nov-12
Christmas 2012
25-Dec-12
Type all entries, DO NOT cut & paste values
Dec 31, 2013
ASSETS
Cash and cash equivalents
Short-term investments
Accounts receivable, net
Accounts receivable allowance
Inventories, net
Current deferred taxes
Other current assets
Property, plant, and equipment
Less: accumulated depreciation
Long-term investments
Goodwill, net
Other intangibles, net
Other deferred taxes
Other assets
$
11,823
1,389
4,725
139
12,821
Dec 31, 2012 Use a formula format [ =n+n+n…] to add multiple
financial statement line items into a single cell
$
13,020
743 Include ‘Marketable securities’ if listed separately
5,024
124 If B/S does not have ‘Allowance’ amounts, see Notes
11,848
752
11,888
10,204
3,848 Use formula format to summarize multiple line items
10,769 If Net PPE on B/S, add Accum. Depr. from Notes
9,370 Enter Accumulated Depreciation as a positive number
5,248
100
5,021
107 Use formula format to summarize multiple line items
Use formula format to summarize multiple line items
743 Use formula format to summarize multiple line items
1,389
LIABILITIES
Accounts payable
Short-term debt
Current portion of long-term debt
Accrued liabilities
Income taxes payable
Other current liabilities
Long-term debt
Deferred income taxes payable
Other deferred liabilities
Other liabilities
$
3,115
$
5,285
548
5,556
553
3,162 Use formula format to summarize multiple line items
13,309
2,260
17,054 Use formula format to summarize multiple line items
1,288
7,305
1,816
8,576 Use formula format to summarize multiple line items
1,652 Use formula format to summarize multiple line items
Included in Total Liabilities and Stockholder Equity total
Minority interest
STOCKHOLDERS’ EQUITY
Preferred stock
Common stock, par value plus additional paid-in capital
Retained earnings (accumulated deficit)
Treasury stock
Accumulated other comprehensive income (loss)
Other stockholders’ equity
No enterable fields exist below this point
351,761
(345,351)
(249)
341,508 Include ‘Capital in excess of par value’ if listed separately
(336,443) Enter a negative amount to bracket the number
Enter Treasury Stock as a negative number
(758) Enter a negative amount to bracket the number
Use formula format to summarize multiple line items
Hydrogenics Corporation
(HYGS / NASDAQ)
Annual Consolidated Income Statement
Amounts Rounded to : Thousands
(except per share amounts)
Results for the Years Ending
Dec 31, 2013
Dec 31, 2012
Jan 0, 1900
No No
Net sales
Less: Cost of goods sold
Gross profit
–
–
–
Total operating expenses
–
–
–
Operating profit (loss)
–
–
–
–
–
–
–
–
–
–
–
–
Sales, general and administrative
Research and development (R&D)
Restructuring, impairment, and amortization
Purchased in-process R&D
Other operating expenses
Other income (expenses), net excluding interest expense
Earnings (loss) before interest and taxes
Interest expense
Earnings (loss) before taxes
Provision for (benefit from) income taxes
Earnings (loss) after taxes
Extraordinary items, net
Discontinued operations, net
Cumulative effect of changes in accounting principles, net
Other after-tax income (loss), net
Net profit (loss)
$
–
$
Basic earnings per common share
Enter following data only if Multiple Source information is available
–
$
–
No
Hydrogenics Corporation
(HYGS / NASDAQ)
Annual Consolidated Statement of Cash Flows
Amounts Rounded to : Thousands
Results for the Years Ending
Dec 31, 2013
Dec 31, 2012
Jan 0, 1900
Cash flows from operating activities:
Income (loss) from continuing operations
Adjustments to reconcile to net cash provided by operating activities:
Depreciation and amortization
Stock-based compensation
Restructuring and impairment charges
Increase (decrease) in provision for deferred income taxes
(Gain) loss on sales of investments, acquisitions, and securities
(Gain) loss on sales of property, plant, and equipment
Other non-cash items, net
Changes in assets and liabilities:
(Increase) decrease in receivables
(Increase) decrease in inventories
(Increase) decrease in other current assets
Increase (decrease) in accounts payable,
accrued liabilities, and income taxes payable
Increase (decrease) in deferred liabilities
Other assets and liabilities, net
$
Net cash provided by (used in ) operating activities
–
$
–
$
–
–
–
–
–
–
–
–
–
–
–
–
–
Cash flows from investing activities:
Purchases of property, plant, and equipment
Sales of property, plant, and equipment
Purchases of marketable securities and short-term investments
Sales of marketable securities and short-term investments
Acquisitions, net of cash acquired
Other investing activities, net
Net cash provided by (used in) investing activities
Cash flows from financing activities:
Short-term borrowings, net
Proceeds from long-term borrowings
Payment of long-term borrowings
Proceeds from sales of common stock
Repurchase of common stock / treasury stock
Dividends to shareholders
Other financing activities, net
Net cash provided by (used in) financing activities
Net cash provided by (used for) discontinued operations
Effect of exchange rate changes on cash, net
Net increase (decrease) in cash and equivalents for period
Cash and equivalents, beginning of period
Cash and equivalents, end of period
$
Supplemental disclosures of cash flow information:
Cash paid during the year for:
Interest
Income taxes (refunded)
No enterable fields exist below this point
–
$
–
$
–
Hydrogenics Corporation
(HYGS / NASDAQ)
Summary of Financial Statement Ratios
Liquidity Ratios:
Current ratio
Quick ratio
Cash flow liquidity
Average collection period
Days inventory held
Days payable outstanding
Cash conversion cycle
Activity Ratios:
Accounts receivable turnover
Inventory turnover
Payables turnover
Fixed asset turnover
Total asset turnover
Leverage Ratios:
Debt ratio
Long-term debt to total capitalization
Debt to equity
Financial leverage (FL)
Times interest earned
Cash interest coverage
Fixed charge coverage
Cash flow adequacy
Results for the Years Ending
Dec 31, 2012
1.40
0.83
0.59
times
times
times
1.32
0.87
0.53
times
times
times
0.00
0.00
0.00
0
0
0
0
0
0.00
0.00
0.00
0
0
0
0
0
0.00
0.00
0.00
0.00
0.00
0
0
0
0
0
0.00
0.00
0.00
0.00
0.00
0
0
0
0
0
84.92
26.84
5.50
6.48
0.00
0.00
1.00
0.00
Profitability Ratios:
Gross profit margin
Operating profit margin
Net profit margin
Cash flow margin
Return on assets (ROA)
or Return on investment (ROI)
Return on equity (ROE)
Cash return on assets
Market Ratios:
Earnings per share
Price-to-earnings
Dividend pa…
Purchase answer to see full
attachment