$4.$ Chapter $10$ Forecasting Financial Statements $$ Use the Chapter $10$ financial statement exhibits and design a spreadsheet and prepare a set of financial statement forecasts for Walmart for Year $+1$ to Year $+5$ using the assumptions the following assumptions. Project the amounts in the order presented $($unless indicated otherwise$)$ beginning with the income statement, then the balance sheet, and then the statement of cash flows. Assume that Walmart will exercise its financial flexibility with the cash and cash equivalents account to balance the balance sheet.

a$.$ Income statement forecast assumptions $$ Use Exhibit $10\mathrm{.}11.$

i$.$ Sales $$ Assume that the elevated demand for Walmart products during the pandemic in fiscal $2020$ will return to normal levels, so sales will grow $2\mathrm{.}0\%$ each year from Year $+1$ to Year $+5.$ For a company with the scale of Walmart, achieving even a $2\mathrm{.}0\%$ rate of sales growth will require adding over $$10$ billion per year to top line sales.

ii$.$ Cost of Goods Sold $$ Assume that cost of goods sold to sales percentage will continue to be $75\mathrm{.}2\%$ of sales for Year $+1$ to Year $+5.$

iii. Operating, Selling, General, and Administrative Expenses $$ Assume that operating, selling, general, and administrative expenses will continue to average $20\mathrm{.}8\%$ of sales for Year $+1$ to Year $+5.$

iv$.$ Interest Income $$ Assume that Walmart will earn interest income based on a $1\mathrm{.}5\%$ interest rate on average cash balances $($that is$,$ the sum of beginning and end$-$of$-$year cash balances divided by $2)$ for Year $+1$ to Year $+5.($Note: Projecting the amount of interest income must await projection of cash on the balance sheet. Since retained earnings needs net income and net income needs interest income, use $$335$ million for Year $+1,$ $$441$ million for Year $+2,$ $$524$ million for Year $+3,$ $$621$ million for Year $+4,$ and $$732$ million for Year $+5,$ as the interest income numbers, which ends up being about $1\mathrm{.}5\%$ of average cash balance.$)$

v$.$ Interest Expense on Debt $$ Assume a $5\mathrm{.}5\%$ interest rate for all outstanding borrowing $($short$-$term and long$-$term debt, including current portion of long$-$term debt$)$ for Walmart for Year $+1$ to Year $+5.$ Compute interest expense on the average amount of interest$-$bearing debt of outstanding each year. $($Note: Projecting the amount of interest expense must await projection of the interest$-$bearing debt accounts on the balance sheet.$)$

vi$.$ Interest and Financing Costs on Leases $-$ Assume the $6\mathrm{.}1\%$ interest rate will continue to apply for all outstanding operating lease obligations and the $6\mathrm{.}8\%$ interest rate will continue to apply for all outstanding finance lease obligations $($current and long$-$term leases$)$ for Walmart for Year $+1$ to Year $+5.$ Compute interest expense on the average amount of operating and financing lease obligations outstanding each year. $($Note: Projecting the amount of interest expense must await projection of the lease obligations on the balance sheet.$)$

vii. Income Tax Expense $$ Assume that Walmart$$s effective income tax rate will be $31\mathrm{.}0\%$ of income before taxes for Year $+1$ to Year $+5.($Note: Projecting the amount of income tax expense must await computation of income before taxes.$)$

viii. Net Income Attributable to Noncontrolling interests $$ Assume that the portion of net income attributable to noncontrolling interests in the future will continue to yield a $5\mathrm{.}0\%$ rate of return in Year $+1$ to Year $+5.$

b$.$ Balance Sheet forecast assumptions $$ Use Exhibit $10\mathrm{.}10.$

i$.$ Cash $$ Adjust cash as the flexible financial account to equate total assets with total liabilities plus shareholders$$ equity. Projecting the amount of cash must await projections of all other balance sheet amounts.

ii$.$ Accounts Receivable $$ Assume that accounts receivable will continue to turn over at the same rate and the ending accounts receivable balance will grow the sales growth.

iii. Inventories $$ Assume that ending inventory will equal to $40$ days of cost of good sold, in Year $+1$ to Year $+5.$

iv$.$ Prepaid Expenses and Other Current Assets $$ These assets include prepayments for ongoing operating costs, such as rent and insurance. Assume that prepayments will grow at the growth rate in sales in Year $+2$ through Year $+5.$ However, at fiscal year end $2020,$ Walmart includes $$19,200$ million in this account for assets associated with a business being held for sale. Assume that the sale of the business will be completed in Year $+1.$ Therefore, subtract $$19,200$ million from the ending balance in $2020$ to project the ending balance for Year $+1.$

v$.$ Property, Plant and Equipment at Cost $$ Assume that capital spending on new PP&E will continue to be $$10\mathrm{.}0$ billion each year for Year $+1$ to Year $+5.$

vi$.$ Accumulated Depreciation $$ In $2019$ and $2020,$ Walmart depreciated PP&E using an average useful life of approximately $17\mathrm{.}0$ years. For Year $+1$ through Year $+5,$ assume that accumulated depreciation will increase each year by depreciation expenses. For simplicity, compute straight$-$line depreciation based on an average $17-$year useful life and zero salvage value. In co