Hello,

I would like some help in projected income statement and balance sheet in excel for Walmart company. I have started but i need help in figuring out the rest.

Historical and Projected Income Statement Fiscal Years 2011-2020 Year Ending January 1st Projected Actual Figures in Millions of (CURRENCY) 2020 2019 2018 2017 2016 2015 2014 2013 2012 2011 $418,952 Revenue Net sales $478,614 $482,229 $473,076 $466,114 $443,854 Cost of sales 360,984 365,086 358,069 352,488 335,127 315,287 Gross Profit $117,630 $117,143 $115,007 $113,626 $108,727 $103,665 Membership and other income 3516 $3,422 $3,218 $3,048 $3,096 $2,897 -97041 -93,418 -91,353 -88,873 -85,265 -81,020 $24,105 $27,147 $26,872 $27,801 $26,558 $25,542 -2,461 -2,335 -2,251 -2,322 -2,205 113 119 187 162 201 Interest, net ($2,348) ($2,216) ($2,064) ($2,160) ($2,004) Income from continuing opeations before income taxes $24,799 $24,656 $25,737 $24,398 $23,538 -7985 -8105 -7981 -7944 -7579 $16,814 $16,551 $17,756 $16,454 $15,959 Operating, selling, general and administrative expenses Operating income Interest expense, debt, capital lease & financing obligations Interest Income Provision for income taxes Income from continuing operations Income (loss) from discontinued operations, net of income taxes Net Income Net income attributable to noncontrolling interest 285 144 $0 -67 1,034 $17,099 $16,695 $17,756 $16,387 $16,993 -736 -673 -757 -688 -604 $16,363 $16,022 $16,999 $15,699 $16,389 Basic Income 5.01 4.87 5.04 4.56 4.2 Diluted Income 4.99 4.85 5.02 4.54 4.18 Dividends declared 1.92 1.88 1.59 1.46 1.21 Net Income attributable to Walmart Earnings per share attributable to Walmart Projected Actual 2020 2019 2018 2017 2016 2015 2014 2013 2012 2011 8,705 $ 9,135 $ 7,281 $ 7,781 $ 6,550 $ 7,395 Assets Cash $ Accounts Receivable Inventory Total Current Assets $ PP&E Less Depreciation Net Plant and Equipment $ Other assets Total Assets Liabilities Accounts Payable Capital Leases Accrued Expenses Total Current Liabilities Long Term Debt Additional Paid-in-capital Retained Earnings Total Liabilities and Net Worth $ 5624 44,469 58,798 176,958 -66,787 110,171 6,131 116,302 6778 45,141 61,054 $ 177,395 -63,115 114,280 5,455 119,735 6677 44,858 58,816 $ 173,089 -57,725 115,364 6,149 109,227 6768 43,803 58,352 $ 165,825 -51,896 113,929 5,987 119,916 5937 40,714 53,201 $ 155,002 -45,399 109,603 5,456 115,059 5089 36,318 48,802 148,584 -43,486 105,098 4,129 109,227 Income statement: From the five-year historical income statement, it is clear that there is a decrease in the sales growth. But on the other hand, Walmart focus on the e-commerce expansion and targeting to expand in the developing economies indicates that there would be a slow growth in the revenue on year on year basis. In this case, the average rate of sales growth is determined between 2012 and 2016 and being used. The growth rate is at 1.92% that is minimum and is achievable by Walmart through their expansion and growth plan in developing market and ecommerce. There is no indication of any dramatic change in the future expenses of the company as there is soaring sales growth Walmart will target to maintain the expenses at the average level of sales. Common-size income statement for the five-year period is determined, and their average is being used for projecting the remaining years as Walmart pays more attention to the net income. Balance sheet: Asset turnover ratio forms the fundamental for ascertaining the changes in the balance sheet and in forecasting them. In this case, five-year asset turnover ratio is determined to understand the relationship between the assets of the company and the sales behavior. From the analysis, it is clear that Walmart is maintaining a constantly higher level of asset turnover ratio, and it is always above 2.2 for all the years. Therefore, an average of the five years ratio is being used to project the remaining period total assets of the company. It is clear that there is a strong and consistent relationship between the total assets and sales of the company and it is expected to continue in the future as it is a major contributor for the return on equity. Walmart always focuses on improving and maintaining their return provided to their shareholders. Thus the asset turnover ratio will be maintained by the company. Commonsize balance sheet is determined between 2012 and 2016 and average is obtained. As Walmart is well established into the market and expected to maintain their financials at an average level due to higher competition and for maintaining their financial position, five-year average is used for projecting the financial statement. Income statement: From the five-year historical income statement, it is clear that there is a decrease in the sales growth. But on the other hand, Walmart focus on the e-commerce expansion and targeting to expand in the developing economies indicates that there would be a slow growth in the revenue on year on year basis. In this case, the average rate of sales growth is determined between 2012 and 2016 and being used. The growth rate is at 1.92% that is minimum and is achievable by Walmart through their expansion and growth plan in developing market and ecommerce. There is no indication of any dramatic change in the future expenses of the company as there is soaring sales growth Walmart will target to maintain the expenses at the average level of sales. Common-size income statement for the five-year period is determined, and their average is being used for projecting the remaining years as Walmart pays more attention to the net income. Balance sheet: Asset turnover ratio forms the fundamental for ascertaining the changes in the balance sheet and in forecasting them. In this case, five-year asset turnover ratio is determined to understand the relationship between the assets of the company and the sales behavior. From the analysis, it is clear that Walmart is maintaining a constantly higher level of asset turnover ratio, and it is always above 2.2 for all the years. Therefore, an average of the five years ratio is being used to project the remaining period total assets of the company. It is clear that there is a strong and consistent relationship between the total assets and sales of the company and it is expected to continue in the future as it is a major contributor for the return on equity. Walmart always focuses on improving and maintaining their return provided to their shareholders. Thus the asset turnover ratio will be maintained by the company. Commonsize balance sheet is determined between 2012 and 2016 and average is obtained. As Walmart is well established into the market and expected to maintain their financials at an average level due to higher competition and for maintaining their financial position, five-year average is used for projecting the financial statement