Need help with this problem. tI need solutions for all the parts

Question 1 Note: For students' convenience, i posted an excel spreadsheet for Q1 in the problem set foider. it contains oil the toblesfrom Q1 to answer individual parts. You can use the spreadsheet to calculate your solution. Today is December 29, 2012. You work as a financial manager at PG and want to know projected cash flows of your company for the next two years for decisionmaking purposes. Below is some financial statement information that is loosely based on Procter & Gamble (PG hereafter) for the year ended December 29, 2012: Income Statement (in millions 5} Year Ended December 29, 2012 Sales 83,680 Cost of Sales 39,190 Depreciation 2,130 Gross Income 42,360 Research and Development 0 Selling, General, Administrative Expenses 26,420 Operating Income (EBIT) 15,940 Interest Expense 1,840 Pretax Income 14,100 Tax 3,800 Net Income 10,300 Balance Sheet [in millions 5} Year Ended December 29, 2012 Cash 4,440 Accounts Payable 16,210 Accounts Receivables 7,070 Shortterm Debt 8,700 Inventory 6,700 Current Assets 21,910 Current Liabilities 24,910 Net Property, Plant, Equipment 20,400 Longterm Debt 43,800 Goodwill and Intangibles 84,640 Other Longterm Assets 8,900 Longterm Assets 113,940 Longterm Liabilities 43,800 Total Liabilities 68,710 Total Stockholders' Equity 63,440 Total Assets 132,150 Total L+E 132,150 Assume the following: (1) net property, plant, and equipment for the year ended December 29, 2011 was $21,300M, {2) longterm debt for the year ended December 29, 2011 was $32,000M, and (3) PG has no excess cas h . 8) Assume that PG's sales will increase by 0.5% per year for the next two years. To support the projected sales growth, PG will expand its production during this period by using its existing plants more efficiently, rather than building new plants. That is, PG plans to maintain its current net PP&E level for the next two years. Assume that PG depreciates its assets using Declining Balance Method with 10% depreciation rate. That is, PG's assets will depreciate by 10% each year: DEPN: 2 Opening net PPSzEt X 10% Opening net PPSLEt 2 Closing net PP&Et_1 Based on these assumptions, forecast PG's capital expenditures over the next two yea rs. Use the following equation to solve for capital expenditures: Closing net PP&Et 2 Closing net PP&Et_1 + CAPEXt DEPNt PG CAPEX Proiections Year 2012 2013 2014 Opening BV of net PP&E 21300 Capital Investment 123i]I Depreciation 2130 Closing BV of net PP&E 20400 (Opening Book Value is the Closing Book Value in the previous year) bl The interest rate is 4% on PG's shortterm debt and 5.5% on PG's longterm debt. Based on these assumptions, calculate PG's interest payments in 2013. Use the following equation to calculate interest payments: Interest; 2 Interest Ratesh X Debthim + Interest Ratekmg X Debtt_1_1mg 61 Assume that PG's sales will increase by 0.5% per year for the next two years. Assume also that the ratio of cost of sales to sales and the ratio of selling, general, administrative expenses to sales will remain the same from year 2012 through 2014. PG plans to keep its R&D expenditures at zero. The corporate tax rate of P6 is 27%. Based on these assumptions and your answers from part (a) and (b), construct PG's pro forma income statement for 2013. Net Income Statement Year 2012 2013 Sales 83680 Cost of Sales 39190 Depreciation 2130 Gross Income 42360 Research 8; Development 0 SeIIIing, General, Admin Expenses 26420 Operating Income {EBIT} 15940 Interest Expense 1840 Pretax Income 14100 Tax 3800 Net Income 10300 d} The table below lists PG's current operating working capital as a fraction of sales and cost of sales. To utilize their operating working capital more efficiently, PG plans to tighten its credit policy, speed up customer payments and reduce PG's inventory. Specifically, PG plans to reduce the ratios of operating cash to sales, accounts receivables to sales, inventory to sales by 0.5% each year for the next two years. For example, cash will be 5.3060.5=4.806% of 2013 sales in 2013. Likewise, accounts receivables will be 8.4490.5=7.949% of 2013 sales in 2013. At the same time, PG will also reduce the ratio of accounts payable to cost of sales by 2% each year for the next two years. For example, accounts payable will be 41.3632=39.363% of 2013 cost of sales in 2013. Based on these projections, calculate PG's operating working capital requirements in 2013. Operating Current Assets 2012 % of 2012 sales Operating Cash 4440 5.306% =4440f'83680 Accounts Receivables 7070 8.449% =7070/83680 lnve ntory 6700 8.007% =6700733 630 % of 2012 cost of Operating Current Liability 2012 sales Accounts Payable 16210 41.36396 =16210139190 PG OWC Requirements Year 2012 2013 Operating Current Assets Operating Cash 4440 Accounts Receivables 7070 Inventory 6700 Operating Current Liability Accounts Payable 16210 @ ) Assume that PG has a non-flexible dividend policy . That is , PG pays $8 cash dividend per share each year , regardless of its net income . PG has 1000 M stock shares outstanding . Calculate stockholders" equity for PG in 2013 . Assume that PG will not issue or repurchase stocks in 2013 . Use the following equation to calculate stockholders' equity ( SE ) :` SEE = SEE - 1 + Net Incomet - Dividends + + Stock Issuance+ - Stock Repurchases Based on parts ( a ) through ( e ) construct a pro-forma balance sheet for 2013 . Assume that goodwill and intangibles and other long-term assets remain unchanged from 2012 . Assume also that PG will have no short - term debt outstanding in 2013 . If total assets are greater than total liabilities plus equity , correct this imbalance by adding to long-term debt . If total assets are less than total liabilities plus equity , correct this imbalance by retiring long-term debt . Pro - forma Balance Sheet ( Asset Side ) 2012 2013 partial 2013 Cash ( = Operating Cash + Excess Cash ) 4440 Accounts Receivables 7070 Inventory 6700 Current Assets 21910 Net Property , Plant , Equipment 20400 Goodwill and Intangibles* 8:4640 Other Long- Term Assets 8900 Long - Term Assets Total Assets 132150 Pro - forma Balance Sheet ( L + E ) 2012 2013 partial 2013 Accounts Payable* 16210 Short- Term Debt 8700 Current Liabilities 24910 Long- Term Debt 43800 Long - Term Liabilities 43800 Total Liabilities 58710 Total Stockholders " Equity 53440 Total L + E 132150 EFNs) The interest rate is4% on PG's shortterm debt and 5.5% on PG's longtem'I debt. Based on the shortterm and long-term debt projections for 2013 from part (f), calculate PG's interest payments in 2014. Use the following equation to calculate interest payments: Interest; = Interest Rateghm )