Need to hire help on attached assignment.
Overview: You are the new CIO of Walmart and have proposed an IT investment project to improve analytics to ensure inventory levels are well aligned with consumer demands. The project proposal entails investing in AI driven inventory analytics via a IaaS/SaaS inventory management solution that utilizes the latest virtualized infrastructure and AI technologies. Homework Assignment: Explain how your IT investment project idea will be funded, including a detailed discussion of the marginal cost of capital (MCC) that supports your project proposal, and based on the company’s “Financial Fact Sheet.” Include the following calculations in your explanation: • weighted average cost of capital (WACC) • average rate of return (ARR) • net present value (NPV) Specific criteria that MUST be followed: Using your chosen company’s Financial Fact Sheet, provide the following dollar amounts and percentages that are the basis for your IT investment project funding plan: 1. The percentage of total capital represented by common equity for the proposed project you create in the WACC section below. 2. The percentage of total capital represented by preferred stock for the proposed project you create in the WACC section below. Note that Walmart does not currently issue preferred stock. But, for purposes of this assignment, you are to assume that Walmart does issue preferred stock. 3. The percentage of total capital represented by debt for the proposed project you create in the WACC section below. 4. The dollar amount of common equity for the proposed project (multiply the percentage of total capital from common stock (PTCCS) from the WACC section below times the dollar amount of total capital for the proposed project (Initial cost of project from the Financial Fact Sheet). 5. The dollar amount of preferred stock for the proposed project (multiply the percentage of total capital from preferred stock (PTCPS) from the WACC section below times the dollar amount of total capital for the proposed project (Initial cost of project from the Financial Fact Sheet). Note that Walmart does not currently issue preferred stock. But, for purposes of this assignment, you are to assume that Walmart does issue preferred stock. 6. The dollar amount of debt for the proposed project (multiply the percentage of total capital from debt (PTCD) from the WACC section below times the dollar amount of total capital for the proposed project (Initial cost of project from the Financial Fact Sheet). 7. The dollar amount of total capital (Initial cost of project from the Financial Fact Sheet) for the proposed project, which is the sum of the common equity, preferred stock, and debt. 8. The tax rate of the company for your project (from the Financial Fact Sheet). Plan for Funding the IT Investment Project. Based on the data and percentages appearing in the previous section (taken from your created numbers and Financial Fact Sheet) explain in detail how your IT investment project idea will be funded. Note that the results of the calculations appearing in sections E.2 through E.6 will provide the sources of the data used in this section. Justification of the Plan for Funding the IT Investment Project. Provide a detailed narrative in which you Justify your funding plan using the results of the calculations appearing in the following four sections, stating any assumptions you make. Weighted Average Cost of Capital The formula for computing the WACC is: WACC = (PTCCS)(CCS) + (PTCPS)(CPS) + (PTCD)(CD)(1-TR) · where: PTCCS is the Percentage of Total Capital from Common Stock (created by you) · CCS is the Cost of Common Stock (from the Financial Fact Sheet) · PTCPS is the Percentage of Total Capital from Preferred Stock (created by you) · CPS is the Cost of Preferred Stock (from the Financial Fact Sheet) · PTCD is the Percentage of Total Capital from Debt (created by you) · CD is the Cost of Debt (from the Financial Fact Sheet) · TR is the firm’s Tax Rate (from the Financial Fact Sheet) · Note that Walmart does not currently issue preferred stock. But, for purposes of this assignment, you are to assume that Walmart does issue preferred stock. To compute the WACC: 1. Insert the values for the WACC formula variables CCS, CPS, CD, and TR into the WACC formula. These values are all found on the Financial Fact Sheet. 2. Create values for the three percentages for the WACC formula variables PTCCS, PTCPS, and PTCD. Note that the sum of the three values must be 100%. 3. Compute the WACC value. 4. If the WACC value equals 6%, 8%, 10%, or 12%, you’ve created an acceptable WACC value. If it is not one of these percentages, then adjust the values you created for the variables PTCCS, PTCPS, and PTCD and compute the WACC value again. Repeat this step until the WACC value equals 6%, 8%, 10%, or 12% Answer this question using your chosen company’s created numbers, Financial Fact Sheet, and the values for PTCCS, PTCPS, and PTCD created by you. In your answer, be sure to provide: (1) the formula for computing the Weighted Average Cost of Capital (WACC), (2) the numbers you used in the WACC formula for your project, and (3) the WACC value resulting from evaluating the WACC formula. Marginal Cost of Capital Answer this question using the WACC value from the previous section. Average Rate of Return Answer this question using your chosen company’s created numbers, Financial Fact Sheet, common methodologies, and the data for common equity, debt, and preferred stock in the earlier part of this section. Be sure to provide: (1) the formula for computing the Average Rate of Return (ARR), (2) the component numbers for the ARR formula for your project, and (3) the number resulting from evaluating the ARR formula. Net Present Value (NPV) Answer this question using your chosen company’s created numbers, Financial Fact Sheet, common methodologies, and the data for common equity, debt, and preferred stock in section E.1.1 above. Be sure to provide: (1) the formula for computing the Net Present Value (NPV), (2) the component numbers for the NPV formula for your project, and (3) the number resulting from evaluating the NPV formula. Financial Formula Sheet Average investment = initial cost + residual value 2 Average rate of return = average annual income average investment Current ratio = current assets current liabilities Days’ sales outstanding = accounts receivable annual sales × number of days in the period (for a quarter, use 120 days) Debt ratio = total liabilities total assets Dividends per share = dividends on common stock shares of common stock outstanding Earnings per share on common stock = net income − preferred dividends shares of common stock outstanding Future value = present value × (1 + r)n, where r is the rate of return and n is the number of years Inventory turnover ratio = cost of goods sold average inventory Market value = purchased price – selling price – selling expenses Net cash flow = net income + depreciation + amortization Net present value = expected cash inflows – amount to be invested Operating income = gross income - operating expenses – depreciation – amortization Present value factor = 1 (1+?)? , where r is rate of return and n is number of years Present value of an annuity = sum of present value factors × initial investment Price/earnings ratio = stock price earnings per share Profit margin ratio = net income net sales Quick ratio or acid test = cash + cash equivalents + short−term investments + current receivables current liabilities Ratio of fixed assets to long-term liabilities = fixed assets (net) long−term liabilities Ratio of free cash flow to sales = free cash flow sales Ratio of liabilities to stockholders’ equity = total liabilities total stockholders′ equity Return on total assets = net income + interest expense average total assets Return on common stockholders’ equity = net income − dividends average common stockholders′ equity Straight-line depreciation = purchase price − residual value expected life Times interest earned = income before income tax + interest expense interest expense Working capital = current assets – current liabilities You may need to compute the weighted average cost of capital (WACC) based on non-percentage inputs or based on percentage inputs. There are two formulas below. The first formula should be utilized when the inputs you are given are percentages. The second formula should be used when inputs are not percentages. WACC with percentages. All projects must be entirely funded (100%). Equity = e% Cost of equity = coe% Debt = d% Cost of debt = cod% Stock = s% Cost of stock = cos% WACC = (?% × ???%) + (?% × (???% × (1 − tax rate)) ) + (?% × ???%) Weighted average cost of capital (WACC) = ( equity total capital × cost of equity) + ( debt total capital × (cost of debt × (1 − tax rate))) Assessment Code: Task Title NWM3: IT Project Analysis and Proposal PAGE 1 Walmart Inc. Financial Fact Sheet Strategic Goals for Walmart Inc. (WMT) 1. Increase the average transaction amount per consumer visit to the store and website. 2. Improve analytics to ensure inventory levels are well aligned with consumer demands. 3. Reduce costs related to procuring and delivering the product to the consumer. Financial Facts NWM3: IT Project Analysis and Proposal Walmart Inc. Financial Fact Sheet PAGE 2 Present Value of $1 at Compound Interest Year 6% 8% 10% 12% Year 1 0.943 0.926 0.909 0.893 Year 2 0.89 0.857 0.826 0.797 Year 3 0.84 0.794 0.751 0.712 Year 4 0.792 0.735 0.683 0.636 Year 5 0.747 0.681 0.621 0.567 Year 6 0.705 0.630 0.564 0.507 Year 7 0.665 0.583 0.513 0.452 Year 8 0.627 0.540 0.467 0.404 Year 9 0.592 0.500 0.424 0.361 Year 10 0.558 0.463 0.386 0.322