Business Finance Homework Help
WVU Financial Data for Kroger Company Consolidated Balance Worksheet
(you will use the10-k file forkroker co. from their website)
“In order for a Company to access the public market the SEC requires five years of audited financial statements.Item 6 of the annual report on Form 10-K also requires five years of selected financial data.The CQAD and most financial professionals will normally accumulate at least 5 years of financial statement data for purposes of observing trends and/or recoveries from unforeseen events and conditions.Because only two balance sheets are presented in a US GAAP set of financial statements, three 10-K’s will be required to obtain five years of balance sheet information.
BECAUSE THIS IS YOUR FIRST EFFORT AT THIS EXERCISE, YOU MAY SHORTEN YOUR REVIEW PERIOD TO THE MOST RECENT THREE FISCAL YEARS.Thus all of your income statement, cash flow and changes in equity information can be found in the 10-K that you downloaded in Phase 1.You will however have to download the10-K filed two years earlier in order to obtain the third and fourth years of balance sheet information.Four years of balance sheet data are needed as many of the ratios require the calculation of average balances (beginning + ending)/2.I have been known to add points to students who accumulate 6 years of data (I/S, CF, Equity and BS).
1) Prepare an Excel spread sheet containing a minimum of three years of Income Statement, and Statement of Cash Flows and a minimum of four years of Balance Sheets as presented in the 10-Ks.All three of the financial statement will be on the same tab within the spread sheet.That tab should be called “As Reported”.In future steps we will create a new tab containing reclassified items to conform to CQAD format, but for the time being we need a starting point and that starting point needs to be the financial statements as reported by the Company.DO NOT SUBMIT A SPREAD SHEET PREPARED BY ANYONE OTHER THAN YOU.(see my comments on page 3).
DO NOT INCLUDE PARENTHETICAL DATA IN THE LABEL COLUMN OF YOUR SPREAD SHEET.Rather create a new row for the data.For example it is common to report:
Accounts receivable (net of allowances of $100 and $90) $1,651$1,542
The label for Accounts receivable should just read: Accounts receivable – net
Under the balance sheet you should create a new row for the supplemental information as follows:
Allowances against receivables$100$90
By doing this the allowance data is now usable data rather than a narrative label.
2. To the right of the financial statement data, prepare common size financial statements (vertical analysis) using total revenues as the base for the income statement, operating cash flows as the base for the statement of cash flows and total assets as the base for the balance sheet.(Hint – use anchors and copy.Then go back and add borders)
3. To the right of the vertical analysis prepare horizontal analysis columns including year to year growth and a compounded annual growth rate (CAGR) for all relevant rows on your spreadsheet.CLEAR CELLS THAT RETURN ERROR MESSAGES AND OR RATES IN EXCESS OF 100%. (Hint – use anchors and copy.Then go back and add borders)
Step 1 is the most time consuming because it takes time to type and to find your keypunch errors.You will make keypunch errors.Find them so that I don’t have to.
Do not concern yourself with mirroring the cosmetics of the Company’s financial statements.This Excel spread sheet is a working tool.What is important is that the numbers on the spreadsheet are presented in the same order and with a reasonable representation of the line item label.
On the income statement, you can omit Basic Shares Outstanding and Basic EPS.We will focus on Diluted Weighted Average Shares Outstanding and Diluted EPS.If your income statement does not included the number of diluted shares used to compute EPS, find the number in the footnotes and include it on your spread sheet in a row in between Net Earnings and Diluted Earnings per Share.If your company declared a cash dividend and does not disclose the cash dividend per share on the income statement, compute and present on the income statement the cash dividend per share using the diluted weighted average shares outstanding.
On the statement of Cash Flows be sure to include supplemental cash flow information at the bottom of the financial statement including: Interest paid during the year and Income taxes paid during the year.
On the balance sheet, add lines to your spread sheet below the balance sheet to reflect parentheticals disclosures for allowance for doubtful accounts, shares issued, treasury shares and shares outstanding.Eventually, you will also need separate amounts for Land, Construction-in-progress, accumulated depreciation and deferred revenues (gift card liabilities) which are often found in the footnotes rather than on the face of the financial statements.
