B 9 1 As discussed in the 'SFAM Balance Sheet' Sheet we have divided Cash & Marketable Securities into Operating Cash and Excess Cash. In this Sheet, you will analyze how much Operating Cash is required (Excess Cash becomes the balance) While this sounds like an academic exercise, it represents a relevant and real question in the financial world. When a company or business is acquired it is purchased at a negotiated price. However, the price agreed to at the time of an offer is often subject to 'purchase price adjustments' based on the financials at the time the acquisition is closed. For example, if accounts receivable were $100,000 at the time the offer was accepted and $110,000 at the closing time a $10,000 purchase price adjustment would be required. The agreement often requires the company to leave the amount of Operating Cash required for the business and permits the seller to retain the Excess Cash. The amount of cash required to operate the business, 'Operating Cash', is generally a negotiated amount in this process. Buyers, having previously established an acquisition price, want the Operating Cash requirement to be as high as possible. Sellers, having previously established an acquisition price, want the Operating Cash requirement to be as low as possible. There is no one right amount of Operating Cash. It is generally determined based on the history of the business and comparable data, Forecasting (Workbook 8), of similar businesses. 2 Analyzing the 'Right' Level of Operating Cash 3 You are going to analyze the data on the 'Data Operating Cash' sheet to establish a general guideline of how much Operating Cash as a percent of Revenues companies require. 54 Ultimately, when you analyze specific companies, specific situations may influence the amount of Operating Cash a particular company needs to hold. 55 56 57 Companies with high seasonality may need to hold more cash to buffer the peaks and valleys. Highly cyclical companies (e.g., automobile manufactures) may need to hold cash in advance of a downcycle to avoid having to go to the credit markets when their finances are weak. Companies that receive highly erratic and large payments from customers may hold more cash to buffer them. 58 Create a PivotTable based on the data in a range named "Data" on the 'Data Operating Cash' Sheet of multiple measures of cash as a percent of Revenues for each of the S&P 500 companies ex Finan 59 60 61 62 63 1 Create a PivotTable in Cell A11 of the 'Sheet: DATA Operating Cash' Sheet using the data in the table named 'Data' on that Sheet. 2 Drag 'Ticker' from the 'Field Name' box to the 'Rows' box. 3 Drag the 'Cash & Cash Eqiv Pct', 'Cash & STI Pct' and 'Cash & Mkt Sec Pct' from the 'Field Name' box to the 'Values' box. 4 Select the 'I' on a Mac or the down arrow on a Windows computer to the right of each of these three entries in the 'Values Box' and in the 'PivotTable Field' window select 'Min' and then click on 'Number...' and select 'Percentage' and select 1 decimal place. 64 The 'Min' or minimum shows for each company the lowest level of Cash & Mkt as a percent of Revenues at which that company has operated over the last 30+years. 65 66 Calculate the Average Cash as Percent of Revenues Across S&P 500 ex Financials 67 Across all companies, calculate the average of the minimum level of each measure of Cash at which each company has operated. 68 Write a formula in Cell B72 that averages Column B of the PivotTable on the 'DATA Operating Cash' Sheet. 69 Copy Cell B72 to the right such that the formula averages the cells in the PivotTable in that column. 70 71 72 Cash & Cash Equiv Cash & STI Cash & Mkt Sec Average 73 74 Excluding Outliers, Calculate the Average Cash as Percent of Revenues for the S&P 500 ex Financials 25678 77 76 Sort 'Min of Cash & Mkt Sec Pct' in the PivotTable in ascending order. 1 Click on column D ('Min of Cash & Mkt Sec Pct') anywhere in the PivotTable 2 Select the Sort Ascending icon on the Data Tab 79 Data Review View ns AZ Clear Sort Filter Advanced 80 This will result in the 'Min of Cash & Mkt Sec Pct' being sorted in ascending order. 81 If you scan down the 'Min of Cash & Mkt Sec Pct' in the PivotTable you created you will see extreme variations with the company with the highest minimum level of 135% of Revenues. 82 We will therefore apply 'windsorization' or 'data clipping' to exclude outliers. 83 In column E of the 'DATA Operating Cash' Sheet starting in E12 enter formulas that in each row averages the 'Min of Cash & Mkt Sec Pct' from row 12 to that row. This can be done by writing a formula using the AVERAGE function over the range starting in cell 'D$12' and going through 'DXX' where XX is the row number of each row. This will result in '=AVERAGE(D$12:DXX)', where XX is row number of each row. 84 85 86 When you copy this down the E$12 will remain fixed while the range expands with each additional row. 87 88 Analyze the Data 89 You have now prepared the data - it is time to analyze it. 90 This is a situation in which an answer or result is judgmental. Such a situation in not uncommon in financial analysis. 91 Looking at the results in the above table and notice that the result is robust and does not vary significantly based on the definition of an outlier. 92 Let's define outliers to be the 10% of companies with 'Min of Cash & Mkt Sec Pct' being in the top decile of the ratio (above the 90% level) 93 There are approximately 400 companies going through row 417. Subtracting 40 (10% of 400) from 417 would target row 377. 94 Enter as a percent the average 'Min of Cash & Mkt Sec Pct' up through row 377? 95 96 Based on this data, we believe that a cash balance of 3% of Revenues would be a reasonable, if not conservative, level of Operating Cash requirements for the typical company. 97 For special situations you, as a financial analyst, will need to consider idiosyncratic situations and make company specific judgment calls. 98 For example, Ford has maintained a dramatically higher level of cash (20+% in the last decade) to buffer against extreme cyclicality in the industry. 99 100 101 102 103