Finance 430 project

profileYangqiren
430project.xlsx
Home>Business & Finance homework help>Finance 430 project

Project1

Income Statement
All numbers in thousands
Revenue 12/30/17 12/31/16 12/26/15
Total Revenue 63,525,000 62,799,000 63,056,000
Cost of Revenue 28,785,000 28,209,000 28,731,000
Gross Profit 34,740,000 34,590,000 34,325,000
Operating Expenses
Research Development - - -
Selling General and Administrative 24,231,000 24,805,000 24,613,000
Non Recurring - - 1,359,000
Others - - -
Total Operating Expenses - - -
Operating Income or Loss 10,509,000 9,785,000 8,353,000
Income from Continuing Operations
Total Other Income/Expenses Net 244,000 110,000 59,000
Earnings Before Interest and Taxes 10,753,000 9,895,000 8,412,000
Interest Expense 1,151,000 1,342,000 970,000
Income Before Tax 9,602,000 8,553,000 7,442,000
Income Tax Expense 4,694,000 2,174,000 1,941,000
Minority Interest 92,000 104,000 107,000
Net Income From Continuing Ops 4,857,000 6,329,000 5,452,000
Non-recurring Events
Discontinued Operations - - -
Extraordinary Items - - -
Effect Of Accounting Changes - - -
Other Items - - -
Net Income
Net Income 4,857,000 6,329,000 5,452,000
Preferred Stock And Other Adjustments - - -
Net Income Applicable To Common Shares 4,857,000 6,329,000 5,452,000
Balance Sheet
All numbers in thousands
Period Ending 12/26/15 12/31/16 12/30/17
Current Assets
Cash And Cash Equivalents 9,096,000 9,158,000 10,610,000
Short Term Investments 2,913,000 6,967,000 8,900,000
Net Receivables 6,437,000 6,694,000 7,024,000
Inventory 2,720,000 2,723,000 2,947,000 2796666.66666667
Other Current Assets 1,865,000 908,000 1,546,000
Total Current Assets 23,031,000 26,450,000 31,027,000
Long Term Investments 2,311,000 1,950,000 2,042,000
Property Plant and Equipment 16,317,000 16,591,000 17,240,000
Goodwill 14,177,000 14,430,000 14,744,000
Intangible Assets 13,081,000 13,433,000 13,838,000
Accumulated Amortization - - -
Other Assets 750,000 636,000 913,000
Deferred Long Term Asset Charges - - -
Total Assets 69,667,000 73,490,000 79,804,000
Current Liabilities
Accounts Payable 13,507,000 14,243,000 15,017,000
Short/Current Long Term Debt 4,071,000 6,892,000 5,485,000
Other Current Liabilities - - -
Total Current Liabilities 17,578,000 21,135,000 20,502,000
Long Term Debt 29,213,000 30,053,000 33,796,000
Other Liabilities 5,887,000 6,669,000 11,283,000
Deferred Long Term Liability Charges 4,959,000 4,434,000 3,242,000
Minority Interest 107,000 104,000 92,000
Negative Goodwill - - -
Total Liabilities 57,744,000 62,395,000 68,915,000
Stockholders' Equity
Misc. Stocks Options Warrants -145,000 -151,000 -156,000
Redeemable Preferred Stock - - -
Preferred Stock - - -
Common Stock 24,000 24,000 24,000
Retained Earnings 50,472,000 52,518,000 52,839,000
Treasury Stock -29,185,000 -31,468,000 -32,757,000
Capital Surplus 4,076,000 4,091,000 3,996,000
Other Stockholder Equity -13,319,000 -13,919,000 -13,057,000
Total Stockholder Equity 12,068,000 11,246,000 11,045,000
Net Tangible Assets -15,190,000 -16,617,000 -17,537,000
A. Profitability Ratios:
Year ROE Operation Management Asset Management Leverage Management
NI/Equity NI/Sales Sales/Assets Assets/Equity
2015 40.25% 0.08 0.91 5.77
2016 56.28% 0.10 0.85 6.53
2017 49.36% 0.09 0.79 7.23
B. Efficiency Ratios:
Year Asset Turnover A/R Turnover Rate Inventory Turnover Rate Fixed Asset Turnover
Sales/Total assets Sales/Account Receivable COGS/Average inventory Sales/Average Fixed Assets
2015 0.91 9.87 10.58 4.86
2016 0.85 9.38 10.36 4.67
2017 0.79 8.98 9.75 4.56
C. Leverage Ratios:
Year Debt Ratio Debt-to-Equity Ratio Times Interest Earned
Total Liabilities/Total Assets Total Liabilities/Stockholder’s Equity Earnings before Interest and Taxes/Interest Expense
2015 0.83 4.78 9.34
2016 0.85 5.55 7.37
2017 0.86 6.24 8.67
D. Liquidity Ratios:
Year Current Ratio Working Capital
Current Assets/Current Liabilities Current Assets – Current Liabilities
2015 1.3102173171 5,453,000
2016 1.25147859 5,315,000
2017 1.5133645498 10,525,000
2015 2016 2017
Cash And Cash Equivalents 9,096,000 9,158,000 10,610,000
Short Term Investments 2,913,000 6,967,000 8,900,000
Net Receivables 6,437,000 6,694,000 7,024,000
Inventory 2,720,000 2,723,000 2,947,000
Other Current Assets 1,865,000 908,000 1,546,000
Total Current Assets 23,031,000 26,450,000 31,027,000
Current Liabilities
Accounts Payable 13,507,000 14,243,000 15,017,000
Short/Current Long Term Debt 4,071,000 6,892,000 5,485,000
Other Current Liabilities - - -
Total Current Liabilities 17,578,000 21,135,000 20,502,000
Current Ratio (Current Assets/Current Liabilities) 1.31 1.25 1.51
Quick Ratio (Cash+A/R)/Current Liabilities 0.88 0.75 0.86
Working Capital (Current Assets – Current Liabilities) 5,453,000 5,315,000 10,525,000
2015 2016 2017

Project2

Pepsi Beta Calculation S&P 500 Price(Market) S&P 500Return(X) Pepsi Price Pepsi Price Retrun(Y)
1/1/13 1498.109985 72.849998
2/1/13 1514.680054 1.11% 75.769997 4.01%
3/1/13 1569.189941 3.60% 79.110001 4.41%
4/1/13 1597.569946 1.81% 82.470001 4.25% S&P 500 Pepsi
5/1/13 1630.73999 2.08% 80.769997 -2.06% Average return 1.02% 0.90%
6/1/13 1606.280029 -1.50% 81.790001 1.26% standard deviation of returns 2.71% 3.22%
7/1/13 1685.72998 4.95% 83.540001 2.14% Correlation with the Market 56.35%
8/1/13 1632.969971 -3.13% 79.730003 -4.56% Beta 0.6698
9/1/13 1681.550049 2.98% 79.5 -0.29%
10/1/13 1756.540039 4.46% 84.089996 5.77% SUMMARY OUTPUT
11/1/13 1805.810059 2.81% 84.459999 0.44%
12/1/13 1848.359985 2.36% 82.940002 -1.80% Regression Statistics
1/1/14 1782.589966 -3.56% 80.360001 -3.11% Multiple R 0.5634946504
2/1/14 1859.449951 4.31% 80.07 -0.36% R Square 0.3175262211
3/1/14 1872.339966 0.69% 83.5 4.28% Adjusted R Square 0.3055529969
4/1/14 1883.949951 0.62% 85.889999 2.86% Standard Error 0.0267982803
5/1/14 1923.569946 2.10% 88.330002 2.84% Observations 59
6/1/14 1960.22998 1.91% 89.339996 1.14%
7/1/14 1930.670044 -1.51% 88.099998 -1.39% ANOVA
8/1/14 2003.369995 3.77% 92.489998 4.98% df SS MS F Significance F
9/1/14 1972.290039 -1.55% 93.089996 0.65% Regression 1 0.0190450594 0.0190450594 26.5196922733 0.0000033731
10/1/14 2018.050049 2.32% 96.169998 3.31% Residual 57 0.0409344262 0.0007181478
11/1/14 2067.560059 2.45% 100.099998 4.09% Total 58 0.0599794856
12/1/14 2058.899902 -0.42% 94.559998 -5.53%
1/1/15 1994.98999 -3.10% 93.779999 -0.82% Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
2/1/15 2104.5 5.49% 98.980003 5.54% Intercept 0.0021356304 0.0037339758 0.5719454418 0.5696077665 -0.0053415271 0.0096127879 -0.0053415271 0.0096127879
3/1/15 2067.889893 -1.74% 95.620003 -3.39% X Variable 1 0.6698266195 0.1300703062 5.149727398 0.0000033731 0.4093653241 0.930287915 0.4093653241 0.930287915
4/1/15 2085.51001 0.85% 95.120003 -0.52%
5/1/15 2107.389893 1.05% 96.43 1.38%
6/1/15 2063.110107 -2.10% 93.339996 -3.20%
7/1/15 2103.840088 1.97% 96.349998 3.22%
8/1/15 1972.180054 -6.26% 93.599998 -2.85%
9/1/15 1920.030029 -2.64% 94.300003 0.75%
10/1/15 2079.360107 8.30% 102.190002 8.37%
11/1/15 2080.409912 0.05% 100.160004 -1.99%
12/1/15 2043.939941 -1.75% 99.919998 -0.24%
1/1/16 1940.23999 -5.07% 99.300003 -0.62%
2/1/16 1932.22998 -0.41% 97.82 -1.49%
3/1/16 2059.73999 6.60% 102.480003 4.76%
4/1/16 2065.300049 0.27% 102.959999 0.47%
5/1/16 2096.949951 1.53% 101.169998 -1.74%
6/1/16 2098.860107 0.09% 105.940002 4.71%
7/1/16 2173.600098 3.56% 108.919998 2.81%
8/1/16 2170.949951 -0.12% 106.75 -1.99%
9/1/16 2168.27002 -0.12% 108.769997 1.89%
10/1/16 2126.149902 -1.94% 107.199997 -1.44%
11/1/16 2198.810059 3.42% 100.099998 -6.62%
12/1/16 2238.830078 1.82% 104.629997 4.53%
1/1/17 2278.870117 1.79% 103.779999 -0.81%
2/1/17 2363.639893 3.72% 110.379997 6.36%
3/1/17 2362.719971 -0.04% 111.860001 1.34%
4/1/17 2384.199951 0.91% 113.279999 1.27%
5/1/17 2411.800049 1.16% 116.870003 3.17%
6/1/17 2423.409912 0.48% 115.489998 -1.18%
7/1/17 2470.300049 1.93% 116.610001 0.97%
8/1/17 2471.649902 0.05% 115.730003 -0.75%
9/1/17 2519.360107 1.93% 111.43 -3.72%
10/1/17 2575.26001 2.22% 110.230003 -1.08%
11/1/17 2647.580078 2.81% 116.519997 5.71%
12/1/17 2673.610107 0.98% 119.919998 2.92%
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.5634946504
R Square 0.3175262211
Adjusted R Square 0.3055529969
Standard Error 0.0267982803
Observations 59
ANOVA
df SS MS F Significance F
Regression 1 0.0190450594 0.0190450594 26.5196922733 0.0000033731
Residual 57 0.0409344262 0.0007181478
Total 58 0.0599794856
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.0021356304 0.0037339758 0.5719454418 0.5696077665 -0.0053415271 0.0096127879 -0.0053415271 0.0096127879
X Variable 1 0.6698266195 0.1300703062 5.149727398 0.0000033731 0.4093653241 0.930287915 0.4093653241 0.930287915

Pepsi Price Retrun(Y)

1.11E-2 3.5999999999999997E-2 1.8100000000000002E-2 2.0799999999999999E-2 -1.4999999999999999E-2 4.9500000000000002E-2 -3.1300000000000001E-2 2.98E-2 4.4600000000000001E-2 2.81E-2 2.3599999999999999E-2 -3.56E-2 4.3099999999999999E-2 6.8999999999999999E-3 6.1999999999999998E-3 2.1000000000000001E-2 1.9099999999999999E-2 -1.5100000000000001E-2 3.7699999999999997E-2 -1.55E-2 2.3199999999999998E-2 2.4500000000000001E-2 -4.1999999999999997E-3 -3.1E-2 5.4899999999999997E-2 -1.7399999999999999E-2 8.5000000000000006E-3 1.0500000000000001E-2 -2.1000000000000001E-2 1.9699999999999999E-2 -6.2600000000000003E-2 -2.64E-2 8.3000000000000004E-2 5.0000000000000001E-4 -1.7500000000000002E-2 -5.0700000000000002E-2 -4.1000000000000003E-3 6.6000000000000003E-2 2.7000000000000001E-3 1.5299999999999999E-2 8.9999999999999998E-4 3.56E-2 -1.1999999999999999E-3 -1.1999999999999999E-3 -1.9400000000000001E-2 3.4200000000000001E-2 1.8200000000000001E-2 1.7884358171464578E-2 3.71 9816033727915E-2 -3.8919718808453973E-4 9.0912085493182089E-3 1.1576251391341417E-2 4.8137750908554414E-3 1.9348826118030613E-2 5.4643281108568138E-4 1.9302978533243684E-2 2.2188135330349579E-2 2.8082627664458622E-2 9.8316304826040514E-3 4.0082348389357599E-2 4.4080825290252992E-2 4.2472506099450053E-2 -2.0613604697300694E-2 1.2628501150000044E-2 2.139625844973397E-2 -4.5606870414090693E-2 -2.8847735023915201E-3 5.7735798742138356E-2 4.4000834534466734E-3 -1.7996649514523313E-2 -3.1106835517076666E-2 -3.6087729764961518E-3 4.2837517172474172E-2 2.8622742514970097E-2 2.8408464645575209E-2 1.1434325564715894E-2 -1.3879539461810586E-2 4.9829740064239285E-2 6.4871663204057956E-3 3.3086283514288772E-2 4.0865135507229526E-2 -5.5344656450442752E-2 -8.2487205636361111E-3 5.54489662555871 09E-2 -3.394625073915182E-2 -5.2290314192941414E-3 1.3772045402479749E-2 -3.2044011199834153E-2 3.2247719402087829E-2 -2.854177537191023E-2 7.4786860572369288E-3 8.3669127772986421E-2 -1.9864937472063127E-2 -2.3962259426426736E-3 -6.2049140553425844E-3 -1.4904360073382984E-2 4.7638550398691512E-2 4.6838015802946444E-3 -1.7385402266757884E-2 4.7148404609042294E-2 2.8129091407795136E-2 -1.992286118110291E-2 1.8922688524590198E-2 -1.4434127455202627E-2 -6.6231335808712724E-2 4.5254736168925835E-2 -8.123846166219419E-3 6.3596049947928779E-2 1.3408262730791647E-2 1.2694421484941759E-2 3.1691419771287192E-2 -1.1808034265216859E-2 9.6978354783588874E-3 -7.5465053807863402E-3 -3.7155472984823044E-2 -1.0769065781208027E-2 5.7062449685318498E-2 2.9179549326627627E-2

Project3

Income Statement Actual Historical Financing 2017
All numbers in thousands Debt 33284000.00
Revenue 12/30/17 12/31/16 12/26/15 Equity 12068000.00
Total Revenue 63,525,000 62,799,000 63,056,000 Weight of debt=D/(D+E) 0.73
Cost of Revenue 28,785,000 28,209,000 28,731,000 Weight of equity=E/(D+E) 0.27
Gross Profit 34,740,000 34,590,000 34,325,000
Operating Expenses Risk Free rate of return 0.90%
Research Development - - - Beta 0.67
Selling General and Administrative 24,231,000 24,805,000 24,613,000 Expecte return of the market 1.02%
Non Recurring - - 1,359,000 Cost of equity= Rrf+Beta*(Rm-Rrf) 0.01
Others - - -
Total Operating Expenses - - - Cost of debt (interst expenses / Value of debt) 0.03
Operating Income or Loss 10,509,000 9,785,000 8,353,000 Tax rate 43.65%
Income from Continuing Operations WACC=wd*rd(1-T)+wcs*rcs 0.02
Total Other Income/Expenses Net 244,000 110,000 59,000
Earnings Before Interest and Taxes 10,753,000 9,895,000 8,412,000
Interest Expense 1,151,000 1,342,000 970,000 US Treasury Bonds Rates
Income Before Tax 9,602,000 8,553,000 7,442,000 Maturity Yield Yesterday Last Week Last Month
Income Tax Expense 4,694,000 2,174,000 1,941,000 3 Month 1.17 1.19  1.21  1.05 
Minority Interest 92,000 104,000 107,000 6 Month 1.37 1.38  1.35  1.20 
Net Income From Continuing Ops 4,857,000 6,329,000 5,452,000 2 Year 1.73 1.71  1.70  1.56 
Non-recurring Events 3 Year 1.84 1.82  1.81  1.68 
Discontinued Operations - - - 5 Year 2.07 2.03  2.02  1.98 
Extraordinary Items - - - 10 Year 2.36 2.31  2.30  2.35 
Effect Of Accounting Changes - - - 30 Year 2.79 2.74  2.72  2.86
Other Items - - -
Net Income
Net Income 4,857,000 6,329,000 5,452,000 2015 2016 2017
Preferred Stock And Other Adjustments - - - Tax Rate 23.07% 22% 43.65%
Net Income Applicable To Common Shares 4,857,000 6,329,000 5,452,000
Balance Sheet
All numbers in thousands
Period Ending 12/26/15 12/31/16 12/30/17
Current Assets
Cash And Cash Equivalents 9,096,000 9,158,000 10,610,000
Short Term Investments 2,913,000 6,967,000 8,900,000
Net Receivables 6,437,000 6,694,000 7,024,000
Inventory 2,720,000 2,723,000 2,947,000
Other Current Assets 1,865,000 908,000 1,546,000
Total Current Assets 23,031,000 26,450,000 31,027,000
Long Term Investments 2,311,000 1,950,000 2,042,000
Property Plant and Equipment 16,317,000 16,591,000 17,240,000
Goodwill 14,177,000 14,430,000 14,744,000
Intangible Assets 13,081,000 13,433,000 13,838,000
Accumulated Amortization - - -
Other Assets 750,000 636,000 913,000
Deferred Long Term Asset Charges - - -
Total Assets 69,667,000 73,490,000 79,804,000
Current Liabilities
Accounts Payable 13,507,000 14,243,000 15,017,000
Short/Current Long Term Debt 4,071,000 6,892,000 5,485,000
Other Current Liabilities - - -
Total Current Liabilities 17,578,000 21,135,000 20,502,000
Long Term Debt 29,213,000 30,053,000 33,796,000
Other Liabilities 5,887,000 6,669,000 11,283,000
Deferred Long Term Liability Charges 4,959,000 4,434,000 3,242,000
Minority Interest 107,000 104,000 92,000
Negative Goodwill - - -
Total Liabilities 57,744,000 62,395,000 68,915,000
Stockholders' Equity
Misc. Stocks Options Warrants -145,000 -151,000 -156,000
Redeemable Preferred Stock - - -
Preferred Stock - - -
Common Stock 24,000 24,000 24,000
Retained Earnings 50,472,000 52,518,000 52,839,000
Treasury Stock -29,185,000 -31,468,000 -32,757,000
Capital Surplus 4,076,000 4,091,000 3,996,000
Other Stockholder Equity -13,319,000 -13,919,000 -13,057,000
Total Stockholder Equity 12,068,000 11,246,000 11,045,000
Net Tangible Assets -15,190,000 -16,617,000 -17,537,000
Cash Flow
All numbers in thousands
Period Ending 12/30/17 12/31/16 12/26/15
Net Income 4,857,000 6,329,000 5,452,000
Operating Activities, Cash Flows Provided By or Used In
Depreciation 2,369,000 2,368,000 2,416,000
Adjustments To Net Income 3,545,000 950,000 2,089,000
Changes In Accounts Receivables -202,000 -349,000 -461,000
Changes In Liabilities -137,000 1,326,000 1,747,000
Changes In Inventories -168,000 -75,000 -244,000
Changes In Other Operating Activities -321,000 74,000 -184,000
Total Cash Flow From Operating Activities 9,994,000 10,673,000 10,864,000
Investing Activities, Cash Flows Provided By or Used In
Capital Expenditures -2,969,000 -3,040,000 -2,758,000
Investments -1,910,000 -4,301,000 -400,000
Other Cash flows from Investing Activities 476,000 193,000 -411,000
Total Cash Flows From Investing Activities -4,403,000 -7,148,000 -3,569,000
Financing Activities, Cash Flows Provided By or Used In
Dividends Paid -4,472,000 -4,227,000 -4,040,000
Sale Purchase of Stock -1,543,000 -2,542,000 -4,501,000
Net Borrowings 2,050,000 3,746,000 4,632,000
Other Cash Flows from Financing Activities -221,000 -188,000 -203,000
Total Cash Flows From Financing Activities -4,186,000 -3,211,000 -4,112,000
Effect Of Exchange Rate Changes 47,000 -252,000 -221,000
Change In Cash and Cash Equivalents 1,452,000 62,000 2,962,000

Project4

Income Statement
All numbers in thousands
Revenue 12/30/17 12/31/16 12/26/15
Total Revenue 63,525,000 62,799,000 63,056,000
Cost of Revenue 28,785,000 28,209,000 28,731,000
Gross Profit 34,740,000 34,590,000 34,325,000 Cash Flow
Operating Expenses All numbers in thousands
Research Development - - - Period Ending 12/31/17 12/31/16 12/31/15
Selling General and Administrative 24,231,000 24,805,000 24,613,000 Net Income 1,248,000 6,527,000 7,351,000
Non Recurring - - 1,359,000 Operating Activities, Cash Flows Provided By or Used In
Others - - - Depreciation 1,260,000 1,787,000 1,970,000
Total Operating Expenses - - - Adjustments To Net Income 923,000 680,000 1,349,000
Operating Income or Loss 10,509,000 9,785,000 8,353,000 Changes In Accounts Receivables - - -
Income from Continuing Operations Changes In Liabilities - - -
Total Other Income/Expenses Net 244,000 110,000 59,000 Changes In Inventories - - -
Earnings Before Interest and Taxes 10,753,000 9,895,000 8,412,000 Changes In Other Operating Activities 3,537,000 -221,000 -157,000
Interest Expense 1,151,000 1,342,000 970,000 Total Cash Flow From Operating Activities 6,995,000 8,796,000 10,528,000
Income Before Tax 9,602,000 8,553,000 7,442,000 Investing Activities, Cash Flows Provided By or Used In
Income Tax Expense 4,694,000 2,174,000 1,941,000 Capital Expenditures -1,675,000 -2,262,000 -2,553,000
Minority Interest 92,000 104,000 107,000 Investments -609,000 1,125,000 -1,752,000
Net Income From Continuing Ops 4,857,000 6,329,000 5,452,000 Other Cash flows from Investing Activities -101,000 138,000 -1,881,000
Non-recurring Events Total Cash Flows From Investing Activities -2,385,000 -999,000 -6,186,000
Discontinued Operations - - - Financing Activities, Cash Flows Provided By or Used In
Extraordinary Items - - - Dividends Paid -6,320,000 -6,043,000 -5,741,000
Effect Of Accounting Changes - - - Sale Purchase of Stock -2,087,000 -2,247,000 -2,319,000
Other Items - - - Net Borrowings 1,089,000 1,666,000 2,696,000
Net Income Other Cash Flows from Financing Activities -91,000 79,000 251,000
Net Income 4,857,000 6,329,000 5,452,000 Total Cash Flows From Financing Activities -7,409,000 -6,545,000 -5,113,000
Preferred Stock And Other Adjustments - - - Effect Of Exchange Rate Changes 242,000 -6,000 -878,000
Net Income Applicable To Common Shares 4,857,000 6,329,000 5,452,000 Change In Cash and Cash Equivalents -2,549,000 1,246,000 -1,649,000
Balance Sheet
All numbers in thousands
Period Ending 12/26/15 12/31/16 12/30/17
Current Assets
Cash And Cash Equivalents 9,096,000 9,158,000 10,610,000
Short Term Investments 2,913,000 6,967,000 8,900,000
Net Receivables 6,437,000 6,694,000 7,024,000
Inventory 2,720,000 2,723,000 2,947,000
Other Current Assets 1,865,000 908,000 1,546,000
Total Current Assets 23,031,000 26,450,000 31,027,000
Long Term Investments 2,311,000 1,950,000 2,042,000
Property Plant and Equipment 16,317,000 16,591,000 17,240,000
Goodwill 14,177,000 14,430,000 14,744,000
Intangible Assets 13,081,000 13,433,000 13,838,000
Accumulated Amortization - - -
Other Assets 750,000 636,000 913,000
Deferred Long Term Asset Charges - - -
Total Assets 69,667,000 73,490,000 79,804,000
Current Liabilities
Accounts Payable 13,507,000 14,243,000 15,017,000
Short/Current Long Term Debt 4,071,000 6,892,000 5,485,000
Other Current Liabilities - - -
Total Current Liabilities 17,578,000 21,135,000 20,502,000
Long Term Debt 29,213,000 30,053,000 33,796,000
Other Liabilities 5,887,000 6,669,000 11,283,000
Deferred Long Term Liability Charges 4,959,000 4,434,000 3,242,000
Minority Interest 107,000 104,000 92,000
Negative Goodwill - - -
Total Liabilities 57,744,000 62,395,000 68,915,000
Stockholders' Equity
Misc. Stocks Options Warrants -145,000 -151,000 -156,000
Redeemable Preferred Stock - - -
Preferred Stock - - -
Common Stock 24,000 24,000 24,000
Retained Earnings 50,472,000 52,518,000 52,839,000
Treasury Stock -29,185,000 -31,468,000 -32,757,000
Capital Surplus 4,076,000 4,091,000 3,996,000
Other Stockholder Equity -13,319,000 -13,919,000 -13,057,000
Total Stockholder Equity 12,068,000 11,246,000 11,045,000
Net Tangible Assets -15,190,000 -16,617,000 -17,537,000
Cash Flow
All numbers in thousands
Period Ending 12/30/17 12/31/16 12/26/15
Net Income 4,857,000 6,329,000 5,452,000
Operating Activities, Cash Flows Provided By or Used In
Depreciation 2,369,000 2,368,000 2,416,000
Adjustments To Net Income 3,545,000 950,000 2,089,000
Changes In Accounts Receivables -202,000 -349,000 -461,000
Changes In Liabilities -137,000 1,326,000 1,747,000
Changes In Inventories -168,000 -75,000 -244,000
Changes In Other Operating Activities -321,000 74,000 -184,000
Total Cash Flow From Operating Activities 9,994,000 10,673,000 10,864,000
Investing Activities, Cash Flows Provided By or Used In
Capital Expenditures -2,969,000 -3,040,000 -2,758,000
Investments -1,910,000 -4,301,000 -400,000
Other Cash flows from Investing Activities 476,000 193,000 -411,000
Total Cash Flows From Investing Activities -4,403,000 -7,148,000 -3,569,000
Financing Activities, Cash Flows Provided By or Used In
Dividends Paid -4,472,000 -4,227,000 -4,040,000
Sale Purchase of Stock -1,543,000 -2,542,000 -4,501,000
Net Borrowings 2,050,000 3,746,000 4,632,000
Other Cash Flows from Financing Activities -221,000 -188,000 -203,000
Total Cash Flows From Financing Activities -4,186,000 -3,211,000 -4,112,000
Effect Of Exchange Rate Changes 47,000 -252,000 -221,000
Change In Cash and Cash Equivalents 1,452,000 62,000 2,962,000
Figure 1 Liabilities and equity 2016 2017
MicroDrive’s Most Recent Financial Statements (Thousand, Except for Per Share Data) Accounts Payable $ 14,243,000 $ 15,017,000
INCOME STATEMENTS BALANCE SHEETS Short/Current Long Term Debt 6,892,000 5,485,000
2016 2017 Assets 2016 2017 Other Current Liabilities - -
Net sales $ 62,799,000 $ 63,525,000 Cash And Cash Equivalents $ 9,158,000 $ 10,610,000 Total Current Liabilities 21,135,000 20,502,000
COGS (excl. depr.) 28,209,000 28,785,000 Short Term Investments 6,967,000 8,900,000 Long Term Debt 30,053,000 33,796,000
Depreciation 2,368,000 2,369,000 Net Receivables 6,694,000 7,024,000 Other Liabilities 6,669,000 11,283,000
Other operating expenses - - Inventory 2,723,000 2,947,000 Deferred Long Term Liability Charges 4,434,000 3,242,000
EBIT $ 32,222,000 $ 32,371,000 Other Current Assets 908,000 1,546,000 Minority Interest 104,000 92,000
Interest expense 1,342,000 1,151,000 Total Current Assets 26,450,000 31,027,000 Negative Goodwill - -
Pre-tax earnings $ 30,880,000 $ 31,220,000 Long Term Investments 1,950,000 2,042,000 Total Liabilities 62,395,000 68,915,000
Taxes (7,849,073) 15,262,100 Property Plant and Equipment 16,591,000 17,240,000 Stockholders' Equity
NI before pref. div. $ 38,729,073 $ 15,957,900 Goodwill 14,430,000 14,744,000 Misc. Stocks Options Warrants (151,000) (156,000)
Preferred div. - - Intangible Assets 13,433,000 13,838,000 Redeemable Preferred Stock - -
Net income $ 38,729,073 $ 15,957,900 Accumulated Amortization - - Preferred Stock - -
Other Assets 636,000 913,000 Common Stock 24,000 24,000
Other Data Deferred Long Term Asset Charges - - Retained Earnings 52,518,000 52,839,000
Common dividends $0 $0 Total Assets 73,490,000 79,804,000 Treasury Stock (31,468,000) (32,757,000)
Addition to RE $52,518,000 $50,472,000 Capital Surplus 4,091,000 3,996,000
Tax rate -25% 49% Other Stockholder Equity (13,919,000) (13,057,000)
Shares of common stock 4,000,000 4,000,000 Total Stockholder Equity 11,246,000 11,045,000
Earnings per share $9.68 $3.99 Net Tangible Assets (16,617,000) (17,537,000)
Dividends per share $0.00 $0.00
Price per share $5.67 $6.89
Figure 2
Pepsi's Forecast: Inputs for the Selected Scenario
Status Quo KO Pepsi Actual Pepsi
Inputs Actual Actual Forecast
1. Operating Ratios 2017 2016 2017 2018 2019 2020 2021 2022
Sales growth rate 5% -1% 1% 8% 6% 7% 5% 5%
COGS (excl. depr.) / Sales 76% 223% 221% 200% 178% 178% 178% 178%
Depreciation / Net PP&E 9% 14% 14% 35% 35% 35% 35% 35%
Other op. exp. / Sales 10% 0% 0% 18% 18% 18% 19% 20%
Cash / Sales 1% 15% 17% 6% 6% 7% 7% 7% Actual Historical Financing
Acc. rec. / Sales 8% 11% 11% 10% 8% 8% 8% 8% 2016 2017
Inventory / Sales 15% 4% 5% 20% 5% 4% 4% 4% Long-term debt $30,053,000 $33,796,000
Net PP&E / Sales 33% 26% 27% 40% 55% 60% 60% 60% Short-term debt $6,892,000 $5,485,000
Acc. pay. / Sales 4% 23% 24% 20% 20% 23% 23% 23% Preferred stock $0 $0
Accruals / Sales 7% 6% 6% 6% 6% 6% 6% 5% Market value of equity = (Price x # shares) $0 $0
Tax rate 40% -25% 49% 9% 12% 13% 13% 13% Total $36,945,000 $39,281,000
2. Capital Structure Actual Market Weights Target Market Weights
% Long-term debt 22% 48% 49% 50% 50% 44% 55% 57% See the box to the right for calculations of the actual capital structures, based on market values, for the past two years. Percent long-term debt 49% 54%
% Short-term debt 3% 11% 8% 8% 8% 6% 7% 9% Percent short-term debt 8% 8%
% Preferred stock 0% 0% 0% 0% 0% 0% 2% 3% Percent preferred stock 0% 0%
% Common stock 75% 100% 100% 80% 80% 80% 5% 5% Percent market value of equity 0% 0%
3. Costs of Capital Forecast Total 57% 62%
Rate on LT debt 9.0% 9% 9% 9% 9%
Rate on ST debt 10.0% 10% 10% 10% 10%
Rate on preferred stock (ignoring flotation costs) 8.0% 8% 8% 8% 8%
Cost of equity 13.58% 14% 14% 14% 14%
4. Target Dividend Policy Actual
Growth rate of dividends 0% 0.0% 0% 0% 0% 0% 0%
Figure 3
Pepsi's Forecast of Operations for the Selected Scenario (Thousands of Dollars, Except for Per Share Data)
INCOME STATEMENTS KO Pepsi Pepsi
Panel A: Inputs Actual Actual Forecast
A1. Operating Ratios 2017 2016 2017 2018 2019 2020 2021 2022
Sales growth rate 5% -7% 7% 8% 6% 7% 5% 5%
COGS (excl. depr.) / Sales 76% 211% 182% 200% 178% 178% 178% 178%
Depreciation / Net PP&E 9% 33% 41% 35% 35% 35% 35% 35%
Other op. exp. / Sales 10% 22% 15% 18% 18% 18% 19% 19%
Cash / Sales 1% 8% 8% 6% 6% 7% 7% 7%
Acc. rec. / Sales 8% 3% 12% 10% 8% 8% 8% 8%
Inventory / Sales 15% 4% 3% 20% 5% 4% 4% 4%
Net PP&E / Sales 33% 63% 56% 40% 55% 60% 60% 60%
Acc. pay. / Sales 4% 23% 21% 20% 20% 23% 23% 23%
Accruals / Sales 7% 6% 6% 6% 6% 6% 6% 6%
Tax rate 40% 8% 15% 9% 12% 13% 13% 13%
Panel B: Results Actual Forecast
B1. Sales Revenues 2017 2018 2019 2020 2021 0
Net sales $63,525,000 $68,607,000 $72,723,420 $77,814,059 $81,704,762 $81,704,762
B2. Operating Assets and Operating Liabilities
Cash $10,610,000 $4,116,420 $4,363,405 $5,446,984 $5,719,333 $0
Accounts receivable $7,024,000 $7,726,400 $8,344,512 $9,012,073 $9,733,039 $0
Inventories $2,947,000 13721400 3636171 3112562 3268190 $0
Net PP&E $17,240,000 $27,442,800 $39,997,881 $46,688,436 $49,022,857 $0
Accounts payable $15,017,000 $13,721,400 $14,544,684 $17,897,234 $18,792,095 $0
Accruals $2,319,863 $2,282,244 $2,339,425 $2,474,740 $2,647,971 $0
B3. Operating Income
COGS (excl. depr.) $18,969,000 $137,214,000 $129,447,688 $138,509,026 $145,434,477 $0
Depreciation $7,848,000 $24,012,450 $25,453,197 $27,234,921 $28,596,667 $0
Other operating expenses $5,349,000 $12,349,260 $13,090,216 $14,006,531 $15,523,905 $0
EBIT $2,366,000 $2,507,960 $2,658,438 $2,844,528 $3,043,645 $81,704,762
Net operating profit after taxes −$12,896,100 $2,282,244 $2,339,425 $2,474,740 $2,647,971 $81,704,762
B4. Free Cash Flows
Net operating working capital $3,244,137 $9,560,576 −$540,021 −$2,800,354 −$2,719,504 $0
Total operating capital $20,484,137 $37,003,376 $39,457,860 $43,888,082 $46,303,353 $0
FCF = NOPAT – Δ op capital −$33,380,236 −$14,236,996 −$115,059 −$1,955,482 $232,700 $128,008,116
B5. Estimated Intrinsic Value
Target WACC 15.7% 15.7% 15.7% 15.7% 0.0%
Return on invested capital -63.0% 11.7% 11.7% 11.7% 11.7% ERROR:#DIV/0!
Growth in FCF -99% 1599.6% -111.9% 54910.0%
Horizon Value: Value of operations 101745634.55 $101,745,635
+ ST investments 8900000.00 $0
= −$128,241,239 Estimated total intrinsic value 110645634.55 $101,745,635
− All debt 45079000.00 $0
Value of Operations: − Preferred stock 0.00 $0
Present value of HV $53,495,882 Estimated intrinsic value of equity 65566634.55 $101,745,635
+ Present value of FCF $48,249,753 ÷ Number of shares 4000000.00 $0
Value of operations = $101,745,635 Estimated intrinsic stock price = 16.39 ERROR:#DIV/0!