Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
Complete the projections for years 1 to 5 using the assumption below. Display formulas used. COMPREHENSIVE INCOME STATEMENT 2012 Year+1 Year +2 Year +3 Year
Complete the projections for years 1 to 5 using the assumption below. Display formulas used.
COMPREHENSIVE INCOME STATEMENT 2012 Year+1 Year +2 Year +3 Year +4 Year +5 Revenues COGS Gross Profit SG&A Expense Operating Income Interest Income Interest Expense Income Before Tax Income Tax Expense Net Income NI Attributable to NCI NI Attributable to CS OCI Comprehensive Income 469,162 (352,488) 116,674 (88,873) 27,801 187 (2,251) 25,737 (7,981) 17,756 (757) 16,999 823 17,822 (809) (809) (809) (809) (809) 2012 7,781 6,768 43,803 1,588 59,940 171,724 (55,043) 20,497 5,987 203,105 Year +1 Year +2 Year +3 Year +4 Year +5 17,830 29,834 43,05857,689 73,769 Cash & CE Accounts Receivables (net) Inventory Prepaid Expenses Current Assets PPE (cost) Acc. Depreciation Goodwill Other Assets Total Assets Accounts Payable Accrued Expenses Notes Payable/ST Debt LT Debt Current IT Payable Current Liabilities LT Debt Long-Ternm Deferred Taxes -LT Redeemable NCI Total Liabilities 38,080 18,808 6,805 5,914 2,211 71,818 41,417 7,613 519 121,367 Common Stock & APIC Retained Earnings Acc. Other Comprehensive Income Total Shareholder's Equity Noncontrolling Interests Total Equity Total Liabilities and Equity 3,952 72,978 (587) 76,343 5,3955,3955,395 5,395 5,395 5,395 81,738 203,105 Income Statement Forecast Assumptions Sales Sales grew by 3.4% in 2010, 6.0% in 2011, and 50% in 2012. The compound annual sales growth rate during the last three years was 5.5%. Walmart generates sales growth primarily through increasing same-store sales, opening new stores, and acquiring other retailers. In the future, Walmart will continue to grow in international markets by opening stores and acquiring other firms and in domestic U.S. markets by converting discount stores to Supercenters. In addi- tion, despite vigorous competition, Walmart will likely continue to generate steady increases in same-store sales, consistent with its experience through 2012. Assume that sales will grow 4.0% each year from Year +1 through Year +5 Cost of Goods Sold The percentage of costs of goods sold relative to sales increased slightly from 74.7% of sales in 2010 to 75.0% in 2011 to 75.1% in 2012, Walmart's everyday low-price strategy, its movement into grocery products, and competition will likely prevent Walmart from achieving significant reductions in this expense percentage. Assume that the cost of goods sold to sales percentage will be 75.0% for Year +1 to Year +5. The selling and administrative expenses percentage has declined slightly from 19.3% of sales in 2010 to 19.1% in 2011 to 18.9% in 2012. Walmart has exhibited strong cost control over the years and is likely to continue to exhibits such control. Assume that the selling and administrative expenses will continue to average 19% for sales for year +1 to years +5. Interest Income Walmart earns interest income on its cash and cash equivalents accounts The average interest rate earned on average cash balances was approximately 2.6% during 2012, similar to rates earned in 2010 and 2011. Assume that Walmart will earn interest income based on a 2.6% interest rate on average cash balances (that is, the sum of beginning and end- of-year cash balances divided by 2) for Year +1 through Year +5. (Note: Projecting the amount of interest income must await projection of cash on the balance sheet.) Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short- and long-term debt to finance corporate acquisitions. The aveage interest rate on all interest-bearing debt and capital leases was approximately 42% during 2011 and 2012 Assume a 42% interest rate for all outstanding borrowing short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Waimartbeari +1 through Year +5. Compute interest expense on the average amount of debt outstanding each year. (Note: Projecting the amount of interest expense m jection of the interest-bearing debt accounts on the balance sheet.) n of long-term debt) for Walmart for nt of interet-beari ust await pro- Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 32% during the last two years. Assume that Walmart's effective income tax rate remains a constant 32.0% of income before taxes for Year +1 through Year +5. (Note Projecting the amount of income tax expense must await computation of income before taxes Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholdes in Walmart subsidiaries were entitled to a $757 million share in Walmart's 2012 net income which amounted to roughly a 15% rate of return on investment. Assume that the portion ofs income attributable to noncontrolling interests in the future will continue to amount to a lbe rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total libl! ities plus shareholders' equity. Projecting the amount of cash must await projections of all thfer balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for thir party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores but the total amount of receivables outstanding on these credit cards is relatively minor com pared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steacy raging roughly five days during each of the past two years. Assume that accounits receivable will continue to turnover at the same rate and increase at the growth rate in sal les Inventories Walmart's overall inventory effciency has declined slightly in the past two ye in part because of the distribution of merchandise to stores worldwide. Inventory have decreased from an average of 42 days in 2011 to 44 days in 2012. Assume that endi turnovers in- ventory will continue to be equal to 44 days of cost of goods sold, in YeartoYar Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's balance sheets in 2010 and prior recognize amounts as current assets and current liabilities that are associated with discontinued segments (subsidiaries that Walmart is divesting). These operations were divested in 2011, so assume that these amounts will be zero in Year +1 through Year +5. Property, Plant, and Equipment-At Cost Property, plant, and equipment (including assets held under capital leases) grew by roughly $12.5 billion per year in 2010 through 2012 (capital expenditures net of proceeds from selling property, plant, and equipment). The construction of new Supercenters and the acquisition of established retail chains abroad will require additional investments in property, plant, and equipment. Assume that property, plant, and equipment will continue to grow by $12.5 billion each year from Year +1 through Year +5 Accumulated Depreciation In 2011 and 2012, Walmart depreciated property, plant, and equipment using an average useful life of approximately 19.8 years. For Year +1 through Year +5, assume that accumulated depreciation will increase each year by depreciation expense. For simplicity, compute straight-line depreciation expense based on an average 20-year useful life and zero salvage value. In computing depreciation expense each year, make sure you depreci- ate the beginning balance in the existing property, plant, and equipment- at cost. Also add a w Iayer of depreciation expense for the new property, plant, and equipment acquir ugh capital expenditures. Assume that Walmart recognizes a full year of depreciation on property, plant, and equipment in the first year of service new new Goodwill and Other Assets Goodwill and other assets include primarily goodwill arising from corporate acquisitions outside the United States. Such acquisitions increase Walmart sales. Assume that goodwill and other assets will grow at the same rate as revenues. Also assume that goodwill and other assets are not amortizable. Accounts Payable Walmart has maintained a steady accounts payable turnover, with payment periods averaging 9.5 times per year (an average turnover of roughly 38 days) during the last three years. Assume that ending accounts payable will continue to approximate 38 days of in ventory purchases in Years +1 to +5. To compute the ending accounts payable balance using a 38-day turnover period, remember to add the change in inventory to the cost of goods sold to obtain the total amount of credit purchases of inventory during the year. Accrued Liabilities Accrued liabilities relate to accrued expenses for ongoing operating activ- ties and are expected to grow at the growth rate in selling and administrative expenses, which are expected to grow with sales. Income Taxes Payable and Deferred Tax Liabilities-Noncurrent For simplicity, assume hat income taxes payable and deferred tax liabilities-noncurrent grow at 30% per year in Year +1 through Year +5 indemable Noncontrolling Interests Redeemable noncontrolling interests amount to data ents made by third-party investors in subsidiaries that Walmart controls and consoli- dates hese noncontrolling interests are redeemable, Walmart can redeem (pay off and terests. For simplicity, assume Walmart redeems and retires these redeemable S, B noncontrolling interests in Year +1. Short-Term Debt, Current Maturities of Long-Term Debt, Capital Leases, and Long Term Debt Walmart uses short-term debt, current maturities of long-term debt, capital leases 9- and long-term debt to augment cash from operations to finance capital expenditures on prop- erty, plant, and equipment and acquisitions of existing retail chains outside the United States. Over the past three years, each individual amount of debt financing (short-term debt, current maturities of long-term debt, and long-term debt) have fluctuated considerably from year to year, whereas the aggregate amount of debt financing has remained fairly steady, averaging roughly 27.0% of total assets. For simplicity, assume that the total amount of short-term debt, current maturities of long-term debt, and long-term debt will continue to remain a fairly steady percentage of total assets for Year +1 through Year +5. Assume that Walmart's short-term debt, current maturities of long-term debt, and long-term debt will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. Common Stock and Additional Paid-in Capital Over the past three years, Walmart's com- mon stock and additional paid-in capital have remained a fairly steady 2.0% of total assets (Walmart repurchases company shares on the open market and then reissues these shares to employees and executives to satisfy stock option exercises). Assume that common stock and additional paid-in capital will continue to be 2.0% of total assets for Year +1 through Y Retained Earnings The increase in retained earnings equals net income minus dividends. In 2012, Walmart paid total dividends of $5,361 million to common shareholders, which amounted to roughly 30% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 30% of net income attributable to Walmart shareholders in Year +1 through Year +5 Accumulated Other Comprehensive Income Assume that accumulated other comprehensive income will not change. Equivalently, assume that future other comprehensive income items will be zero, on average, in Year +1 through Year +5 Noncontrolling Interests Noncontrolling interests amount to equity investments madeby third-party investors in subsidiaries that Walmart controls and consolidates. Noncontrolling inter ests grow each year by their proportionate share of the subsidiary's income, and these interests decrease by any dividends paid to them. We assumed for purposes of the income statement, that net income attributable to noncontrolling interests would generate a 15% rate of return for those investors. For simplicity, assume Walmart's noncontrolling interests will grow by the amount of income attributable to these noncontrolling interests each year, and dividends paid to them wll be the same amount in Year +1 to Year +5. Theref equity will remain constant. ore, the amount of noncontrolling interests in Cash At this point, you can project the amount of cash on Walmart's balance sheet at each year- end from Year +1 to Year +5. Assume that Walmart uses cash as the flexible financial account to balance the balance sheet. The resulting cash balance each year should be the total amount of liabi ities and shareholders' equity minus the projected ending balances in all non-cash asset accounts. COMPREHENSIVE INCOME STATEMENT 2012 Year+1 Year +2 Year +3 Year +4 Year +5 Revenues COGS Gross Profit SG&A Expense Operating Income Interest Income Interest Expense Income Before Tax Income Tax Expense Net Income NI Attributable to NCI NI Attributable to CS OCI Comprehensive Income 469,162 (352,488) 116,674 (88,873) 27,801 187 (2,251) 25,737 (7,981) 17,756 (757) 16,999 823 17,822 (809) (809) (809) (809) (809) 2012 7,781 6,768 43,803 1,588 59,940 171,724 (55,043) 20,497 5,987 203,105 Year +1 Year +2 Year +3 Year +4 Year +5 17,830 29,834 43,05857,689 73,769 Cash & CE Accounts Receivables (net) Inventory Prepaid Expenses Current Assets PPE (cost) Acc. Depreciation Goodwill Other Assets Total Assets Accounts Payable Accrued Expenses Notes Payable/ST Debt LT Debt Current IT Payable Current Liabilities LT Debt Long-Ternm Deferred Taxes -LT Redeemable NCI Total Liabilities 38,080 18,808 6,805 5,914 2,211 71,818 41,417 7,613 519 121,367 Common Stock & APIC Retained Earnings Acc. Other Comprehensive Income Total Shareholder's Equity Noncontrolling Interests Total Equity Total Liabilities and Equity 3,952 72,978 (587) 76,343 5,3955,3955,395 5,395 5,395 5,395 81,738 203,105 Income Statement Forecast Assumptions Sales Sales grew by 3.4% in 2010, 6.0% in 2011, and 50% in 2012. The compound annual sales growth rate during the last three years was 5.5%. Walmart generates sales growth primarily through increasing same-store sales, opening new stores, and acquiring other retailers. In the future, Walmart will continue to grow in international markets by opening stores and acquiring other firms and in domestic U.S. markets by converting discount stores to Supercenters. In addi- tion, despite vigorous competition, Walmart will likely continue to generate steady increases in same-store sales, consistent with its experience through 2012. Assume that sales will grow 4.0% each year from Year +1 through Year +5 Cost of Goods Sold The percentage of costs of goods sold relative to sales increased slightly from 74.7% of sales in 2010 to 75.0% in 2011 to 75.1% in 2012, Walmart's everyday low-price strategy, its movement into grocery products, and competition will likely prevent Walmart from achieving significant reductions in this expense percentage. Assume that the cost of goods sold to sales percentage will be 75.0% for Year +1 to Year +5. The selling and administrative expenses percentage has declined slightly from 19.3% of sales in 2010 to 19.1% in 2011 to 18.9% in 2012. Walmart has exhibited strong cost control over the years and is likely to continue to exhibits such control. Assume that the selling and administrative expenses will continue to average 19% for sales for year +1 to years +5. Interest Income Walmart earns interest income on its cash and cash equivalents accounts The average interest rate earned on average cash balances was approximately 2.6% during 2012, similar to rates earned in 2010 and 2011. Assume that Walmart will earn interest income based on a 2.6% interest rate on average cash balances (that is, the sum of beginning and end- of-year cash balances divided by 2) for Year +1 through Year +5. (Note: Projecting the amount of interest income must await projection of cash on the balance sheet.) Interest Expense Walmart uses long-term mortgages and capital leases to finance new stores and warehouses and short- and long-term debt to finance corporate acquisitions. The aveage interest rate on all interest-bearing debt and capital leases was approximately 42% during 2011 and 2012 Assume a 42% interest rate for all outstanding borrowing short-term and long-term debt, including capital leases, and the current portion of long-term debt) for Waimartbeari +1 through Year +5. Compute interest expense on the average amount of debt outstanding each year. (Note: Projecting the amount of interest expense m jection of the interest-bearing debt accounts on the balance sheet.) n of long-term debt) for Walmart for nt of interet-beari ust await pro- Income Tax Expense Walmart's average income tax rate as a percentage of income before taxes has been roughly 32% during the last two years. Assume that Walmart's effective income tax rate remains a constant 32.0% of income before taxes for Year +1 through Year +5. (Note Projecting the amount of income tax expense must await computation of income before taxes Net Income Attributable to Noncontrolling Interests Noncontrolling interest shareholdes in Walmart subsidiaries were entitled to a $757 million share in Walmart's 2012 net income which amounted to roughly a 15% rate of return on investment. Assume that the portion ofs income attributable to noncontrolling interests in the future will continue to amount to a lbe rate of return in Year +1 through Year +5. Balance Sheet Forecast Assumptions Cash We will adjust cash as the flexible financial account to equate total assets with total libl! ities plus shareholders' equity. Projecting the amount of cash must await projections of all thfer balance sheet amounts. Accounts Receivable As a retailer, a large portion of Walmart's sales are in cash or for thir party credit card charges, which Walmart can convert into cash within a day or two. Walmart has its own credit card that customers can use for purchases at its Sam's Club warehouse stores but the total amount of receivables outstanding on these credit cards is relatively minor com pared to Walmart's total sales. As a consequence, Walmart's receivables turnover is very steacy raging roughly five days during each of the past two years. Assume that accounits receivable will continue to turnover at the same rate and increase at the growth rate in sal les Inventories Walmart's overall inventory effciency has declined slightly in the past two ye in part because of the distribution of merchandise to stores worldwide. Inventory have decreased from an average of 42 days in 2011 to 44 days in 2012. Assume that endi turnovers in- ventory will continue to be equal to 44 days of cost of goods sold, in YeartoYar Prepaid Expenses Current assets include prepayments for ongoing operating costs such as rent and insurance. Assume that prepayments will grow at the growth rate in sales. Current Assets and Liabilities of Discontinued Segments Walmart's balance sheets in 2010 and prior recognize amounts as current assets and current liabilities that are associated with discontinued segments (subsidiaries that Walmart is divesting). These operations were divested in 2011, so assume that these amounts will be zero in Year +1 through Year +5. Property, Plant, and Equipment-At Cost Property, plant, and equipment (including assets held under capital leases) grew by roughly $12.5 billion per year in 2010 through 2012 (capital expenditures net of proceeds from selling property, plant, and equipment). The construction of new Supercenters and the acquisition of established retail chains abroad will require additional investments in property, plant, and equipment. Assume that property, plant, and equipment will continue to grow by $12.5 billion each year from Year +1 through Year +5 Accumulated Depreciation In 2011 and 2012, Walmart depreciated property, plant, and equipment using an average useful life of approximately 19.8 years. For Year +1 through Year +5, assume that accumulated depreciation will increase each year by depreciation expense. For simplicity, compute straight-line depreciation expense based on an average 20-year useful life and zero salvage value. In computing depreciation expense each year, make sure you depreci- ate the beginning balance in the existing property, plant, and equipment- at cost. Also add a w Iayer of depreciation expense for the new property, plant, and equipment acquir ugh capital expenditures. Assume that Walmart recognizes a full year of depreciation on property, plant, and equipment in the first year of service new new Goodwill and Other Assets Goodwill and other assets include primarily goodwill arising from corporate acquisitions outside the United States. Such acquisitions increase Walmart sales. Assume that goodwill and other assets will grow at the same rate as revenues. Also assume that goodwill and other assets are not amortizable. Accounts Payable Walmart has maintained a steady accounts payable turnover, with payment periods averaging 9.5 times per year (an average turnover of roughly 38 days) during the last three years. Assume that ending accounts payable will continue to approximate 38 days of in ventory purchases in Years +1 to +5. To compute the ending accounts payable balance using a 38-day turnover period, remember to add the change in inventory to the cost of goods sold to obtain the total amount of credit purchases of inventory during the year. Accrued Liabilities Accrued liabilities relate to accrued expenses for ongoing operating activ- ties and are expected to grow at the growth rate in selling and administrative expenses, which are expected to grow with sales. Income Taxes Payable and Deferred Tax Liabilities-Noncurrent For simplicity, assume hat income taxes payable and deferred tax liabilities-noncurrent grow at 30% per year in Year +1 through Year +5 indemable Noncontrolling Interests Redeemable noncontrolling interests amount to data ents made by third-party investors in subsidiaries that Walmart controls and consoli- dates hese noncontrolling interests are redeemable, Walmart can redeem (pay off and terests. For simplicity, assume Walmart redeems and retires these redeemable S, B noncontrolling interests in Year +1. Short-Term Debt, Current Maturities of Long-Term Debt, Capital Leases, and Long Term Debt Walmart uses short-term debt, current maturities of long-term debt, capital leases 9- and long-term debt to augment cash from operations to finance capital expenditures on prop- erty, plant, and equipment and acquisitions of existing retail chains outside the United States. Over the past three years, each individual amount of debt financing (short-term debt, current maturities of long-term debt, and long-term debt) have fluctuated considerably from year to year, whereas the aggregate amount of debt financing has remained fairly steady, averaging roughly 27.0% of total assets. For simplicity, assume that the total amount of short-term debt, current maturities of long-term debt, and long-term debt will continue to remain a fairly steady percentage of total assets for Year +1 through Year +5. Assume that Walmart's short-term debt, current maturities of long-term debt, and long-term debt will grow at 3.0% per year in Year +1 through Year +5, roughly consistent with the projected growth in total assets. Common Stock and Additional Paid-in Capital Over the past three years, Walmart's com- mon stock and additional paid-in capital have remained a fairly steady 2.0% of total assets (Walmart repurchases company shares on the open market and then reissues these shares to employees and executives to satisfy stock option exercises). Assume that common stock and additional paid-in capital will continue to be 2.0% of total assets for Year +1 through Y Retained Earnings The increase in retained earnings equals net income minus dividends. In 2012, Walmart paid total dividends of $5,361 million to common shareholders, which amounted to roughly 30% of net income attributable to Walmart shareholders. Assume that Walmart will maintain a policy to pay dividends equivalent to 30% of net income attributable to Walmart shareholders in Year +1 through Year +5 Accumulated Other Comprehensive Income Assume that accumulated other comprehensive income will not change. Equivalently, assume that future other comprehensive income items will be zero, on average, in Year +1 through Year +5 Noncontrolling Interests Noncontrolling interests amount to equity investments madeby third-party investors in subsidiaries that Walmart controls and consolidates. Noncontrolling inter ests grow each year by their proportionate share of the subsidiary's income, and these interests decrease by any dividends paid to them. We assumed for purposes of the income statement, that net income attributable to noncontrolling interests would generate a 15% rate of return for those investors. For simplicity, assume Walmart's noncontrolling interests will grow by the amount of income attributable to these noncontrolling interests each year, and dividends paid to them wll be the same amount in Year +1 to Year +5. Theref equity will remain constant. ore, the amount of noncontrolling interests in Cash At this point, you can project the amount of cash on Walmart's balance sheet at each year- end from Year +1 to Year +5. Assume that Walmart uses cash as the flexible financial account to balance the balance sheet. The resulting cash balance each year should be the total amount of liabi ities and shareholders' equity minus the projected ending balances in all non-cash asset accounts
Complete the projections for years 1 to 5 using the assumption below. Display formulas used.
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6866268_047669ecc67e011f.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6937d10_048669ecc6894892.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6a1687e_049669ecc6974c5c.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6aeb3e1_050669ecc6a557cb.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6bd6709_051669ecc6b49e51.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6d25b8a_052669ecc6c6a631.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6e0f98d_053669ecc6d6a511.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc6eeff09_054669ecc6e55b82.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc7012648_055669ecc6f5d0fb.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc71096f1_056669ecc7057513.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc7204048_057669ecc71505c7.jpg)
![image text in transcribed](https://s3.amazonaws.com/si.experts.images/answers/2024/07/669ecc72e30c0_058669ecc7252955.jpg)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started