Financial management assignment (MBA)

profilejena.a
sample_analysis.xls
Home>Business & Finance homework help>Financial management assignment (MBA)

Balance Sheet

Kellogg (NYSE) - K
BALANCE SHEET (US$ in millions, except per-share data)
12/31/12 12/31/11 12/31/10 12/31/09
$ Amount % $ Amount % $ Amount % $ Amount %
Assets Assets Assets Assets
Assets
Cash $ 281.00 1.9% $ 460.00 3.9% $ 444.00 3.7% $ 334.00 3.0%
Accounts Receivables $ 1,454.00 9.6% $ 1,188.00 9.9% $ 1,190.00 10.0% $ 1,093.00 9.8%
Inventory $ 1,365.00 9.0% $ 1,174.00 9.8% $ 1,056.00 8.9% $ 910.00 8.1%
Other $ 280.00 1.8% $ 247.00 2.1% $ 225.00 1.9% $ 221.00 2.0%
Total Current Assets $ 3,380.00 22.3% $ 3,069.00 25.7% $ 2,915.00 24.6% $ 2,558.00 22.8%
Net Plant/Property/Equipment $ 3,782.00 24.9% $ 3,281.00 27.5% $ 3,128.00 26.4% $ 3,010.00 26.9%
Goodwill $ 7,412.00 48.8% $ 5,077.00 42.5% $ 5,084.00 42.9% $ 5,101.00 45.5%
Other Fixed Assets $ 610.00 4.0% $ 516.00 4.3% $ 720.00 6.1% $ 531.00 4.7%
Total Non-Current Assets $ 11,804.00 77.7% $ 8,874.00 74.3% $ 8,932.00 75.4% $ 8,642.00 77.2%
Total Assets $ 15,184.00 100.0% $ 11,943.00 100.0% $ 11,847.00 100.0% $ 11,200.00 100.0%
Liabilities and Equity
Payables $ 1,448.00 9.5% $ 1,255.00 10.5% $ 1,269.00 10.7% $ 1,143.00 10.2%
Accrued Expenses,Current $ 783.00 5.2% $ 652.00 5.5% $ 558.00 4.7% $ 731.00 6.5%
Current Debt $ 1,820.00 12.0% $ 995.00 8.3% $ 996.00 8.4% $ 45.00 0.4%
Other Current Liabilities $ 472.00 3.1% $ 411.00 3.4% $ 361.00 3.0% $ 369.00 3.3%
Total Current Liabilities $ 4,523.00 29.8% $ 3,313.00 27.7% $ 3,184.00 26.9% $ 2,288.00 20.4%
Long-term Debt $ 6,082.00 40.1% $ 5,037.00 42.2% $ 4,908.00 41.4% $ 4,835.00 43.2%
Deferred Liabilities $ 523.00 3.4% $ 643.00 5.4% $ 697.00 5.9% $ 425.00 3.8%
Other $ 1,637.00 10.8% $ 1,154.00 9.7% $ 900.00 7.6% $ 1,380.00 12.3%
Total Non-Current Liabilities $ 8,242.00 54.3% $ 6,834.00 57.2% $ 6,505.00 54.9% $ 6,640.00 59.3%
Total Liabilities $ 12,765.00 84.1% $ 10,147.00 85.0% $ 9,689.00 81.8% $ 8,928.00 79.7%
Total Equity $ 2,419.00 15.9% $ 1,796.00 15.0% $ 2,158.00 18.2% $ 2,272.00 20.3%
Total liabilities and equity $ 15,184.00 100.0% $ 11,943.00 100.0% $ 11,847.00 100.0% $ 11,200.00 100.0%
Per Share Data
Common shares outstanding 1 361.27 357.3 365.6 381.38
Book Value per share 2 $ 6.70 $ 5.03 $ 5.90 $ 5.96
Market Value per share $ 55.33 $ 50.57 $ 51.08 $ 54.00
NOTES
1 millions of shares
2 = Equity/shares

Income Statement

Kellogg (NYSE) - K
INCOME STATEMENT (US$ in millions, except per-share data)
12/31/12 12/31/11 12/31/10 12/31/09
$ Amount % $ Amount % $ Amount % $ Amount %
Sales Sales Sales Sales
Total Revenue $ 14,197.00 100.0% $ 13,198.00 100.0% $ 12,397.00 100.0% $ 12,575.00 100.0%
Cost of Revenue $ 8,763.00 61.7% $ 8,046.00 61.0% $ 7,055.00 56.9% $ 7,184.00 57.1%
Operating Expenses $ 3,872.00 27.3% $ 3,725.00 28.2% $ 3,305.00 26.7% $ 3,390.00 27.0%
EBIT $ 1,562.00 11.0% $ 1,427.00 10.8% $ 2,037.00 16.4% $ 2,001.00 15.9%
Net Interest Income/Expense $ 237.00 1.7% $ 245.00 1.9% $ 254.00 2.0% $ 321.00 2.6%
Income Tax $ 363.00 2.6% $ 320.00 2.4% $ 510.00 4.1% $ 476.00 3.8%
Net Income $ 962.00 6.8% $ 862.00 6.5% $ 1,273.00 10.3% $ 1,204.00 9.6%
DPS (dividend per share) 1.74 1.67 1.56 1.43
EPS (earnings per share) 2.68 2.39 3.43 3.17

Ratios

SUMMARY OF FINANCIAL RATIOS
Kellogg (NYSE) - K 2012 2011 2010 2009
12/31/12 12/31/11 12/31/10 12/31/09
Ratios
Liquidity
Current 0.75 0.93 0.92 1.12 --> CA / CL
Quick 0.45 0.57 0.58 0.72 --> (CA - Inventory) / CL
Asset management
Inventory Turnover 6.42 6.85 6.68 7.89 --> Sales / Inventory
Days sales in inventory 56.86 53.26 54.63 46.23 --> 365 / Inv. Turnover ratio
Fixed assets turnover 1.20 1.49 1.39 1.46 --> Sales / Fixed Assets
Total assets turnover 0.93 1.11 1.05 1.12 --> Sales / TA
Leverage (Debt Management)
Debt to total assets 0.84 0.85 0.82 0.80 --> Tot. Debt / TA
Debt to equity 5.28 5.65 4.49 3.93 --> Tot. Debt / Equity
Equity multiplier 6.28 6.65 5.49 4.93 --> TA / Equity
Times interest earned 6.59 5.82 8.02 6.23 --> EBIT / Interest
Profitability
Net Profit Margin 6.78% 6.53% 10.27% 9.57% --> NI / Sales
ROA 6.34% 7.22% 10.75% 10.75% --> NI / TA
ROE 39.77% 48.00% 58.99% 52.99% --> NI / Equity
Market Value
Earnings per Share 2.68 2.39 3.43 3.17 --> NI / shares outstanding
Price/earnings 20.65 21.16 14.89 17.03 --> Market price / EPS
Market/book 8.26 10.06 8.65 9.06 --> Market price / Book value per share
Dividends
Dividends per share 1.74 1.67 1.56 1.43
Dividend payout ratio 0.39 0.41 0.31 0.31
Retention ratio 0.61 0.59 0.69 0.69

Raw Statement Data

Here are the screenshots for Kellogg's statements:
Please do the same for balance sheets and income statements for each company
(It might take to make +/- 12 screenshots total)
Balance Sheet, Part 1 Balance Sheet, Part 2
Income statement:

Raw price data

Links

Here's where financial statements data can be obtained (link below will bring you to financial statements for Kellogg):
Please, make screen shots of the annual balance sheet and income statement data for each company and paste those in "Raw Statements Data" tab
Balance sheets: http://investing.money.msn.com/investments/stock-balance-sheet/?symbol=US%3AK&stmtView=Ann
Income Statements: http://investing.money.msn.com/investments/stock-income-statement/?symbol=US%3aK
Here's where daily stock prices can be obtained:
Please, collect and summarize all price data on the Raw price data tab
You would need to get daily prices for entire year 2012 (use Adjusted Price column)
If there is any dividend paid - add it to adjusted closing price for the same day.
(Suggestion: instead of copy-pasting data from a brouser - scroll page down, at the bottom of the table you should see hyperlink "Download to Spreadsheet"
this might me a much faster way to get everything into Excel and then do all necessary calculations)
http://finance.yahoo.com/q/hp?s=K&a=00&b=01&c=2012&d=11&e=31&f=2012&g=d
($964.54)
($822.70)

SP500 & Tbills

Date Adj Close, SP500 T-bill rates
12/31/12 1426.19 0.15%
12/28/12 1402.43 0.14%
12/27/12 1418.1 0.14%
12/26/12 1419.83 0.15%
12/24/12 1426.66 0.15%
12/21/12 1430.15 0.15%
12/20/12 1443.69 0.15%
12/19/12 1435.81 0.15%
12/18/12 1446.79 0.16%
12/17/12 1430.36 0.13%
12/14/12 1413.58 0.13%
12/13/12 1419.45 0.14%
12/12/12 1428.48 0.14%
12/11/12 1427.84 0.16%
12/10/12 1418.55 0.17%
12/7/12 1418.07 0.17%
12/6/12 1413.94 0.17%
12/5/12 1409.28 0.17%
12/4/12 1407.05 0.17%
12/3/12 1409.46 0.17%
11/30/12 1416.18 0.17%
11/29/12 1415.95 0.17%
11/28/12 1409.93 0.17%
11/27/12 1398.94 0.17%
11/26/12 1406.29 0.16%
11/23/12 1409.15 0.18%
11/21/12 1391.03 0.17%
11/20/12 1387.81 0.16%
11/19/12 1386.89 0.16%
11/16/12 1359.88 0.16%
11/15/12 1353.33 0.17%
11/14/12 1355.49 0.18%
11/13/12 1374.53 0.17%
11/12/12 1380.03 0.17%
11/9/12 1379.85 0.19%
11/8/12 1377.51 0.17%
11/7/12 1394.53 0.18%
11/6/12 1428.39 0.18%
11/5/12 1417.26 0.18%
11/2/12 1414.2 0.17%
11/1/12 1427.59 0.17%
10/31/12 1412.16 0.17%
10/26/12 1411.94 0.18%
10/25/12 1412.97 0.18%
10/24/12 1408.75 0.18%
10/23/12 1413.11 0.18%
10/22/12 1433.82 0.19%
10/19/12 1433.19 0.18%
10/18/12 1457.34 0.18%
10/17/12 1460.91 0.18%
10/16/12 1454.92 0.18%
10/15/12 1440.13 0.18%
10/12/12 1428.59 0.17%
10/11/12 1432.84 0.17%
10/10/12 1432.56 0.17%
10/9/12 1441.48 0.17%
10/8/12 1455.88 0.17%
10/5/12 1460.93 0.17%
10/4/12 1461.4 0.17%
10/3/12 1450.99 0.15%
10/2/12 1445.75 0.16%
10/1/12 1444.49 0.16%
9/28/12 1440.67 0.17%
9/27/12 1447.15 0.16%
9/26/12 1433.32 0.17%
9/25/12 1441.59 0.18%
9/24/12 1456.89 0.18%
9/21/12 1460.15 0.18%
9/20/12 1460.26 0.18%
9/19/12 1461.05 0.18%
9/18/12 1459.32 0.18%
9/17/12 1461.19 0.17%
9/14/12 1465.77 0.17%
9/13/12 1459.99 0.16%
9/12/12 1436.56 0.17%
9/11/12 1433.56 0.17%
9/10/12 1429.08 0.17%
9/7/12 1437.92 0.17%
9/6/12 1432.12 0.17%
9/5/12 1403.44 0.16%
9/4/12 1404.94 0.16%
8/31/12 1406.58 0.16%
8/30/12 1399.48 0.17%
8/29/12 1410.49 0.18%
8/28/12 1409.3 0.18%
8/27/12 1410.44 0.18%
8/24/12 1411.13 0.19%
8/23/12 1402.08 0.19%
8/22/12 1413.49 0.19%
8/21/12 1413.17 0.20%
8/20/12 1418.13 0.18%
8/17/12 1418.16 0.19%
8/16/12 1415.51 0.19%
8/15/12 1405.53 0.18%
8/14/12 1403.93 0.18%
8/13/12 1404.11 0.18%
8/10/12 1405.87 0.17%
8/9/12 1402.8 0.19%
8/8/12 1402.22 0.18%
8/7/12 1401.35 0.18%
8/6/12 1394.23 0.16%
8/3/12 1390.99 0.16%
8/2/12 1365 0.17%
8/1/12 1375.32 0.17%
7/31/12 1379.32 0.16%
7/30/12 1385.3 0.18%
7/27/12 1385.97 0.17%
7/26/12 1360.02 0.18%
7/25/12 1337.89 0.17%
7/24/12 1338.31 0.18%
7/23/12 1350.52 0.16%
7/20/12 1362.66 0.16%
7/19/12 1376.51 0.16%
7/18/12 1372.78 0.17%
7/17/12 1363.67 0.17%
7/16/12 1353.64 0.17%
7/13/12 1356.78 0.19%
7/12/12 1334.76 0.19%
7/11/12 1341.45 0.19%
7/10/12 1341.47 0.19%
7/9/12 1352.46 0.19%
7/6/12 1354.68 0.20%
7/5/12 1367.58 0.19%
7/3/12 1374.02 0.21%
7/2/12 1365.51 0.21%
6/29/12 1362.16 0.21%
6/28/12 1329.04 0.22%
6/27/12 1331.85 0.21%
6/26/12 1319.99 0.21%
6/25/12 1313.72 0.18%
6/22/12 1335.02 0.18%
6/21/12 1325.51 0.18%
6/20/12 1355.69 0.19%
6/19/12 1357.98 0.17%
6/18/12 1344.78 0.17%
6/15/12 1342.84 0.17%
6/14/12 1329.1 0.17%
6/13/12 1314.88 0.17%
6/12/12 1324.18 0.18%
6/11/12 1308.93 0.17%
6/8/12 1325.66 0.18%
6/7/12 1314.99 0.18%
6/6/12 1315.13 0.18%
6/5/12 1285.5 0.18%
6/4/12 1278.18 0.18%
6/1/12 1278.04 0.17%
5/31/12 1310.33 0.18%
5/30/12 1313.32 0.19%
5/29/12 1332.42 0.19%
5/25/12 1317.82 0.19%
5/24/12 1320.68 0.20%
5/23/12 1318.86 0.19%
5/22/12 1316.63 0.20%
5/21/12 1315.99 0.20%
5/18/12 1295.22 0.19%
5/17/12 1304.86 0.19%
5/16/12 1324.8 0.19%
5/15/12 1330.66 0.18%
5/14/12 1338.35 0.18%
5/11/12 1353.39 0.18%
5/10/12 1357.99 0.18%
5/9/12 1354.58 0.18%
5/8/12 1363.72 0.18%
5/7/12 1369.58 0.18%
5/4/12 1369.1 0.18%
5/3/12 1391.57 0.19%
5/2/12 1402.31 0.18%
5/1/12 1405.82 0.19%
4/30/12 1397.91 0.19%
4/27/12 1403.36 0.18%
4/26/12 1399.98 0.17%
4/25/12 1390.69 0.17%
4/24/12 1371.97 0.17%
4/23/12 1366.94 0.16%
4/20/12 1378.53 0.17%
4/19/12 1376.92 0.16%
4/18/12 1385.14 0.17%
4/17/12 1390.78 0.17%
4/16/12 1369.57 0.17%
4/13/12 1370.26 0.17%
4/12/12 1387.57 0.18%
4/11/12 1368.71 0.18%
4/10/12 1358.59 0.19%
4/9/12 1382.2 0.19%
4/5/12 1398.08 0.19%
4/4/12 1398.96 0.19%
4/3/12 1413.38 0.20%
4/2/12 1419.04 0.17%
3/30/12 1408.47 0.18%
3/29/12 1403.28 0.17%
3/28/12 1405.54 0.17%
3/27/12 1412.52 0.17%
3/26/12 1416.51 0.18%
3/23/12 1397.11 0.18%
3/22/12 1392.78 0.18%
3/21/12 1402.89 0.20%
3/20/12 1405.52 0.21%
3/19/12 1409.75 0.20%
3/16/12 1404.17 0.20%
3/15/12 1402.6 0.20%
3/14/12 1394.28 0.20%
3/13/12 1395.95 0.19%
3/12/12 1371.09 0.18%
3/9/12 1370.87 0.18%
3/8/12 1365.91 0.18%
3/7/12 1352.63 0.18%
3/6/12 1343.36 0.17%
3/5/12 1364.33 0.16%
3/2/12 1369.63 0.16%
3/1/12 1374.09 0.17%
2/29/12 1365.68 0.17%
2/28/12 1372.18 0.17%
2/27/12 1367.59 0.16%
2/24/12 1365.74 0.17%
2/23/12 1363.46 0.16%
2/22/12 1357.66 0.16%
2/21/12 1362.21 0.16%
2/17/12 1361.23 0.17%
2/16/12 1358.04 0.16%
2/15/12 1343.23 0.17%
2/14/12 1350.5 0.17%
2/13/12 1351.77 0.15%
2/10/12 1342.64 0.15%
2/9/12 1351.95 0.15%
2/8/12 1349.96 0.15%
2/7/12 1347.05 0.14%
2/6/12 1344.33 0.13%
2/3/12 1344.9 0.13%
2/2/12 1325.54 0.13%
2/1/12 1324.09 0.12%
1/31/12 1312.41 0.12%
1/30/12 1313.01 0.11%
1/27/12 1316.33 0.11%
1/26/12 1318.43 0.11%
1/25/12 1326.06 0.11%
1/24/12 1314.65 0.11%
1/23/12 1316 0.11%
1/20/12 1315.38 0.11%
1/19/12 1314.5 0.11%
1/18/12 1308.04 0.11%
1/17/12 1293.67 0.11%
1/13/12 1289.09 0.10%
1/12/12 1295.5 0.11%
1/11/12 1292.48 0.11%
1/10/12 1292.08 0.11%
1/9/12 1280.7 0.10%
1/6/12 1277.81 0.11%
1/5/12 1281.06 0.10%
1/4/12 1277.3 0.11%
1/3/12 1277.06 0.11%