Please do it in excel and show all your calculations
Following are the financial statements of CVS Caremark Corporation for the year ended December 3 Required: 1, 2016. Prepare a forecasted income statement and balance sheet for the company for the next year ending December 31, 2017 CVS CAREMARK CORPORATION Consolidated Balance Sheets Dec. 31, Dec. 31, In millions Cash and cash equivalents S 1,380 Short-term investments Accounts receivable, net 8,729 11,045 6,479 11,032 693 Deferred income taxes Other current assets Total current assets 25,325 8,615 20,161 8,632 26,395 9,753 Property and equipment, net Intangible assets, net Other assets Total assets 9,529 $5,070 3,974 Accounts payable Claims and discounts payable Accrued expenses Short-term debt Current maturities of long-term debt Total current liabilities Long-term debt Deferred income taxes Other long-term liabilities S 5,548 4,768 15,425 12,841 3,901 1,421 14,150 3,784 1,501 Common stock, par value $0.01 Treasury stock, at cost Capital surplus Retained earnings (20,200) 29,777 28,493 (16,301) 29,120 24,998 181 Accumulated other comprehensive loss Total shareholders' equity Total liabilities and shareholders' equity $71,526 Pobn t-23 8 CVS CAREMARK CORPORATION Consolidated Statementsorincome Dec. 31,Dec. 31, In millions Net revenues Cost of revenues Gross profit Total operating expenses Operating profit Interest expense, net Asset impairment expense Income before income tax provision Income tax provision Income from continuing operations Income (loss) from discontinued operations, net Net income -2016 $126,761 $123,120 102,978 23,783 746 22,488 8,037 509 7,210 557 7,528 928 6,305 436 3,869 4,600 592 To forecast the financial statements, make the following assumptions. For accounts that are not included in the list Growth in net revenues Gross profit margin percentage Operating expenses to net revenues Income tax provision to income before income tax provision Income (loss) from discontinued operations Cash and cash equivalents to net revenues A/R to net revenues Inventories to net revenues CAPEX to net revenues. Forecasted depreciation expense ($ millions) Forecasted amortization of intangible assets ($ millions). 18.8% 12.4% 38.9% -ocpiccation ne 6.9% 8.7% 1.6% $1,119 $271 $576 44% 23.9% Long-term debt due in current year (S millions), A/P to net revenues Dividends to net earnings Additional Information: The balance in property and equipment, net is increased by forecasted CAPEX and reduced by forecasted depreciation expense. The balance in intangible assets, net is reduced by forecasted amortization expense. The balance in long-term debt is reduced by the current maturities of long-term debt (long-term debt due in current year). The balance in retained earnings is increased by net income and reduced by dividends. Any imbalance in total assets or total liabilities and equity that remains after all forecast assumptions are applied, must be eliminated by increasing short-term investments or short-term debt, respectively
