Pro forma financial statements and credit analysis, using forecast assumptions.

FINC 4335 Online Summer 2016 Homework Assignment 3 1. Using the data provided (an Excel spreadsheet with the data is available on Blackboard), along with the following assumptions, prepare a forecasted income statement, balance sheet, and cash flow statement for the company for 2016. Be certain to incorporate the impact that the additional debt and resulting increase in interest expense would have on your pro forma financial statements (i.e., include the results of the first feedback loop). In addition, be certain that the income statement, balance sheet, and cash flow statement data fully articulate (e.g., the change in cash on the forecasted cash flow statement equals the change in cash shown on the balance sheet and vice versa). Forecast Assumptions Sales are expected to grow by 8%. The cost of goods sold and selling, general, and administrative expenses are expected to be at the same percentage of sales as they were in 2015. Depreciation expense is expected to be at the same percentage of the end-of-year amount of gross property, plant, and equipment as it was in 2015. Interest expense is expected to remain constant except for increases associated with the additional debt financing needed to support the growth (i.e., the feedback loop). The tax rate is expected to remain at the same level as in 2015. All assets with the exception of property, plant, and equipment are expected to remain at the same percentage of sales as they were in 2015. New capital expenditures (investments in property, plant, and equipment) will equal the same percentage of sales as occurred in 2015. During 2015 the company made new capital investments of $9,247 million. All operating liabilities are expected to be at the same percentage of sales as they were in 2015. During 2016 the company will repay the current portion of long-term debt that was due at the beginning of the year. An additional $4,250 million of long-term debt will shift to current maturities of long-term debt during the year (i.e., this amount is expected to be repaid in 2017.) The company will not issue any common stock nor repurchase any treasury stock during the year (i.e., common shares outstanding will remain at 946 million). The current market price for the common stock is $67.89 and the price is expected to remain stable over the next year. The company will maintain the same dividend payout ratio as in 2015. In 2015 the company paid out dividends of $8,119. Any excess cash generated during the year will be invested in new short-term investments earning 4% p.a. and any cash shortages will be financed using new short-term debt (i.e., notes payable) costing 10% p.a. 2. Calculate the current ratio, quick ratio, debt ratio, interest coverage ratio, and Altman Z-score based on the historical 2015 data and the 2016 forecasted data. Comment on whether these expected changes in ratios would be seen as favorable or unfavorable to current and potential creditors of the company, and why. Please submit your Excel spreadsheet via the assignment submission feature in Blackboard. ($ millions) Sales Less: Cost of goods sold Gross profit Less: Selling, general, and administrative expenses Depreciation and amortization Operating profit Less: Interest expense Earnings before income taxes Income taxes Earnings after income taxes 2015 $90,478 46,924 43,554 19,980 5,821 17,753 2,678 15,075 5,111 $9,964 ($ millions) Cash & equivalents Accounts receivable, net Inventories Other current assets Total current assets Plant, property & equipment, gross Less: Accumulated depreciation Plant, property & equipment, net Long-term investments Goodwill Other noncurrent assets Total assets 2015 $1,498 8,128 15,842 8,032 33,500 73,502 39,845 33,657 3,785 4,850 12,016 $87,808 Short-term debt Current portion of long-term debt Accounts payable Other accrued expenses Total current liabilities Long-term debt Deferred taxes Other long-term liabilities Total liabilities Common stock & paid-in capital Retained earnings Treasury stock Total equity Total liabilities and equity 1,665 4,032 5,738 9,215 20,650 22,755 3,882 8,486 55,773 9,848 28,486 (6,299) 32,035 $87,808 FINC 4335 Summer 2016 Homework Assignment 3 Review Guide 1. Using the data for Phillips 66 provided, along with the assumptions given, prepare a forecasted income statement, balance sheet, and cash flow statement for the company for 2015. Be certain to incorporate the impact that the additional debt and resulting increase in interest expense would have on your pro forma financial statements (i.e., include the results of the first feedback loop). In addition, be certain that the income statement, balance sheet, and cash flow statement data fully articulate (e.g., the change in cash on the forecasted cash flow statement equals the change in cash shown on the balance sheet and vice versa). Forecast Assumptions Sales are expected to grow by 4%. The cost of goods sold and selling, general, and administrative expenses are expected to be at the same percentage of sales as they were in 2014. Depreciation expense is expected to be at the same percentage of the end-of-year amount of gross property, plant, and equipment as it was in 2014. Interest expense is expected to remain constant except for increases associated with the additional debt financing needed to support the growth (i.e., the feedback loop). The tax rate is expected to remain at the same level as in 2014. All assets with the exception of property, plant, and equipment are expected to remain at the same percentage of sales as they were in 2014. New capital expenditures (new investments in property, plant, and equipment) will equal the same percentage of sales as occurred in 2014. During 2014 the company made new capital investments of $238 million. In addition, because the company is nearing full capacity, an additional investment of $3,000 million must be made to support the higher level of sales. All operating liabilities are expected to be at the same percentage of sales as they were in 2014. During 2015 the company will repay the current portion of long-term debt that was due at the beginning of the year. Furthermore, $850 million of long-term debt will be reclassified as short-term debt during the year (i.e., this is the current portion to be repaid in 2016.) The company will not issue any common stock nor repurchase any treasury stock during the year (i.e., common shares outstanding will remain at 546 million). The company will maintain the same dividend payout ratio as in 2014. Any excess cash generated during the year will be invested in new short-term investments earning 4% p.a. and any cash shortages will be financed using new short-term debt costing 8% p.a. 2. Calculate the current ratio, quick ratio, debt ratio [using both total debt and total liabilities in the numerator], and interest coverage ratio based on your 2015 forecasted data. Compare these projected ratios to those based on the 2014 financial statement amounts. qattachments_a38c3770a618fb79e18682b8b211e482fe320a41.xlsx Phillips 66 Forecasted Income Statement ($ millions) Assumptions 2014 $149,204 $149,204.0 1.04 Sales 140,525 $155,172.2 94.2% Less: Cost of goods sold 8,679 Gross profit 1,663 $155,172.2 1.1% Less: Selling, general, and administrative expenses 1,019 $32,471.5 3.8% Depreciation and amortization 5,997 Operating profit 287 Less: Interest expense 5,710 Earnings before income taxes 1,654 $5,790.6 29.0% Income taxes $4,056 Earnings after income taxes 2015 est. $155,172.2 146,146.0 9,026.2 1,729.5 1,219.0 6,077.6 287.0 5,790.6 1,677.4 $4,113.3 Feedback 2015 est. $155,172.2 146,146.0 9,026.2 1,729.5 1,219.0 6,077.6 482.9 5,594.7 1,620.6 $3,974.1 Phillips 66 Forecasted Balance Sheet 2014 Assumptions $5,208 $155,172.2 3.5% 0 $155,172.2 0.0% 0 plug 4.0% 7,254 $155,172.2 4.9% 3,398 $155,172.2 2.3% 837 $155,172.2 0.6% 16,697 27,144 + $155,172.2 1.5% 9,798 + $32,471.5 3.8% 17,346 10,190 $155,172.2 6.8% 4,174 $155,172.2 2.8% 335 $155,172.2 0.2% $48,742 2015 est. $5,416.3 0.0 0.0 7,544.2 3,533.9 870.5 17,364.9 32,471.5 11,017.0 21,454.5 10,597.6 4,341.0 348.4 $54,106.4 Feedback 2015 est. $5,416.3 0.0 0.0 7,544.2 3,533.9 870.5 17,364.9 32,471.5 11,017.0 21,454.5 10,597.6 4,341.0 348.4 $54,106.4 0.0 850.0 2,449.2 $8,386.6 2,276.6 13,962.4 6,991.0 5,710.6 $0.0 850.0 2,449.2 8,386.6 2,276.6 13,962.4 6,991.0 5,710.6 ($ millions) Cash & equivalents Marketable securities Short-term investments Accounts receivable, net Inventories Other current assets Total current assets Plant, property & equipment, gross Less: Accumulated depreciation Plant, property & equipment, net Long-term investments Intangible assets, net Other noncurrent assets Total assets Short-term debt Current portion of long-term debt Notes payable Accounts payable Other accrued expenses Total current liabilities Long-term debt Deferred taxes 0 842 0 8,064 2,189 11,095 7,841 5,491 plug $155,172.2 $155,172.2 0.0 850.0 8.0% 5.4% 1.5% - $155,172.2 $850.0 3.7% qattachments_a38c3770a618fb79e18682b8b211e482fe320a41.xlsx Other long-term liabilities Total liabilities Common stock & paid-in capital Retained earnings Treasury stock Total equity Total liabilities and equity 2,277 26,704 19,047 9,225 + (6,234) 22,038 $48,742 $155,172.2 $4,113.3 - + 1.5% $1,077.0 0.0 Financing shortfall (cash surplus) Critical footnotes and assumptions Assumed sales growth rate Prior year capital expenditures made Additional required capital expenditures (assumed) Prior year dividends paid Long-term debt shifting to current during year Short-term investment rate Short-term financing rate Common shares outstanding Current stock price Expected stock price 2,368.1 $29,032.1 $19,047.0 12,261.3 (6,234.0) 25,074.3 $54,106.4 2,368.1 29,032.1 19,047.0 12,158.5 (6,234.0) 24,971.5 $54,003.6 $2,449.2 $102.7 $2,552.0 2015 est. $4,113.3 1,219.0 (290.2) (135.9) (33.5) (13.4) 322.6 87.6 219.6 91.1 $5,580.2 Feedback $3,974.1 1,219.0 (290.2) (135.9) (33.5) (13.4) 322.6 87.6 219.6 91.1 $5,441.0 (5,327.5) (5,327.5) 4.0% $2,238 $3,000 $1,062 $850 4.0% 8.0% 546 $71.70 $71.70 Phillips 66 Forecasted Statement of Cash Flows ($ millions) Net income Add back depreciation expense (Increase) decrease in accounts receivable (Increase) decrease in inventories (Increase) decrease in other current assets (Increase) decrease in other long-term assets Increase (decrease) in accounts payable Increase (decrease) in other current liabilities Increase (decrease) in deferred income taxes Increase (decrease) in other long-term liabilities Net cash from operating activities Assumptions From income statement From income statement 7,254.0 - 7,544.2 3,398.0 - 3,533.9 837.0 - 870.5 335.0 - 348.4 8,386.6 - 8,064.0 2,276.6 - 2,189.0 5,710.6 - 5,491.0 2,368.1 - 2,277.0 Capital expenditures 155,172.2 1.5% qattachments_a38c3770a618fb79e18682b8b211e482fe320a41.xlsx (Investment in) sale of marketable securities (Investment in) sale of short-term investments (plug) (Investment in) sale of long-term investments (Investment in) sale of in intangible assets Net cash from investing activities 0.0 0.0 10,190.0 4,174.0 - - - - 0.0 0.0 10,597.6 4,341.0 0.0 0.0 (407.6) (167.0) ($5,902.1) 0.0 0.0 (407.6) (167.0) ($5,902.1) Dividends paid Issuance (repayment) of notes payable (plug) Issuance (repayment) of short-term debt Issuance (repayment) of long-term debt Issuance of stock/increase in common stock & paid-in capital Purchase of treasury stock Net cash from financing activities 4,113.3 2,449.2 0.0 7,841.0 19,047.0 (6,234.0) - - - - - 26.2% 0.0 0.0 8,683.0 19,047.0 (6,234.0) (1,077.0) 2,449.2 0.0 (842.0) 0.0 0.0 $530.2 (1040.6) 2,552.0 0.0 (842.0) 0.0 0.0 $669.4 208.3 5,208.0 $5,416.3 208.3 5,208.0 $5,416.3 Net change in cash Beginning cash Ending cash Current ratio Quick ratio Debt ratio Debt ratio Interest coverage ratio Working capital Retained earnings EBIT Sales Total assets MV-equity Total liabilities Working capital Total assets Retained earnings Total assets EBIT Total assets Ratios 2014 1.50 1.12 17.81% 54.79% 20.9 2015 est. 1.24 0.93 19.02% 53.66% 21.2 Altman Z-Score (Current) $5,602.0 $9,225.0 $5,997.0 $149,204.0 $48,742.0 $39,148.2 $26,704.0 0.1149 0.1893 0.1230 Feedback 1.24 0.93 19.02% 53.66% 12.6 Altman Z (Forecasted) $3,402.5 $12,158.5 $6,077.6 $155,172.2 $54,106.4 $39,148.2 $29,032.1 1.2000 1.4000 3.3000 = = = 0.1379 0.2650 0.4060 0.0629 0.2247 0.1123 0.0755 0.3146 0.3707 qattachments_a38c3770a618fb79e18682b8b211e482fe320a41.xlsx Mveq Bvliabs Sales Total assets 1.4660 3.0611 0.6000 0.9999 = = 0.8796 3.0608 1.3484 2.8679 0.8091 2.8676