Generate a BA solution report based on the “Supermarket Case” as…

/in /by

Question Answered step-by-step Generate a BA solution report based on the “Supermarket Case” as… Generate a BA solution report based on the “Supermarket Case” as following.”A busy supermarket has 5 POS (point-of-sale) terminals, which collect 2,000 customers’ data (10,000 transaction records) each day. All the sales records from the POS terminals are transmitted to a centralized server. The transaction records contain the data scanned from barcodes, seller’s data inputs and the POS software processed data (discounts, promotions, total sum, etc.) for each customer. The transaction records can be exported as xls/xlsx, txt and csv data formats and stored in the server’s backup storage units. ” Computer Science Engineering & Technology Networking ICT NIT3171 Share QuestionEmailCopy link Comments (0)

You might also like
Description Homework 8Unless stated otherwise, round your answers to two decimal points, and do not round intermediate calculations.Problem 1. [20 points]We are going to explore the difference between two different companies, both from the same sector, firms A and B. They differ in their reinvestment abilities. Firm A and Firm B are both financed with equity only.At the end of their business year, both firms report $1,000m in revenue. The net income is $100m for both firms. Assume that management is able to maintain a constant profit margin of 10%. Firm A is capable of achieving 5% revenue growth annually by investing 25% of their net income. Firm B is capable of achieving 5% revenue growth annually by investing 50% of their net income. Assume that this difference persists into the future. Find the value of firms A and B. Use a discount rate of 10% for both firms.The forward P/E ratio of a company is the price of a share divided by next year’s earnings per share, or its value divided by next year’s earnings. What is the forward P/E ratio of firms A and B? Comment on your result. Why is the forward P/E ratio higher for firm A? Or said differently: Why would shares of firm A be more expensive?Note: This value is the sum of the value of assets already in place plus the present value of future growth opportunities.Problem 2. [30 points]One important challenge in the creation of DCF models is the estimation of growth rates and tying them into the model. How is growth in the free cash flow to the firm (FCFF) generated? Overly simplified, firms can increase their revenue while maintaining their margins (sell more of a unit without incurring additional costs), or firms can improve their margins while maintaining their revenue (sell the same number of units while making more profit on each), or both. Consider the following simplified income statement and balance sheet of a firm for 2020:Net Sales $ 1,000.00COGS $ 200.00Operating Costs $ 500.00 Operating Costs ex. D&A $ 420.00 D&A $ 80.00Operating Income / EBIT $ 300.00Interest Expenses $ 40.00Taxes $ 65.00Net Income $ 195.00Total Assets $ 5000.00 Cash $ 1000.00 Operating Assets $ 4000.00Total Liabilities $ 2000.00Total Equity $ 3000.00Note that the tax rate is . The firm records capital expenditures of $ 160.00 and no changes to non-cash working capital.What is the free cash flow to the firm?What is firm’s reinvestment rate, defined as What is the firm’s return on capital, defined as What is your expectation for the FCFF of the firm in 2021?Just like earnings and dividends could not grow faster than , free cash flow to the firm cannot grow faster than . These two numbers, roughly, represent increases in revenue by growth and increases in margins, as discussed above. Problem 3. [30 Points] Create an Excel file with the Microsoft valuation according to the problems in the worksheet and our in-class discussion. 3 attachmentsSlide 1 of 3attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3.slider-slide > img { width: 100%; display: block; } .slider-slide > img:focus { margin: auto; } Unformatted Attachment Preview Name: _______________________________________ Homework 8 Unless stated otherwise, round your answers to two decimal points, and do not round intermediate calculations. Problem 1. [20 points] We are going to explore the difference between two different companies, both from the same sector, firms A and B. They differ in their reinvestment abilities. Firm A and Firm B are both financed with equity only. At the end of their business year, both firms report $1,000m in revenue. The net income is $100m for both firms. Assume that management is able to maintain a constant profit margin of 10%. • • Firm A is capable of achieving 5% revenue growth annually by investing 25% of their net income. Firm B is capable of achieving 5% revenue growth annually by investing 50% of their net income. Assume that this difference persists into the future. a) Find the value of firms A and B. Use a discount rate of 10% for both firms. Note: This value is the sum of the value of assets already in place plus the present value of future growth opportunities. b) The forward P/E ratio of a company is the price of a share divided by next year’s earnings per share, or its value divided by next year’s earnings. What is the forward P/E ratio of firms A and B? c) Comment on your result. Why is the forward P/E ratio higher for firm A? Or said differently: Why would shares of firm A be more expensive? Problem 2. [30 points] One important challenge in the creation of DCF models is the estimation of growth rates and tying them into the model. How is growth in the free cash flow to the firm (FCFF) generated? Overly simplified, firms can increase their revenue while maintaining their margins (sell more of a unit without incurring additional costs), or firms can improve their margins while maintaining their revenue (sell the same number of units while making more profit on each), or both. Consider the following simplified income statement and balance sheet of a firm for 2020: Net Sales COGS Operating Costs Operating Costs ex. D&A D&A Operating Income / EBIT Interest Expenses Taxes Net Income $ $ $ $ $ $ $ $ $ 1,000.00 200.00 500.00 420.00 80.00 300.00 40.00 65.00 195.00 Total Assets Cash Operating Assets Total Liabilities Total Equity $ $ $ $ $ Note that the tax rate is = 25%. 1 5000.00 1000.00 4000.00 2000.00 3000.00 Name: _______________________________________ The firm records capital expenditures of $ 160.00 and no changes to non-cash working capital. a) What is the free cash flow to the firm? b) What is firm’s reinvestment rate, defined as = − & + ℎ ℎ ? (1 − ) c) What is the firm’s return on capital, defined as = (1 − ) ? + − ℎ Just like earnings and dividends could not grow faster than , free cash flow to the firm cannot grow faster than × . These two numbers, roughly, represent increases in revenue by growth and increases in margins, as discussed above. d) What is your expectation for the FCFF of the firm in 2021? Problem 3. [30 Points] Create an Excel file with the Microsoft valuation according to the problems in the worksheet and our inclass discussion. 2 ITEM 8. FINANCIAL STATEMENTS AND SUPPLEMENTARY DATA INCOME STATEMENTS (In millions, except per share amounts) Year Ended June 30, 2018 2017 64,497 45,863 $ 63,811 32,760 Total revenue 110,360 96,571 91,154 Cost of revenue: Product Service and other 15,420 22,933 15,175 19,086 17,880 14,900 Total cost of revenue 38,353 34,261 32,780 Gross margin Research and development Sales and marketing General and administrative Impairment and restructuring 72,007 14,726 17,469 4,754 0 62,310 13,037 15,461 4,481 306 58,374 11,988 14,635 4,563 1,110 Operating income Other income (expense) Interest income (expense) 35,058 4,149 (2,733) 29,025 876 26,078 (439) Income before income taxes (EBT) Provision for income taxes 36,474 19,903 29,901 4,412 25,639 5,100 $ 25,489 $ 20,539 $ $ $ $ 2.59 2.56 Revenue: Product Service and other $ Net income $ 16,571 Earnings per share: Basic Diluted $ $ 2.15 2.13 Weighted average shares outstanding: Basic Diluted Cash dividends declared per common share Refer to accompanying notes. Operating Income + One-Time Income (Expenses) = EBIT Net Income = EBIT – Interest – Taxes 7,700 7,794 $ 1.68 3.29 3.25 2016 $ 7,746 7,832 $ 1.56 67,336 23,818 7,925 8,013 $ 1.44 OTHER INCOME (EXPENSE), NET The components of other income (expense), net were as follows: (In millions) Year Ended June 30, Dividends and interest income Interest expense Net recognized gains on investments Net losses on derivatives Net losses on foreign currency remeasurements Other, net Total 2018 2017 2016 $ 2,214 $ (2,733) 2,399 (187) (218) (59) 1,387 $ (2,222) 2,583 (510) (111) (251) 903 (1,243) 668 (443) (129) (195) $ 1,416 $ 876 $ (439) BALANCE SHEETS (In millions) June 30, 2018 Assets Current assets: Cash and cash equivalents Short-term investments $ 11,946 121,822 Total cash, cash equivalents, and short-term investments Accounts receivable, net of allowance for doubtful accounts of $377 and $345 Inventories Other Liabilities and stockholders’ equity Current liabilities: Accounts payable Short-term debt Current portion of long-term debt Accrued compensation Short-term income taxes Short-term unearned revenue Other Total current liabilities Long-term debt Long-term income taxes Long-term unearned revenue Deferred income taxes Operating lease liabilities Other long-term liabilities Total liabilities Commitments and contingencies Stockholders’ equity: Common stock and paid-in capital – shares authorized 24,000; outstanding 7,677 and 7,708 Retained earnings Accumulated other comprehensive income (loss) Total stockholders’ equity Total liabilities and stockholders’ equity $ 7,663 125,318 133,768 26,481 2,662 6,751 132,981 22,431 2,181 5,103 169,662 29,460 6,686 1,862 35,683 8,053 7,442 162,696 23,734 6,555 6,023 35,122 10,106 6,076 $258,848 $250,312 $ $ Total current assets Property and equipment, net of accumulated depreciation of $29,223 and $24,179 Operating lease right-of-use assets Equity and other investments Goodwill Intangible assets, net Other long-term assets Total assets 2017 8,617 0 3,998 6,103 2,121 28,905 8,744 7,390 9,072 1,049 5,819 718 24,013 7,684 58,488 72,242 30,265 3,815 541 5,568 5,211 55,745 76,073 13,485 2,643 5,734 5,372 3,549 176,130 162,601 71,223 13,682 (2,187) 69,315 17,769 627 82,718 87,711 $258,848 $250,312 CASH FLOWS STATEMENTS (In millions) Year Ended June 30, Operations Net income Adjustments to reconcile net income to net cash from operations: Asset impairments Depreciation, amortization, and other Stock-based compensation expense Net recognized gains on investments and derivatives Deferred income taxes Changes in operating assets and liabilities: Accounts receivable Inventories Other current assets Other long-term assets Accounts payable Unearned revenue Income taxes Other current liabilities Other long-term liabilities 2018 $ Net cash from operations Financing Proceeds from issuance (repayments) of short-term debt, maturities of 90 days or less, net Proceeds from issuance of debt Repayments of debt Common stock issued Common stock repurchased Common stock cash dividends paid Other, net Net cash from (used in) financing Investing Additions to property and equipment Acquisition of companies, net of cash acquired, and purchases of intangible and other assets Purchases of investments Maturities of investments Sales of investments Securities lending payable Net cash used in investing Effect of foreign exchange rates on cash and cash equivalents Net change in cash and cash equivalents Cash and cash equivalents, beginning of period Cash and cash equivalents, end of period $ 16,571 2017 $ 25,489 2016 $ 20,539 0 10,261 3,940 (2,212) (5,143) 0 8,778 3,266 (2,073) (829) 630 6,622 2,668 (223) 2,479 (3,862) (465) (952) (285) 1,148 5,922 18,183 798 (20) (1,216) 50 1,028 (917) 81 3,820 1,792 356 (118) 562 600 (1,212) (1,110) 88 2,565 (298) (179) (406) 43,884 39,507 33,325 (7,324) 7,183 (10,060) 1,002 (10,721) (12,699) (971) (4,963) 44,344 (7,922) 772 (11,788) (11,845) (190) 7,195 13,884 (2,796) 668 (15,969) (11,006) (369) (33,590) 8,408 (8,393) (11,632) (8,129) (8,343) (888) (137,380) 26,360 117,577 (98) (25,944) (176,905) 28,044 136,350 (197) (1,393) (129,758) 22,054 93,287 203 (6,061) (46,781) (23,950) 50 19 4,283 7,663 1,153 6,510 11,946 $ 7,663 (67) 915 5,595 $ 6,510 Worksheet AD 717 Name: ___________________________ When trying to determine the intrinsic value of a company, analysts use past financial data and assumptions about its future growth. One method of analysis is the so-called discounted free cash flow analysis. Using this worksheet, you will create a DCF valuation of Microsoft (MSFT). For your analysis, consider the annual financial report of Microsoft, which is available in full on the website of the SEC as form 10-K. It contains the company’s income statement, balance sheet, and cash flow statement. Unless specified differently, compute results for the years 2017 and 2018. Use an Excel spreadsheet to keep track of your results. 1. The gross profit of a company is defined as its revenue minus the cost to generate that revenue. Find Microsoft’s gross profit, in US dollars and as a percentage. 2. In order to keep Microsoft running and growing, the company incurs costs for research and development, sales and marketing, and other general administrative purposes. Once these costs are deducted from the gross profit, we find the operating profit, also called EBIT (Earnings before interest and taxes). This result is important for our discounted cash flow analysis. What is Microsoft’s operating profit, in US dollars and as a percentage? 3. Identify the tax rate at which Microsoft’s earnings, that is, what is left of its revenue after cost of revenue, operating expenses, and interest expenses, were taxed. 4. To calculate the free cash flow of Microsoft, we need to calculate how much of operating income (not EBIT) is left over after taxes. Compute this number. 5. As time passes, assets on the company’s balance sheet lose their value, which is charged as depreciation and amortization, depending on whether the assets are tangible or intangible. To account for this, companies take a non-cash charge on their income statement. While this reduces the tax burden of the company, it does not reduce the free cash flow. Using the cash flow statement, find the non-cash charge taken for depreciation and amortization (D&A). 6. We start from the net operating profit after taxes; then we introduce the contribution of D&A. Explain if the value for D&A contributes positively or negatively to free cash flow that we derive from the net operating profit after taxes.. 7. When considering the balance sheet of Microsoft, we see that a distinction is made between current assets and non-current assets, and likewise for liabilities. Explain why this distinction is important. 8. The net cash working capital of a company is defined as the difference between its current operating assets and its current operating liabilities, and it can be thought of as a measure of the liquidity to operate the business. That’s why we exclude things like cash or short-term debt – they are financial and not operational. Identify current assets and current liabilities on the balance sheet and calculate Microsoft’s working capital. 9. While negative working capital means that liabilities due within the next twelve months exceed the assets available in the same period of time, it need not be a bad sign. From a practical perspective, Microsoft needs to finance this shortcoming somehow. Explain if negative working capital increases or decreases the cash flow. 10. Companies use their funds to add new tangible assets or upgrade existing ones. Capital expenditure or capex summarize the amount of money spent for these purposes. Identify Microsoft’s capex. 11. We have now assembled the most important building blocks for our discounted cash flow analysis: Operating income after taxes, depreciation and amortization, changes in the working capital, and capital expenditure. What was the free cash flow to the firm for Microsoft in the fiscal year that ended June 30, 2018? 12. Assuming no growth and a discount rate of 8%, calculate the present value of all cash flows from 2019 to 2023. 13. Obviously, the no-growth assumption is not realistic. Revenues and earnings change over time. We have to estimate these changes. Include parameters for growth in your model and update your calculations. 14. What happens after the year 2023? Purchase answer to see full attachment Tags: gross profit Microsoft Balance Sheet tax rate free cash flow Operating profit User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
In what ways is the Maternal Role Attainment- Becoming a Mother by...
Describe your plan for carrying out a collaborative assessment with...
Quantitative analysis is defined as? Group of answer choices:...
Please refernece attachment. See image below. The following is from...
Kohlberga "s three levels of moral development Title of level...
in a stakeholder model, communities are an example of primary...
.If a pediatric client was not meeting developmental milestones,...