Help only Forecast the Balance Sheet in Excel!
AUTOMATIC DATA PROCESSING INC. Balance Sheet \$ millions June 30,2019 Current assets Cash and cash equivalents. Accounts receivable, net. Other current assets . Total current assets before funds held for clients Funds held for clients Total current assets Long-term receivables, net. Property, plant and equipment, net Capitalized contract cost, net. Other assets. Goodwill . Intangible assets, net $1,949.22,439.3519.64,908.129,434.234,342.323.8764.22,428.5934.42,323.01,071.5$41,887.7 Current liabilities Accounts payable ......... Accrued expenses and other current liabilities Accrued payroll and payroll-related expenses Dividends payable. Short-term deferred revenues. Obligations under reverse repurchase agreements Income taxes payable. Total current liabilities before client funds obligations. Client funds obligations. Total current liabilities. Long-term debt. Other liabilities Deferred income taxes. Long-term deferred revenues. Total liabilities. 125.51,759.0721.1340.1220.7262.054.83,483.229,144.532,627.72,002.2798.7659.9399.336,487.8 \$ millions June 30,2019 Shareholders' equity Preferred stock, $1.00 par value: Authorized, 0.3 shares; issued, none Common stock, $0.10 par value: Authorized, 1,000.0 shares; issued, 638.7 shares; outstanding 434.2 shares Capital in excess of par value. Retained earnings . 63.91,183.217,500.6 Treasury stock, at cost: 204.5 shares. Accumulated other comprehensive loss. Total stockholders' equity. Total liabilities and stockholders' equity a. Forecast ADP's 2020 income statement and balance sheet using the following relations (\$ millions). Assume total revenues grow by 13\% in 2020. All other percentages (other than sales growth and provision for income taxes) are based on historic percent of total revenues. - CAPEX for 2020 will be 1.1% of total revenue, and depreciation will be $184.4 million. - Goodwill, long-term debt, preferred stock, common stock, and Accumulated other comprehensive loss will not change for the year. - The company will acquire intangibles equal to 2.9% of total revenues and will record amortization expense of $276.1 million. - Income taxes will be 25% of pretax income and income taxes payable will be 7.7% of 2020 tax expense. - The company will award $167.3 million of stock-based compensation, which increases Capital in excess of par value by the same amount. Assume that the company routinely includes this form of compensation in operating expenses each year. - The company will continue its stock repurchases. ADP will repurchase $750 million of treasury stock. - Dividends will be $1,389.4 in 2020 , and dividends payable will be 26.3% of dividends. b. What does the forecasted adjustment to balance the accounting equation from part a reveal to us about AUTOMATIC DATA PROCESSING INC. Balance Sheet \$ millions June 30,2019 Current assets Cash and cash equivalents. Accounts receivable, net. Other current assets . Total current assets before funds held for clients Funds held for clients Total current assets Long-term receivables, net. Property, plant and equipment, net Capitalized contract cost, net. Other assets. Goodwill . Intangible assets, net $1,949.22,439.3519.64,908.129,434.234,342.323.8764.22,428.5934.42,323.01,071.5$41,887.7 Current liabilities Accounts payable ......... Accrued expenses and other current liabilities Accrued payroll and payroll-related expenses Dividends payable. Short-term deferred revenues. Obligations under reverse repurchase agreements Income taxes payable. Total current liabilities before client funds obligations. Client funds obligations. Total current liabilities. Long-term debt. Other liabilities Deferred income taxes. Long-term deferred revenues. Total liabilities. 125.51,759.0721.1340.1220.7262.054.83,483.229,144.532,627.72,002.2798.7659.9399.336,487.8 \$ millions June 30,2019 Shareholders' equity Preferred stock, $1.00 par value: Authorized, 0.3 shares; issued, none Common stock, $0.10 par value: Authorized, 1,000.0 shares; issued, 638.7 shares; outstanding 434.2 shares Capital in excess of par value. Retained earnings . 63.91,183.217,500.6 Treasury stock, at cost: 204.5 shares. Accumulated other comprehensive loss. Total stockholders' equity. Total liabilities and stockholders' equity a. Forecast ADP's 2020 income statement and balance sheet using the following relations (\$ millions). Assume total revenues grow by 13\% in 2020. All other percentages (other than sales growth and provision for income taxes) are based on historic percent of total revenues. - CAPEX for 2020 will be 1.1% of total revenue, and depreciation will be $184.4 million. - Goodwill, long-term debt, preferred stock, common stock, and Accumulated other comprehensive loss will not change for the year. - The company will acquire intangibles equal to 2.9% of total revenues and will record amortization expense of $276.1 million. - Income taxes will be 25% of pretax income and income taxes payable will be 7.7% of 2020 tax expense. - The company will award $167.3 million of stock-based compensation, which increases Capital in excess of par value by the same amount. Assume that the company routinely includes this form of compensation in operating expenses each year. - The company will continue its stock repurchases. ADP will repurchase $750 million of treasury stock. - Dividends will be $1,389.4 in 2020 , and dividends payable will be 26.3% of dividends. b. What does the forecasted adjustment to balance the accounting equation from part a reveal to us about
