CASE Share Repurchase n this CASE we are going to implement a share repurchase. We will utilize all three of the major financial statements. Please start this assignment with the forecast for 2020E and 2021E set to the consensus revenues and EPS we used in the last CASE Simple Forecast. This means you should re-set your forecast! In CASE Simple Forecast, row 137, we found the consensus forecast for Microsoft (MSFT): \begin{tabular}{|l|r|r|r|r|} \hline & Low & High & Mean \\ \hline 2020E & & & \\ \hline Revenues \$B & 139.4 & 152.0 & 142.2 \\ \hline EPS & 5.46 & 6.00 & 5.66 \\ \hline Revenues \$B & 153.2 & & \\ \hline EPS & 5.52 & 168.7 & 159.7 \\ \hline \\ Source: CNN Money, 3/16/20 & 7.11 & 6.30 \\ \hline \end{tabular} The Board has approved a share repurchase program of $10 billion and these repurchases will be primarily financed through free cash flow. (3/10/20) -irst let's consider whether the company has the cash in place to execute the repurchase or whether it may need to seek outside financing. In recent years, many companies have taken advantage of low interest rates and borrowed money, raising Debt, to finance share repurchases.) In this instance, the company has announced it will self finance the repurchase. Other times, the company may not indicate how it is financing the repurchase and the analyst will determine whether it will be through: (a) Cash on hand (Excess Cash on the Balance Sheet) (b) projected Free Cash Flows (c) new Debt financing The 'Cash Flow Statement' Sheet will automatically calculate Free Cash Flows based on your Income Statement and Balance Sheet forecasts. As a reminder, we already used the 'Income Statement (GAAP)' Sheet to forecast 2020E and 2021E earnings in line with the consensus forecast for revenues and earnings. Our Balance Sheet forecasts are set at the default levels, which rise and fall with the forecast for Revenues. How much Free Cash Flow do you forecast Microsoft will generate in FY2020E (\$ Millions)? Do you think Microsoft can self finance this repurchase with its Free Cash Flows? Second, let's determine how many shares Microsoft will repurchase with $10 billion? Find the closing price of MSFT stock on 3/10/20, the day of the announcement. Let's say 'the close' on the day of the announcement is: $160/ share. We do not want to forecast the repurchase price and instead will assume the current close is the repurchase price. (This is what a working financial analyst on Wall Street would do.) The repurchase also affects the Balance Sheet. When we implemented the repurchase we did not simply reduce the share count on the Income Statement. We also paid for the repurchase, as evidenced on the Balance Sheet. In this CASE, the repurchase is being paid for with Cash \& Marketable Securities (though it is also possible to issue Debt to finance an Equity repurchase). To account for the transaction, we would debit Equity (a decrease) and credit Cash \& Marketable Securities (a decrease). What is the value of Common Equity for 2020E following the repurchase (versus the default 20202E forecast in 'Balance Sheet')? Hint: If you cannot locate the right place on the Balance Sheet, use the Control F function and search for 'Common Equity'. \begin{tabular}{|l|r|r|} \hline Default Equity 2020E prior to the Repurchase & 128,966 \\ \hline Revised Equity 2020E forecast following the Repurchase & \\ \hline \end{tabular} Peeling back the onion a bit, you can see that the Common Equity on our Balance Sheet is forecast using a table in the Derivation of Accounts, Common Equity section. Take a look at the calculation of Equity in the forecast years and see how the repurchase has affected the balance. What happened to the Free Cash Flows for 2020E after you implemented the share repurchase? What happened to Excess Cash for 2020E after you implemented the share repurchase? \begin{tabular}{|l|l|l|l|l|} \hline & \multicolumn{1}{|l|}{} & \\ \hline \end{tabular} If the company finances the repurchase with Excess Cash, what is the impact on the Income Statement from the share repurchase? A The lost interest income earned on the cash utilized to repurchase the shares B The cash (on the Balance Sheet) used to repurchase the shares, $10 billion C The cash outflow (on the Cash Flow Statement) required to repurchase the shares Why isn't the forecast for Equity for 2020E exactly $10 billion lower than the 2019 Equity in our forecast? A Dividends B Net Income C Dividends and Net Income REMEMBER, THESE ARE FORECASTS FOR THIS CASE AND NOT THE NEXT CASE! Please end this assignment by restoring the figuires to the "base" case. IF YOU HAVE NOT RE-SET THE BASE CASE TO THE "DEFAULT" IN SFAM, PLEASE DO SO NOW! Since 2014 McDonalds has been converting restaurants it owned and operated to franchises. In owned and operated stores all of the activities are included in the financial statements. This makes the income statement and balance sheet much larger than companies that sell franchises, but margins and asset turns are commensurately smaller. In franchised stores only franchising related fees are included in the financial statements. This makes the income statement and balance sheet much smaller than companies that sell franchises, but margins and asset turns are commensurately large. Transitioning company owned stores to franchises has dramatically changed McDonalds' financials. Also since 2014 McDonalds has been repurchasing shares, driving its Common Equity negative. For each of the following accounts select in column F whether the measure has 'increased' or 'decreased' from 2014 to date. For each of the following accounts select in column H the cause of the change in column F : Franchising - the change resulted from McDonald's conversion of company operated restaurants to third-party franchises Share Repurchases - the change resulted from McDonald's aggressive repurchasing of shares Neither - the change resulted from neither franchising of company operated restaurants nor share repurchases Both - the change resulted from both franchising of company operated restaurants and share repurchases The increase in reported PP\&E is result of that change and not true CAPEX. Analyzing anomalous data is another example of Data Integrity. SFAM's default forecast assumes that future year drivers will be the same as last year's. Use SFAM to forecast for 2020 the following hypothesized scenarios: Hint : Remember to restore SFAM because of prior changes you have made to SG\&A and R\&D. We do not want to forecast the repurchase price and instead will assume the current close is the repurchase price. (This is what a working financial analyst on Wall Street would do.) Calculate the number of shares the company is likely to repurchase at the current share price: Hint: Calculate how many shares you can afford to buy with $10 billion if the price of a share is $160. 62,500,000 Now let's implement the repurchase using SFAM. Jse the share repurchase model on the 'Balance Sheet' Sheet which is located in the 'Common Equity' section of the Derivation of Accounts segment. The Share Repurchase model has been built for you and starts in Row 221 on the 'Balance Sheet' Sheet. 1 Insert the number of shares to be repurchased in the space provided for the 'Issues (Repurchases) of Shares' on 'Balance Sheet' Sheet in AH221. The number is expressed in millions, so insert '-10' if you mean 10,000,000. Hint: Take the number you calculated in B56 and divide by 1 million or 1,000,000 to arrive at the figure to insert expressed in millions. This is a repurchase so the number is negative! Given Microsoft's $160 share price and the $10 billion size of the repurchase, the number of shares inserted should be between -1 and -100 . 2 Insert the $160 repurchase price in the yellow cell for 'Price per Share' on the 'Balance Sheet' Sheet in AH222. 3 Verify the amount repurchased is the $10 billion or $10,000,000,000 we intended to repurchase. It should say 10,000 in the line reading Issues (Repurchases) of Equity below the prior two inserted fields. How many shares are in the diluted share count for 2020E and 2021E? \begin{tabular}{|c|c|c|c|} \hline & 2019A & 2020E & 2021E \\ \hline Share Count Prior to Repurchase & 7,753 & 7,753 & 7,753 \\ \hline hare Count Now, After Repurchase & & & \\ \hline \end{tabular} Did the share count decline from 2019 to 2020 by the entire number you repurchased as expressed in your answer in B40? The answer should be no. The reason why is that most companies do not announce all of their repurchases at the beginning of the year on January 1. Therefore the entirety of the mid-year repurchase will not fully be visible in the average diluted share count for the current year. Instead, you will see part of the share repurchase in FY2020E and part of the share repurchase in FY2021E. If the repurchase were announced in June 30, exactly the midpoint of the year, half of the repurchase would decrease shares in FY2020E and half in FY2021E. The key is to understand that the share count is a weighted average figure for the entire fiscal year. The repurchase also affects the Balance Sheet. When we implemented the repurchase we did not simply reduce the share count on the Income Statement. We also paid for the repurchase, as evidenced on the Balance Sheet. In this CASE, the repurchase is being paid for with Cash \& Marketable Securities (though it is also possible to issue Debt to finance an Equity repurchase). To account for the transaction, we would debit Equity (a decrease) and credit Cash \& Marketable Securities (a decrease). CASE Share Repurchase n this CASE we are going to implement a share repurchase. We will utilize all three of the major financial statements. Please start this assignment with the forecast for 2020E and 2021E set to the consensus revenues and EPS we used in the last CASE Simple Forecast. This means you should re-set your forecast! In CASE Simple Forecast, row 137, we found the consensus forecast for Microsoft (MSFT): \begin{tabular}{|l|r|r|r|r|} \hline & Low & High & Mean \\ \hline 2020E & & & \\ \hline Revenues \$B & 139.4 & 152.0 & 142.2 \\ \hline EPS & 5.46 & 6.00 & 5.66 \\ \hline Revenues \$B & 153.2 & & \\ \hline EPS & 5.52 & 168.7 & 159.7 \\ \hline \\ Source: CNN Money, 3/16/20 & 7.11 & 6.30 \\ \hline \end{tabular} The Board has approved a share repurchase program of $10 billion and these repurchases will be primarily financed through free cash flow. (3/10/20) -irst let's consider whether the company has the cash in place to execute the repurchase or whether it may need to seek outside financing. In recent years, many companies have taken advantage of low interest rates and borrowed money, raising Debt, to finance share repurchases.) In this instance, the company has announced it will self finance the repurchase. Other times, the company may not indicate how it is financing the repurchase and the analyst will determine whether it will be through: (a) Cash on hand (Excess Cash on the Balance Sheet) (b) projected Free Cash Flows (c) new Debt financing The 'Cash Flow Statement' Sheet will automatically calculate Free Cash Flows based on your Income Statement and Balance Sheet forecasts. As a reminder, we already used the 'Income Statement (GAAP)' Sheet to forecast 2020E and 2021E earnings in line with the consensus forecast for revenues and earnings. Our Balance Sheet forecasts are set at the default levels, which rise and fall with the forecast for Revenues. How much Free Cash Flow do you forecast Microsoft will generate in FY2020E (\$ Millions)? Do you think Microsoft can self finance this repurchase with its Free Cash Flows? Second, let's determine how many shares Microsoft will repurchase with $10 billion? Find the closing price of MSFT stock on 3/10/20, the day of the announcement. Let's say 'the close' on the day of the announcement is: $160/ share. We do not want to forecast the repurchase price and instead will assume the current close is the repurchase price. (This is what a working financial analyst on Wall Street would do.) The repurchase also affects the Balance Sheet. When we implemented the repurchase we did not simply reduce the share count on the Income Statement. We also paid for the repurchase, as evidenced on the Balance Sheet. In this CASE, the repurchase is being paid for with Cash \& Marketable Securities (though it is also possible to issue Debt to finance an Equity repurchase). To account for the transaction, we would debit Equity (a decrease) and credit Cash \& Marketable Securities (a decrease). What is the value of Common Equity for 2020E following the repurchase (versus the default 20202E forecast in 'Balance Sheet')? Hint: If you cannot locate the right place on the Balance Sheet, use the Control F function and search for 'Common Equity'. \begin{tabular}{|l|r|r|} \hline Default Equity 2020E prior to the Repurchase & 128,966 \\ \hline Revised Equity 2020E forecast following the Repurchase & \\ \hline \end{tabular} Peeling back the onion a bit, you can see that the Common Equity on our Balance Sheet is forecast using a table in the Derivation of Accounts, Common Equity section. Take a look at the calculation of Equity in the forecast years and see how the repurchase has affected the balance. What happened to the Free Cash Flows for 2020E after you implemented the share repurchase? What happened to Excess Cash for 2020E after you implemented the share repurchase? \begin{tabular}{|l|l|l|l|l|} \hline & \multicolumn{1}{|l|}{} & \\ \hline \end{tabular} If the company finances the repurchase with Excess Cash, what is the impact on the Income Statement from the share repurchase? A The lost interest income earned on the cash utilized to repurchase the shares B The cash (on the Balance Sheet) used to repurchase the shares, $10 billion C The cash outflow (on the Cash Flow Statement) required to repurchase the shares Why isn't the forecast for Equity for 2020E exactly $10 billion lower than the 2019 Equity in our forecast? A Dividends B Net Income C Dividends and Net Income REMEMBER, THESE ARE FORECASTS FOR THIS CASE AND NOT THE NEXT CASE! Please end this assignment by restoring the figuires to the "base" case. IF YOU HAVE NOT RE-SET THE BASE CASE TO THE "DEFAULT" IN SFAM, PLEASE DO SO NOW! Since 2014 McDonalds has been converting restaurants it owned and operated to franchises. In owned and operated stores all of the activities are included in the financial statements. This makes the income statement and balance sheet much larger than companies that sell franchises, but margins and asset turns are commensurately smaller. In franchised stores only franchising related fees are included in the financial statements. This makes the income statement and balance sheet much smaller than companies that sell franchises, but margins and asset turns are commensurately large. Transitioning company owned stores to franchises has dramatically changed McDonalds' financials. Also since 2014 McDonalds has been repurchasing shares, driving its Common Equity negative. For each of the following accounts select in column F whether the measure has 'increased' or 'decreased' from 2014 to date. For each of the following accounts select in column H the cause of the change in column F : Franchising - the change resulted from McDonald's conversion of company operated restaurants to third-party franchises Share Repurchases - the change resulted from McDonald's aggressive repurchasing of shares Neither - the change resulted from neither franchising of company operated restaurants nor share repurchases Both - the change resulted from both franchising of company operated restaurants and share repurchases The increase in reported PP\&E is result of that change and not true CAPEX. Analyzing anomalous data is another example of Data Integrity. SFAM's default forecast assumes that future year drivers will be the same as last year's. Use SFAM to forecast for 2020 the following hypothesized scenarios: Hint : Remember to restore SFAM because of prior changes you have made to SG\&A and R\&D. We do not want to forecast the repurchase price and instead will assume the current close is the repurchase price. (This is what a working financial analyst on Wall Street would do.) Calculate the number of shares the company is likely to repurchase at the current share price: Hint: Calculate how many shares you can afford to buy with $10 billion if the price of a share is $160. 62,500,000 Now let's implement the repurchase using SFAM. Jse the share repurchase model on the 'Balance Sheet' Sheet which is located in the 'Common Equity' section of the Derivation of Accounts segment. The Share Repurchase model has been built for you and starts in Row 221 on the 'Balance Sheet' Sheet. 1 Insert the number of shares to be repurchased in the space provided for the 'Issues (Repurchases) of Shares' on 'Balance Sheet' Sheet in AH221. The number is expressed in millions, so insert '-10' if you mean 10,000,000. Hint: Take the number you calculated in B56 and divide by 1 million or 1,000,000 to arrive at the figure to insert expressed in millions. This is a repurchase so the number is negative! Given Microsoft's $160 share price and the $10 billion size of the repurchase, the number of shares inserted should be between -1 and -100 . 2 Insert the $160 repurchase price in the yellow cell for 'Price per Share' on the 'Balance Sheet' Sheet in AH222. 3 Verify the amount repurchased is the $10 billion or $10,000,000,000 we intended to repurchase. It should say 10,000 in the line reading Issues (Repurchases) of Equity below the prior two inserted fields. How many shares are in the diluted share count for 2020E and 2021E? \begin{tabular}{|c|c|c|c|} \hline & 2019A & 2020E & 2021E \\ \hline Share Count Prior to Repurchase & 7,753 & 7,753 & 7,753 \\ \hline hare Count Now, After Repurchase & & & \\ \hline \end{tabular} Did the share count decline from 2019 to 2020 by the entire number you repurchased as expressed in your answer in B40? The answer should be no. The reason why is that most companies do not announce all of their repurchases at the beginning of the year on January 1. Therefore the entirety of the mid-year repurchase will not fully be visible in the average diluted share count for the current year. Instead, you will see part of the share repurchase in FY2020E and part of the share repurchase in FY2021E. If the repurchase were announced in June 30, exactly the midpoint of the year, half of the repurchase would decrease shares in FY2020E and half in FY2021E. The key is to understand that the share count is a weighted average figure for the entire fiscal year. The repurchase also affects the Balance Sheet. When we implemented the repurchase we did not simply reduce the share count on the Income Statement. We also paid for the repurchase, as evidenced on the Balance Sheet. In this CASE, the repurchase is being paid for with Cash \& Marketable Securities (though it is also possible to issue Debt to finance an Equity repurchase). To account for the transaction, we would debit Equity (a decrease) and credit Cash \& Marketable Securities (a decrease)