finance help 7&8 assigments not too long

profileLt_brt
airtran_assignment_7_and_8v1.xlsx
Home>Business & Finance homework help>finance help 7&8 assigments not too long

Turn in Page HW 8

Name:
1) Using Model 1 forecast AirTran's EBIT
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
EBIT 148.09
2) Using Model 1 forecast AirTran's NOPAT
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
NOPAT 88.85
3) Assuming all new debt will be issued at the end of the year determine AirTran's Interest Expense. Interest rates are on the input sheet.
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Interest Expense 80.83
4) What is AirTran's forecasted EBT
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
EBT 59.84
5) What is AirTran's' Forecasted Earnings
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Earnings 38.54
6) Assuming a dividend payout ratio of 0.0% what is AirTran's addition to retained earnings and cumulative retained earnings
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
addition to retained earnings 30.11
cumulative retained earnings (54.81)
7) What are the external capital requirements
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
External Capital Requirements 1,703.84
8) What is the additional funds needed?
Dec-11 Dec-12 Dec-13 Dec-14
AFN
9) How much of the additional funds needed are raised by: (Hint: take the difference between the two years on the balance sheet)
Dec-11 Dec-12 Dec-13 Dec-14
Short - Term Debt
Long -Term Debt
Common Stock
AFN - 0 - 0 - 0 - 0
10) Use the IF function to determine whether the source of funding is a "source" or a "use". Let "1" represent source and "0" represent use.
Dec-11 Dec-12 Dec-13 Dec-14
Short - Term Debt
Long -Term Debt
Common Stock

Turn in Page HW 7

Name:
1) Forecast Sales using the average growth in sales from 2007 - 2010
Dec-07 Dec-08 Dec-09 Dec-10
Growth in Sales
Average Growth in Sales
2) Calculate the average Accrued Expense to Sales from 2006 - 2010
Average Accrued Expenses to Sales
3) List two Current Operating Liabilities from AirTran's Balance Sheet
1)
2)
4) Using Model 1 (Historical Averages) what is AirTran's Operating Current Assets
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Operating Current Assets 680.30
5) Using Model 1 (Historical Averages) what is AirTran's Total Assets
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Total Assets 2,179.35
6) Using Model 1 (Historical Averages) what is AirTran's Spontaneous Liabilities. Include Deferred Taxes and Other Current Liabilities
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Accounts Payable 57.79
Accrued Expenses 109.86
Other Current Liabilities 330.25
Deferred Taxes 32.41
Total Spontaneous Liabilities 530.32
7) What is AirTran's Total Net Operating Capital (Total Assets less Spontaneous Liabilities)
Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Total Net Operating Capital 1,649.03
8) List 3 Items that are financing issues.
9) Using Model 1 forecast the following items (The weights multiplied by Total Net Operating Capital)
Dec-11 Dec-12 Dec-13 Dec-14
Short-Term debt 10%
Long-Term Debt 65%
Common Stock 25%
Total Required Financing Needs from investors

Inputs

Fact Sheet Model 1
Historical -Avg
Sales Growth 0%
Treanor, Stephen: Use the question sheet to calculate
Cost of Goods Sold 84%
SG&A 10%
Depreciation and Amortization Expense 2.7%
Interest Rate 8%
Tax Rate 40%
Dividend Payout Ratio 0%
Current Operating Assets
Cash / Sales 18.79%
Receivables / Sales 1.59%
Inventory / Sales 1.62%
Other Current Assets / Sales 2.81%
Long-term operating Capital
Net Plant, Property & Equipment 53.77%
Other Investments 0.00%
Intangibles 1.09%
Deferred Charges 4.12%
Other Assets 3.10%
Spontaneous Liabilities (Current Operating Liabilities + Deferred Taxes)
Accounts Payable 2.38%
Accrued Expenses

Treanor, Stephen: Use the question sheet to calculate		 Deferred Taxes 0.61%
Other Current Liabilities 11.94%
Investement Tax Credit 0
Capital Struture -- Based on Total Net Operating Captial Target
Debt
Short-Term 10%
Long-Term Debt 65%
Shareholders Equity 25%

AirTran Balance Sheet

AIRTRAN HOLDINGS INC ANNUAL BALANCE SHEET
TICKER: AAI ($ MILLIONS)
SIC: 4512 NAME
GICS: 20302010
ASSETS Dec-06 Dec-07 Dec-08 Dec-08 Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Cash & Equivalents 335.015 347.610 421.141 596.672 503.124
Net Receivables 38.613 42.772 38.301 27.067 36.335
Inventories 17.236 47.148 31.432 50.471 46.185
Other Current Assets 26.093 42.407 93.533 83.213 94.657
Total Current Assets 416.957 479.937 584.407 757.423 680.301
Net Plant, Property & Equipment 1,015.229 1,365.912 1,282.972 1,320.039 1,294.020
Other Investments - 0 8.230 5.497 - 0 - 0
Intangibles 29.917 29.917 21.567 21.567 21.567
Deferred Charges 82.731 97.125 98.369 98.079 104.439
Other Assets 58.748 67.345 70.048 87.064 79.021
TOTAL ASSETS 1,603.582 2,048.466 2,062.860 2,284.172 2,179.348
LIABILITIES
Short Term debt 86.845 99.671 160.700 282.089 119.483
Accounts Payable 35.050 50.644 81.557 57.482 57.793
Accrued Expenses 93.335 105.447 104.036 101.602 109.860
Other Current Liabilities 182.857 252.372 365.554 285.366 330.252
Total Current Liabilities 398.087 508.134 711.847 726.539 617.388
Long Term Debt 826.212 1,062.548 1,097.558 1,051.484 990.188
Deferred Taxes - 0 31.434 7.450 4.206 32.414
Total Long-term Liabilities 826.212 1,093.982 1,105.008 1,055.690 1,022.602
Total Liabilities 1,224.299 1,602.116 1,816.855 1,782.229 1,639.990
EQUITY
Common stock 389.134 396.916 497.510 586.862 594.169
Retained Earnings (9.851) 49.434 (251.505) (84.919) (54.811)
Total Equity 379.283 446.350 246.005 501.943 539.358
TOTAL LIABILITIES & EQUITY 1,603.582 2,048.466 2,062.860 2,284.172 2,179.348

AirTran Income Satement

AIRTRAN HOLDINGS INC ANNUAL INCOME STATEMENT
TICKER: AAI $ Millions
SIC: 4512 NAME
GICS: 20302010
Dec-06 Dec-07 Dec-08 Dec-08 Dec-10 Dec-11 Dec-12 Dec-13 Dec-14
Sales 1,892.083 2,309.983 2,552.478 2,341.442 2,619.172
Cost of Goods Sold 1,642.16 1,884.51 2,335.34 1,868.78 2,157.10
Gross Profit 249.925 425.474 217.143 472.664 462.077
SG&A 178.99 239.06 245.37 241.75 254.03
EBITDA 70.939 186.411 (28.227) 230.917 208.049
Depreciation and Amortization Expense 30.08 48.49 58.62 56.87 59.96

Treanor, Stephen: Calculate using PP&E		 Operating Profit 40.861 137.926 (86.845) 174.046 148.091
Interest Expense 50.861 75.530 75.680 83.967 80.825
Non-Operating Expense (Income) (34.657) (35.606) 118.617 (40.982) (12.478) - 0 - 0 - 0 - 0
Special Items Expense (Income) - 0 10.65 10.75 (4.28) 19.90 7.40 7.40 7.40 7.40
Pretax Income 24.657 87.352 (291.892) 135.339 59.844
Total Income Taxes 9.94 34.67 (18.06) 0.68 21.30
Net Income 14.714 52.683 (273.829) 134.662 38.543
Dividends (24.565) 6.602 (27.110) 31.924 (8.435)
Addition to Retained Earnings (9.851) 59.285 (300.939) 166.586 30.108

Ratio Analysis

NAME
2007 2008 2008 2010 2011 2012 2013 2014
AirTran Price 7.16 4.44 5.22 7.39
2007 2008 2008 2010 1900 1900 1900 1900
Ratios Airline
Current Ratio Airtran 1.047 0.945 0.821 1.043 1.102 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 1.002 1.109 1.299 1.167
JBLU 0.889 0.890 1.326 1.253
PNCLQ 1.211 0.956 1.143 0.828
LUV 0.918 1.031 1.255 1.295
Days Sales Outstanding Airtran 7.449 6.758 5.477 4.219 5.064 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 14.367 11.766 12.003 11.439
JBLU 11.816 9.265 8.981 8.113
PNCLQ 14.420 26.479 32.516 14.518
LUV 10.327 6.921 5.960 5.880
Net Fixed Asset Turnover Airtran 1.864 1.691 1.990 1.774 2.024 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 1.184 1.143 1.073 1.229
JBLU 0.749 0.758 0.710 0.814
PNCLQ 3.083 1.200 1.157 1.092
LUV 0.907 0.998 0.973 1.144
Asset Turnover Airtran 1.180 1.128 1.237 1.025 1.202 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 0.781 0.749 0.682 0.764
JBLU 0.508 0.563 0.503 0.573
PNCLQ 1.111 0.763 0.656 0.681
LUV 0.588 0.770 0.725 0.783
Debt Ratio Airtran 76.35% 78.21% 88.07% 78.03% 75.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 77.20% 86.31% 82.51% 77.97%
JBLU 81.49% 79.06% 76.39% 74.91%
PNCLQ 91.84% 95.71% 92.07% 92.03%
LUV 58.62% 65.38% 61.69% 59.67%
Times Interest Earned Airtran 0.803 1.826 (1.148) 2.073 1.832 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 2.569 (1.275) 3.104 4.476
JBLU 0.720 0.409 1.439 1.956
PNCLQ 4.332 1.623 1.694 1.741
LUV 6.857 3.531 1.763 5.940
Operating Profit Margin Airtran 2.16% 5.97% -3.40% 7.43% 5.65% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 6.45% -3.60% 9.18% 12.65%
JBLU 5.70% 2.77% 8.66% 9.31%
PNCLQ 6.48% 6.84% 9.68% 7.24%
LUV 8.28% 4.16% 3.17% 8.20%
Profit Margin Airtran 0.78% 2.28% -10.73% 5.75% 1.47% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 3.57% -3.75% 3.58% 6.55%
JBLU 0.63% -2.24% 1.85% 2.57%
PNCLQ 4.40% -0.57% 4.95% 1.25%
LUV 6.54% 1.61% 0.96% 3.79%
Basic Earning Power Airtran 2.55% 6.73% -4.21% 7.62% 6.80% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 5.03% -2.70% 6.26% 9.66%
JBLU 2.89% 1.56% 4.35% 5.34%
PNCLQ 7.20% 5.22% 6.35% 4.93%
LUV 4.87% 3.21% 2.30% 6.42%
ROA Airtran 0.92% 2.57% -13.27% 5.90% 1.77% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 2.78% -2.81% 2.44% 5.01%
JBLU 0.32% -1.26% 0.93% 1.47%
PNCLQ 4.89% -0.43% 3.25% 0.85%
LUV 3.85% 1.24% 0.69% 2.97%
ROE Airtran 3.88% 11.80% -111.31% 26.83% 7.15% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
ALK 12.21% -20.53% 13.94% 22.72%
JBLU 1.74% -6.03% 3.95% 5.86%
PNCLQ 59.90% -10.10% 40.94% 10.69%
LUV 9.29% 3.59% 1.81% 7.36%
Debt Ratio Airtran 2007 2008 2008 2010 0.76347763943471547 0.78210524363108791 0.88074566378716945 0.78025166230914311 0.7525140546622201 0 0 0 Profit Margin Airtran 2007 2008 2008 2010 7.7766144508460717E-3 2.2806661347724272E-2 -0.10727967097071943 5.7512421832357996E-2 ALK 2007 2008 2008 2010 3.56531660011409E-2 -3.7538325553130955E-2 3.5766809812341899E-2 6.5522010281032272E-2 JBLU 2007 2008 2008 2010 6.3335679099225895E-3 -2.2432113341204249E-2 1.8529769137302553E-2 2.5668166181529506E-2 PNCLQ 2007 2008 2008 2010 4.3990530548379807E-2 -5.6742427308521777E-3 4.9503966845967161E-2 1.2510200662834906E-2 LUV 2007 2008 2008 2010 6.5409187709157282E-2 1.6148054068765308E-2 9.5652173913043474E-3 3.7921348314606744E-2 ROA Airtran 2007 2008 2008 2010 9.175707883974869E-3 2.5718269182891083E-2 -0.13274240617395269 5.8954404484425856E-2 ALK 2007 2008 2008 2010 2.7834064441425999E-2 -2.8104061543551988E-2 2.4393179538615847E-2 5.0053821313240036E-2 JBLU 2007 2008 2008 2010 3.2154340836012861E-3 -1.2618296529968454E-2 9.3143991449076199E-3 1.471257394205976E-2 PNCLQ 2007 2008 2008 2010 4.8881719701716654E-2 -4.3305133957332043E-3 3.2461106544027549E-2 8.5201608222055265E-3 LUV 2007 2008 2008 2010 3.8456952062962083E-2 1.2440592675426335E-2 6.9381175975891793E-3 2.9683761236500031E-2 Asset Turnover Airtran 2007 2008 2008 2010 1.1799103507023652 1.1276647989275879 1.2373491172449902 1.0250725426981855

ALK

ALASKA AIR GROUP INC
TICKER: ALK
SIC: 4512
GICS: 20302010
Selected Items from the Balance Sheet
Dec-07 Dec-08 Dec-09 Dec-10
Cash & Short Term Investment 934.7 1077.4 1192.1 1208.2
Receivables-Total 138 116.7 111.8 120.1
Inventories-Total 46.6 51.9 45.8 45.1
Current Assets-Total 1390.3 1508.9 1634.3 1662
PP&E-Total Net 2962.3 3167.7 3167.9 3117.1
Assets-Total 4490.9 4835.6 4985 5016.6
Notes Payable 0 0 0 0
Accounts Payable 101.5 59.6 63.3 60.2
Current Liabilities-Total 1388 1360.6 1258.3 1424.7
LT Debt-Total 1124.6 1596.3 1699.2 1313
Liabilities-Total 3466.9 4173.7 4112.9 3911.2
Common Equity-Total 1024 661.9 872.1 1105.4
Com Shares Outstanding 76.102 72.548 71.182 71.848
Selected Items from the Income Statement
Com Shares Outstanding 76.102 72.548 71.182 71.848
Sales-Net 3506 3620.3 3399.8 3832.3
Cost of Goods Sold 2711.5 3176.1 2522.9 2762.5
Gross Profit 794.5 444.2 876.9 1069.8
EBITDA 403.5 74.2 531.2 715.3
Depreciation-Amortization 177.4 204.6 219.2 230.5
EBIT 226.1 -130.4 312 484.8
Interest Expense 88 102.3 100.5 108.3
EBT 201.6 -213.2 202.9 405.9
Net Income (Loss) 125 -135.9 121.6 251.1
Price/Earnings-Monthly 8.068 @NM 10.195 8.087
Price Close - Monthly 12.505 14.625 17.28 28.345

JBLU

JETBLUE AIRWAYS CORP
TICKER: JBLU
SIC: 4512
GICS: 20302010
Selected Items from the Balance Sheet
Dec-07 Dec-08 Dec-09 Dec-10
Cash & Short Term Investment 834 649 1149 963
Receivables-Total 92 86 81 84
Inventories-Total 26 30 40 49
Current Assets-Total 1116 962 1534 1360
PP&E-Total Net 3794 4470 4638 4641
Assets-Total 5598 6023 6549 6593
Notes Payable 43 120 0 0
Accounts Payable 140 144 93 104
Current Liabilities-Total 1256 1081 1157 1085
LT Debt-Total 2588 2883 2920 2850
Liabilities-Total 4562 4762 5003 4939
Common Equity-Total 1036 1261 1546 1654
Com Shares Outstanding 181.593 271.755 291.491 294.687
Selected Items from the Income Statement
Com Shares Outstanding 181.593 271.755 291.491 294.687
Sales-Net 2842 3388 3292 3779
Cost of Goods Sold 1990 2496 2229 2545
Gross Profit 852 892 1063 1234
EBITDA 335 296 493 559
Depreciation-Amortization 173 202 208 207
EBIT 162 94 285 352
Interest Expense 225 230 198 180
EBT 41 -76 104 161
Net Income (Loss) 18 -76 61 97
Price/Earnings-Monthly 59 @NM 22.708 18.361
Price Close - Monthly 5.9 7.1 5.45 6.61
0.0991227635 -0.2796636676 0.2092688968 0.3291628066
-25.3876381579 26.0430483607 20.0812481443

PNCLQ

PINNACLE AIRLINES CORP
TICKER: PNCLQ
SIC: 4512
GICS: 20302010
Selected Items from the Balance Sheet
Dec-07 Dec-08 Dec-09 Dec-10
Cash & Short Term Investment 218.962 74.886 94.689 108.303
Receivables-Total 31.107 62.736 75.321 40.602
Inventories-Total 16.03 17.106 19.472 34.195
Current Assets-Total 294.919 177.226 203.396 203.934
PP&E-Total Net 255.41 720.757 731.073 935.138
Assets-Total 708.588 1133.122 1289.42 1498.798
Notes Payable 8.375 12.35 2.027 19.337
Accounts Payable 33.062 29.962 24.306 44.389
Current Liabilities-Total 243.459 185.415 177.987 246.34
LT Debt-Total 197.296 704.342 526.87 667.875
Liabilities-Total 650.766 1084.529 1187.183 1379.305
Common Equity-Total 57.822 48.593 102.237 119.493
Com Shares Outstanding 17.953 18.065 18.337 18.653
Selected Items from the Income Statement
Com Shares Outstanding 17.953 18.065 18.337 18.653
Sales-Net 787.374 864.785 845.508 1020.767
Cost of Goods Sold 725.866 777.909 727.052 907.728
Gross Profit 61.508 86.876 118.456 113.039
EBITDA 61.508 86.876 118.456 113.039
Depreciation-Amortization 10.468 27.721 36.602 39.147
EBIT 51.04 59.155 81.854 73.892
Interest Expense 11.783 36.451 48.324 42.433
EBT 51.037 1.297 42.238 20.883
Net Income (Loss) 34.637 -4.907 41.856 12.77
Price/Earnings-Monthly 9.187 @NM 2.953 11.286
Price Close - Monthly 15.25 1.7 6.88 7.9

LUV

SOUTHWEST AIRLINES
TICKER: LUV
SIC: 4512
GICS: 20302010
Selected Items from the Balance Sheet
Dec-07 Dec-08 Dec-09 Dec-10
Cash & Short Term Investment 2779 1803 2593 3538
Receivables-Total 279 209 169 195
Inventories-Total 259 203 221 243
Current Assets-Total 4443 2893 3358 4279
PP&E-Total Net 10874 11040 10634 10578
Assets-Total 16772 14308 14269 15463
Notes Payable 0 0 75 0
Accounts Payable 759 668 746 739
Current Liabilities-Total 4838 2806 2676 3305
LT Debt-Total 2050 3498 3325 2875
Liabilities-Total 9831 9355 8803 9226
Common Equity-Total 6941 4953 5466 6237
Com Shares Outstanding 734.798 739.993 742.791 747.435
Selected Items from the Income Statement
Com Shares Outstanding 734.798 739.993 742.791 747.435
Sales-Net 9861 11023 10350 12104
Cost of Goods Sold 7056 8590 8069 9062
Gross Profit 2805 2433 2281 3042
EBITDA 1371 1058 944 1620
Depreciation-Amortization 555 599 616 628
EBIT 816 459 328 992
Interest Expense 119 130 186 167
EBT 1058 278 164 745
Net Income (Loss) 645 178 99 459
Price/Earnings-Monthly 14.353 35.917 87.923 20.935
Price Close - Monthly 12.2 8.62 11.43 12.98
0.877792264 0.2405428159 0.1332810979 0.6141002228
35.8356160674 85.7585972727 21.1366150327

AirTran Cash Flow Act.

AIRTRAN HOLDINGS INC ANNUAL STATEMENT OF CASH FLOWS
TICKER: AAI
SIC: 4512
GICS: 20302010
10-Dec 9-Dec 8-Dec 7-Dec 6-Dec
INDIRECT OPERATING ACTIVITIES
Income Before Extraordinary Items 38.543 134.662 -273.829 52.683 14.714
Depreciation and Amortization 67.889 68.363 64.981 50.842 31.7
Extraordinary Items and Disc. Operations 0 0 0 0 0
Deferred Taxes 21.301 0.677 -18.063 34.464 9.943
Sale of Property, Plant, and Equipment
and Sale of Investments - Loss (Gain) 1.061 -2.914 -22.356 -4.811 4.387
Funds from Operations - Other 5.423 1.11 17.959 13.721 1.788
Receivables - Decrease (Increase) -9.921 8.711 5.157 -5.566 -10.437
Inventory - Decrease (Increase) 3.382 -19.797 15.031 -30.445 -3.083
Accounts Payable and Accrued Liabs - Inc (Dec)
Income Taxes - Accrued - Increase (Decrease) 0 0 0 0 0
Other Assets and Liabilities - Net Change 33.082 -72.579 31.219 71.191 26.114
Operating Activities - Net Cash Flow 160.76 118.233 -179.901 182.079 75.126
INVESTING ACTIVITIES
Investments - Increase 0 0 0 0 0
Sale of Investments 0 0 0 0 0
Short-Term Investments - Change 1.696 27.116 88.683 30.602 -151.025
Capital Expenditures -25.933 -76.565 -80.642 -175.942 -105.478
Sale of Property, Plant, and Equipment 305.965 72.879 5
Acquisitions 0 0 0 0 0
Investing Activities - Other 0 2.225 14.65 -10.348 0
Investing Activities - Net Cash Flow -24.237 -47.224 328.656 -82.809 -251.503
FINANCING ACTIVITIES
Sale of Common and Preferred Stock 1.525 56.513 78.571 2.378 12.003
Purchase of Common and Preferred Stock 0 0 0
Cash Dividends 0 0 0 0 0
Long-Term Debt - Issuance 51.837 160.916 107.577 69.034 71.045
Long-Term Debt - Reduction -202.894 -95.774 -317.153 -115.904 -113.132
Current Debt - Changes -75 35 90
Excess Tax Benefit from Stock Options 0 0 0 0 0
Financing Activities - Other -0.645 -0.123 0.455 -7.005 -5.011
Financing Activities - Net Cash Flow -225.177 156.532 -40.55 -51.497 -35.095
Exchange Rate Effect 0 0 0 0 0
Cash and Equivalents - Change -88.654 227.541 108.205 47.773 -211.472
DIRECT OPERATING ACTIVITIES
Interest Paid - Net 79.479 77.238 75.473 64.397 17.41
Income Taxes Paid -0.332 -0.198 0.332 0 0
SUPPLEMENTAL STATEMENT OF FINANCIAL POSITION