Mini cases
padma25
Ch16ToolKit.xlsxChapter
| Tool Kit | Chapter 16 | 10/27/15 | ||||||||
| Supply Chains and Working Capital Management | ||||||||||
| 16-3 The Cash Conversion Cycle | ||||||||||
| The cash conversion cycle starts with the receipt of raw materials and ends with the collection of cash from sales. | ||||||||||
| Target Inputs: | ||||||||||
| Target inventory conversion period, ICP (days) = | 50 | |||||||||
| Target average collection period, ACP (days) = | 60 | |||||||||
| Target payables deferral period, PDP (days) = | 40 | |||||||||
| Target Cash Conversion Cycle: | ||||||||||
| Target Cash Conversion Cycle (CCC) | = | Inventory conversion period | + | Average collection period | − | Payables deferral period | ||||
| = | 50 | + | 60 | − | 40 | |||||
| = | 70 | |||||||||
| Selected Items from GBM's Financial Statements (Millions of Dollars) | ||||||||||
| Annual sales | $150 | |||||||||
| Cost of goods sold | $122 | |||||||||
| Inventories | $17 | |||||||||
| Accounts receivable | $51 | |||||||||
| Accounts payable | $13 | |||||||||
| Days/year | 365 | |||||||||
| Figure 16-3 | ||||||||||
| GBM's Target and Actual Cash Conversion Cycles (Dollars in Millions) | ||||||||||
| Panel A. Target CCC: Based on Plans | ||||||||||
| Target Cash Conversion Cycle (CCC) | = | Planned Inventory Conversion Period (ICP) | + | Credit Terms Offered to Customers (ACP) | − | Credit Terms Offered by Suppliers (PDP) | ||||
| = | 50 | + | 60 | − | 40 | |||||
| Target CCC | = | 70 | ||||||||
| Panel B. Actual CCC: Based on Financial Statements | ||||||||||
| Sales | $150.0 | |||||||||
| COGS | $122.0 | |||||||||
| Inventories | $17.0 | |||||||||
| Receivables | $51.0 | |||||||||
| Payables | $13.0 | |||||||||
| Days/year | 365 | |||||||||
| Actual CCC | = | Inventory ÷ (COGS/365) | + | Receivables ÷ (Sales/365) | − | Payables ÷ (COGS/365) | ||||
| = | $17 ÷ ($122/365) | + | $51 ÷ ($150/365) | − | $13 ÷ ($122/365) | |||||
| = | 50.9 | + | 124.1 | − | 38.9 | |||||
| Actual CCC | = | 136.1 | ||||||||
| Panel C. Actual versus Target Components | ||||||||||
| ICP | ACP | PDP | ||||||||
| Actual − Target | = | 50.9 − 50.0 | 124.1 − 60.0 | 38.9 − 40.0 | ||||||
| = | 0.9 | 64.1 | −1.1 | |||||||
| % Difference | = | 1.7% | 106.8% | −2.8% | ||||||
| Evaluation | = | Ok | Bad | Ok | ||||||
| Numbers in the figure are shown as rounded values for clarity in reporting. However unrounded values are used for all calculations. | ||||||||||
| Improvement in the cash conversion cylce can lead to substantial reductions in interest expenses due to working capital loans and to substantial increases in free cash flow. | ||||||||||
| Figure 16-4 | ||||||||||
| Benefits from Reducing the Cash Conversion Cycle (Dollars in Millions) | ||||||||||
| New Targets for Conversion Periods | Old (Actual) | New Target | ||||||||
| Inventory conversion period (ICP, days) | 50.9 | 35.0 | ||||||||
| Average collection period (ACP, days) | 124.1 | 40.0 | ||||||||
| Payable deferral period (PDP, days) | 38.9 | 50.0 | ||||||||
| Cash Conversion Cycle (CCC, days) | 136.1 | 25.0 | ||||||||
| Reduction in Cash Conversion Cycle: | 111.1 | |||||||||
| Impact of Reduction in CCC | Old (Actual) | New Target | ||||||||
| Annual sales: No change | $150.00 | $150.00 | ||||||||
| Costs of goods sold (COGS): No change | $122.00 | $122.00 | ||||||||
| Inventory: New level is ICP(COGS/365) | $17.00 | $11.70 | ||||||||
| Receivables: New level is ACP(Sales/365) | $51.00 | $16.44 | ||||||||
| Payables: New level is PDP(COGS/365) | $13.00 | $16.71 | ||||||||
| Net operating working capital: | ||||||||||
| NOWC = Inventory + Receivables – Payables | $55.00 | $11.42 | ||||||||
| Interest rate on NOWC loans (10%) | 10% | |||||||||
| Interest expense due to NOWC: 10%(NOWC) | $5.50 | $1.14 | ||||||||
| Improvement in Selected Results | ||||||||||
| Reduction in NOWC: | $43.6 | |||||||||
| Increase in free cash flow: | $43.6 | |||||||||
| Reduction in interest expense: | $4.36 | |||||||||
| Numbers in the figure are shown as rounded values for clarity in reporting. However unrounded values are used for all calculations. | ||||||||||
| Comparing the Days of Working Capital Measure with the Cash Conversion Cycle | ||||||||||
| In the chapter's opening vignette, we discussed "Days of working capital." DWC is essentially the same as the CCC except that the CCC uses the COGS when calculating both the ICP and the PDP whereas DWC uses sales for all calculations. Here's the DWC calculation for GBM: | ||||||||||
| Actual data | ||||||||||
| Days of Working Capital (DWC): NOWC/(Sales/365) | 133.8 | |||||||||
| CCC: NOWC component ÷ Sales or COGS ÷ 365 | 136.1 | |||||||||
| The CCC is larger than the DWC because Sales > COGS, and Sales is always used in the denominator for the DWC, lowering the result if inventories exceed payables. We regard the CCC as being a more meaningful because it is better reflects actual cash values. | ||||||||||
| 16-4 Inventory Management | ||||||||||
| Improvements in inventory management can increase free cash flow. | ||||||||||
| Original Inputs: | ||||||||||
| Cost of goods sold (COGS) = | $120 | |||||||||
| Inventory turnover ratio = Inventory/COGS = | 3 | |||||||||
| Original Results: | ||||||||||
| Inventory = | COGS/(Inventory turnover) | |||||||||
| Inventory = | $40 | |||||||||
| Improved Turnover: | ||||||||||
| Cost of goods sold (COGS) = | $120 | |||||||||
| Inventory turnover ratio = Inventory/COGS = | 4 | |||||||||
| Improved Results: | ||||||||||
| Inventory = | COGS/(Inventory turnover) | |||||||||
| Inventory = | $30 | |||||||||
| 16-5 Receivables Management | ||||||||||
| Accumulation of Receivables | ||||||||||
| The total amount of accounts receivable outstanding at any given time is determined by two factors: (1) the sales per day and (2) the average collection period. Following is an example based on Boston Lumber Company (BLC). | ||||||||||
| Inputs: | ||||||||||
| Sales per day = | $1,000 | |||||||||
| Average collection period (days) = | 30 | |||||||||
| Results: | ||||||||||
| Accounts receivable = | (Sales per day) x (Average collection period) | |||||||||
| Accounts receivable = | $30,000 | |||||||||
| Monitoring the Receivables Position | ||||||||||
| Credit Terms, Customer Behavior, and the Days Sales Outstanding | ||||||||||
| The average collection period (ACP), which is also called the days sales outstanding (DSO), depends on the credit terms and the percentage of customers that take the disount. The following example is for Super Set, Inc., a manufacturer of ultra-thin televisions. | ||||||||||
| Inputs: | ||||||||||
| Credit terms are: | 2/10, net 30 | |||||||||
| Units sold annually = | 219,000 | |||||||||
| Price per unit = | $200 | |||||||||
| Discount percentage = | 2% | |||||||||
| Discount period = | 10 | |||||||||
| Full credit period = | 30 | |||||||||
| Percentage of customers taking the discount = | 70% | |||||||||
| Percentage of customers not taking the discount = | 30% | |||||||||
| Results: | ||||||||||
| Average collection period = Days sales outstanding = | [(% taking discount)(Discount period)] + [(% not taking discount)(Full credit period)] | |||||||||
| ACP = DSO = | 16 | |||||||||
| Average daily sales = | [(Number of units sold annually)(Price per unit)] / 365 | |||||||||
| Average daily sales = | $120,000 | |||||||||
| Accounts receivable = | (Sales per day) x (Average collection period) | |||||||||
| (Sales per day) x (DSO) | ||||||||||
| Accounts receivable = | $1,920,000 | |||||||||
| Aging Schedules | ||||||||||
| An aging schedule breaks down a firm’s receivables by age of account. Table 16-1 shows the aging schedules of two television manufacturers, Super Set and Wonder Vision. | ||||||||||
| TABLE 16-1 | ||||||||||
| Aging Schedules | ||||||||||
| Super Set | Wonder Vision | |||||||||
| Age of Account (Days) | Value of Account | Percentage of Total Value | Value of Account | Percentage of Total Value | ||||||
| 0–10 | $1,344,000 | 70% | $902,400 | 47% | ||||||
| 11–30 | $576,000 | 30% | 499,200 | 26% | ||||||
| 31–45 | 0 | 0 | 288,000 | 15% | ||||||
| 46–60 | 0 | 0 | 192,000 | 10% | ||||||
| Over 60 | 0 | 0 | 38,400 | 2% | ||||||
| Total receivables | $1,920,000 | 100% | $1,920,000 | 100% | ||||||
| 16-6 Accruals and Accounts Payable (Trade Credit) | ||||||||||
| Cost of Trade Credit | ||||||||||
| If a company allows its customers to pay after say 30 days, then it is extending 30 days of free credit. If it has terms like 2/10, net 30, then it is extending 10 days of free credit and an additional 20 days of "non-free credit" that has a cost equal to the 2% discount that is foregone. Firms should calculate the cost of the non-free credit, compare it to the cost of funds from other sources such as banks, and then borrow from the source with the lowest cost, other things equal. | ||||||||||
| Microchip Electronics sells to Personal Computer Company (PCC) and offers credit terms show below. | ||||||||||
| Inputs: | ||||||||||
| Credit terms are: | 2/10, net 30 | |||||||||
| Discount percentage = | 2% | |||||||||
| Discount period = | 10 | |||||||||
| Credit period = | 30 | |||||||||
| List price per unit = | $100.00 | |||||||||
| Units purchased daily by PCC = | 200 | |||||||||
| Units purchased annually by PCC = | 73,000 | |||||||||
| True price = | $98.00 | |||||||||
| Finance charge = | $2.00 | |||||||||
| Results: PCC's choice in taking discount or credit | Pay at 10, Take Discount | Pay at 30, Take Credit | ||||||||
| Units purchased daily by PCC | 200 | 200 | ||||||||
| Days until payment | 10 | 30 | ||||||||
| True price | $98 | $98 | ||||||||
| Daily purchases = Units(True price) = | $19,600 | $19,600 | Note: See comment. Mike Ehrhardt: A question arises here: Should accounts payable reflect gross purchases or purchases net of discounts? Generally accepted accounting principles permit either treatment if the difference is not material, but if the discount is material then the transaction must be recorded net of discounts, or at “true” prices. Then, the higher payment that results from not taking discounts is reported as an expense called “discounts lost.” Therefore, we show accounts payable net of discounts even if the company does not expect to take discounts. |
|||||||
| Average accounts payable = (Daily sales)(DSO) = | $196,000 | $588,000 | ||||||||
| Take credit: Accounts payble = | $588,000 | |||||||||
| Not take discount: Accounts payble = | $196,000 | |||||||||
| Net credit received by not paying early = | $392,000 | |||||||||
| Number of units per year = | 73,000 | |||||||||
| Finance charge per unit = | $2.00 | |||||||||
| Total annual finance charge = | $146,000 | |||||||||
| Nominal annual cost = | (Total annual finance charge)/(Net credit received by not paying early) | |||||||||
| = | 37.2% | |||||||||
| We can calculate the nominal annual cost more directly from the credit terms. | ||||||||||
| Nominal cost of trade credit | = | Discount % | x | 365 | ||||||
| 100 – Discount % | Days credit out – Discount period | |||||||||
| = | 0.020 | x | 365 | |||||||
| 1 – 0.02 | 20 | |||||||||
| = | Periodic cost | x | Periods per year | |||||||
| = | 0.0204 | x | 18.25 | |||||||
| = | 0.3724489796 | |||||||||
| = | 37.24% | |||||||||
| Effective cost of trade credit | = | (1 + Periodic rate)^No. of periods – 1 | ||||||||
| = | 0.4458529273 | = | 44.59% | |||||||
| Suppose PCC pays in 60 days rather than 30. The nominal cost will go down. | ||||||||||
| Discount % = | 2% | |||||||||
| Discount period = | 10 | |||||||||
| Days credit outstanding = | 60 | |||||||||
| Nominal cost of trade credit | = | Discount % | x | 365 | ||||||
| 100 – Discount % | Days credit out – Discount period | |||||||||
| = | 0.0204 | x | 7.3 | |||||||
| = | 14.90% | |||||||||
| Effective cost of trade credit | = | (1 + Periodic rate)^No. of periods – 1 | ||||||||
| = | 0.1589098322 | = | 15.89% | |||||||
| Figure 16-5 | ||||||||||
| Different Credit Terms and Their Associated Costs | ||||||||||
| Days in Year: | 365 | |||||||||
| Credit Terms | Discount Percentage | Discount Period | Net period | Cost of Additional Credit | ||||||
| Nominal | Effective | |||||||||
| 1/10, net 20 | 1% | 10 | 20 | 36.87% | 44.32% | |||||
| 1/10, net 30 | 1% | 10 | 30 | 18.43% | 20.13% | |||||
| 1/10, net 90 | 1% | 10 | 90 | 4.61% | 4.69% | |||||
| 2/10, net 20 | 2% | 10 | 20 | 74.49% | 109.05% | |||||
| 2/10, net 30 | 2% | 10 | 30 | 37.24% | 44.59% | |||||
| 3/15, net 45 | 3% | 15 | 45 | 37.63% | 44.86% | |||||
| 16-7 The Cash Budget | ||||||||||
| Educational Procucts Corporation (EPC) | ||||||||||
| EPC uses a monthly cash budget for the coming year plus a daily cash budget for the coming month. The monthly cash budget is used for planning purposes, the daily budget for actual cash control. | ||||||||||
| One of EPC's primary concerns is negotiating a line of credit from its bank to cover any cash shortfall that might occur during the year. The treasurer, who is responsible for developing the cash budget, sets up several scenarios that vary mainly in terms of the credit policy and the state of the economy. Information about each scenario is given below, and the scenario data are shown below the verbal descriptions. Also, on a screen to the right, the cost of additional credit (beyond the discount period) is provided for each scenario. | ||||||||||
| Description of the scenarios. | ||||||||||
| Base case (Current).The company sells on terms of 2/10, net 60. 20% of customers pay in the 1st month and take discounts, 70% pay on time in the 2nd month, and 10% either pay in the 3rd month or never pay and end up as bad debts. Purchases are 60% of next month's sales, and payments for purchases are made the month after the purchase. Lease payments and payments due on a new plant are shown on the cash budget. Monthly gross sales are shown on the cash budget, and sales are adjusted for the 2% cash discount. The nominal cost of non-discount credit is 14.9%. | ||||||||||
| Big discount. Change the credit terms to 4/10, net 30. The payment pattern shifts as follows: 70% pay 1st month, 20% pay 2nd month, 10% pay 3rd month or never. Sales rise by 10% over the base case. Bad debts remain at zero. 4% discount is a price cut, stimulates sales. The nominal cost of add'l credit is 76%, which stimulates early payment. | ||||||||||
| Short net period. Change the credit terms to 2/10, net 30. The payment pattern shifts as follows: 50% pay in 1st month, 40% pay in 2nd month, 10% pay in 3rd month or never. Sales fall by 10% versus the base case. Bad debts remain at zero. Reducing the net period turns off some potential customers, reduces sales. The nominal cost of add'l credit is 37%, which is high enough to stimulate early payment. | ||||||||||
| Long net period. Change the credit terms to 1/10, net 90. The payment pattern shifts as follows: 5% pay 1st month, 5% pay 2nd month, 90% pay 3rd month or never. Sales rise by 20% versus the base case because of the long credit period, but many new customers are weak credits. Bad debts rise to 15%. The nominal cost of add'l credit is only 4.61%, which leads to few customers taking discounts. | ||||||||||
| Terrible economy. Leave credit terms unchanged. Due to the economy, the firm has more late payers, more bad debts, lower sales, and fewer discount customers. | ||||||||||
| Data set up to create scenarios (Milions of Dollars) | ||||||||||
| Active Scenario Shown: | Values for scenarios shown below. The Active Scenario shown in the grey area to the left. | |||||||||
| Use Scenario Manager to Pick a Scenario: Data, What-If-Analysis, Scenario Manager | ||||||||||
| Base Case | Base Case | Big Discount | Short Net Period | Long Net Period | Terrible Economy | |||||
| % customers who take discounts and pay in 1st month | 20% | 20% | 70% | 50% | 5% | 15% | ||||
| % who don't take discount and pay in 2nd month | 70% | 70% | 20% | 40% | 5% | 40% | Formula so that sum of %s adds to 100%. | |||
| % who don't pay in 1st or 2nd month. Lates + bad debts | 10% | 10% | 10% | 10% | 90% | 45% | ||||
| Total: Must equal 100% | 100% | 100% | 100% | 100% | 100% | 100% | ||||
| Purchases as a % of next month's sales | 60% | 60% | 60% | 60% | 60% | 60% | ||||
| Other payments | $30 | $30 | $30 | $30 | $30 | $35 | ||||
| Construction cost for new plant (Sept) | $150 | $150 | $150 | $150 | $150 | $200 | ||||
| Target cash balance | $10 | $10 | $10 | $10 | $10 | $10 | ||||
| Disc % for early pmt; reduces 1st month collections | 2% | 2% | 4% | 2% | 1% | 2% | ||||
| Discount period | 10 | 10 | 10 | 10 | 10 | 10 | ||||
| Net period | 60 | 60 | 30 | 30 | 90 | 60 | ||||
| Bad debt % (BD%); reduces 3rd month payments | 0% | 0% | 0% | 0% | 15% | 10% | ||||
| Sales % change from base-case forecast | 0% | 0% | 10% | -10% | 20% | -20% | ||||
| 1. EPC sells on terms of 2/10, net 60, meaning that it gives a discount to customers | ||||||||||
| who pay within 10 days, and non-discount customers have 60 days to pay. | ||||||||||
| 2. All discount customers pay on the 10th day, and other customers pay on the 60th | ||||||||||
| or 90th day, or are bad debts and never pay. No one pays after the 3rd month. | ||||||||||
| Figure 16-6 | ||||||||||
| EPC's Cash Budget, July - December, 2017 (Dollars in Millions) | ||||||||||
| Scenario: | Base Case | May | June | July | August | Sept | Oct | Nov | Dec | Jan |
| Panel A: Forecasted gross sales (manual inputs) | $200 | $250 | $300 | $400 | $500 | $350 | $250 | $200 | $200 | |
| Adjustment: % deviation from forecast | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | |
| Adjusted gross sales forecast | $200 | $250 | $300 | $400 | $500 | $350 | $250 | $200 | $200 | |
| Panel B: Collections on sales | ||||||||||
| During sales' month: | 0.2 | (Sales)(1 – Discount %) | $58.8 | $78.4 | $98.0 | $68.6 | $49.0 | $39.2 | ||
| During 2nd month: | 0.7 | (Prior month's sales) | $175.0 | $210.0 | $280.0 | $350.0 | $245.0 | $175.0 | ||
| Due in 3rd month: | 0.1 | (Sales 2 months ago) | $20.0 | $25.0 | $30.0 | $40.0 | $50.0 | $35.0 | ||
| Less bad debts (BD% × Sales 2 months ago) | $0.0 | $0.0 | $0.0 | $0.0 | $0.0 | $0.0 | ||||
| Total collections | $253.8 | $313.4 | $408.0 | $458.6 | $344.0 | $249.2 | ||||
| Panel C: Purchases: 60% of next month's sales | $180.0 | $240.0 | $300.0 | $210.0 | $150.0 | $120.0 | $120.0 | |||
| Panel D: Payments | ||||||||||
| Pmt for last month's purchases (30 days of credit) | $180.0 | $240.0 | $300.0 | $210.0 | $150.0 | $120.0 | ||||
| Wages and salaries | $30.0 | $40.0 | $50.0 | $40.0 | $30.0 | $30.0 | ||||
| Lease payments | $30.0 | $30.0 | $30.0 | $30.0 | $30.0 | $30.0 | ||||
| Other payments (interest on LT bonds, dividends, etc.) | $30.0 | $30.0 | $30.0 | $30.0 | $30.0 | $30.0 | ||||
| Taxes | $30.0 | $30.0 | ||||||||
| Payment for plant construction | $150.0 | |||||||||
| Total payments | $270.0 | $340.0 | $590.0 | $310.0 | $240.0 | $240.0 | ||||
| Panel E: Net cash flows | ||||||||||
| Assumed excess cash on hand at start of forecast period | $0.0 | |||||||||
| Net cash flow (NCF): Total collections – Total payments | −$16.2 | −$26.6 | −$182.0 | $148.6 | $104.0 | $9.2 | ||||
| Cumulative NCF: Prior month cum plus this month's NCF | −$16.2 | −$42.8 | −$224.8 | −$76.2 | $27.8 | $37.0 | ||||
| Panel F: Net cash position before financing or investing | ||||||||||
| Target cash balance | $10.0 | $10.0 | $10.0 | $10.0 | $10.0 | $10.0 | ||||
| Surplus cash or loan needed: Cum NCF – Target cash | −$26.2 | −$52.8 | −$234.8 | −$86.2 | $17.8 | $27.0 | ||||
| Panel G: Maximum loan requirements and investible funds | ||||||||||
| Max required loan (most negative on Row 396) | $234.8 | |||||||||
| Max investable funds (most positive on Row 396) | $27.0 | |||||||||
| Risk Analysis (not shown in textbook) | ||||||||||
| Scenario Analysis | ||||||||||
| Following are results based on the scenario analysis using the Scenario Summary feature: Data, What-If-Analysis, Scenario Manager, Summary. Selected results are shown below. | ||||||||||
| Scenario | Max Loan Requirement | |||||||||
| Base case | $234.8 | |||||||||
| Big discount | 103.5 | Note: after running Scenario Summary, we copied and pasted these as values from the Scenario Summary worksheet. | ||||||||
| Short net period | 199.3 | |||||||||
| Long net period | 550.7 | |||||||||
| Bad economy | 470.9 | |||||||||
| Sensitivity Analysis with Data Tables | ||||||||||
| Following are results based on the sensitivity analysis using one-way data tables that vary a single input. We examine the impact of changing the percent of customers who pay last (3rd month instead of 2nd month), the percent of bad loans, and variation in the sales forecast. | ||||||||||
| One-way data tables | ||||||||||
| % pay | Max loan | % bad | Max loan | % Sales | Max Loan | |||||
| late | $234.8 | debts | $234.8 | variation | $234.8 | |||||
| 0% | $215 | 0% | $235 | -30% | $311 | |||||
| 15% | $245 | 3% | $257 | -15% | $273 | |||||
| 30% | $275 | 6% | $280 | 0% | $235 | |||||
| 45% | $305 | 9% | $302 | 15% | $197 | |||||
| 60% | $335 | 12% | $325 | 30% | $158 | |||||
| Graph scales are set for the Base Case. Lines won't show for some scenarios because max loan is out of displayed range. | ||||||||||
| Following are results based on the sensitivity analysis using a two-way data table. We examine the impact of changing the percent of customers who pay last (3rd month instead of 2nd month) and the percent of bad loans. | ||||||||||
| Two-way data table | ||||||||||
| Maximum Required Loan | ||||||||||
| BD% | % Late Payers | |||||||||
| $234.8 | 0% | 15% | 30% | 45% | 60% | |||||
| 0% | $214.8 | $244.8 | $274.8 | $304.8 | $334.8 | |||||
| 3% | 237.3 | 267.3 | 297.3 | 327.3 | 357.3 | |||||
| 6% | 259.8 | 289.8 | 319.8 | 349.8 | 379.8 | |||||
| 9% | 282.3 | 312.3 | 342.3 | 372.3 | 402.3 | |||||
| 12% | 304.8 | 334.8 | 364.8 | 394.8 | 424.8 | |||||
| The higher the bad debt %, the larger the loan requirement. Similarly, the higher the % of late payers, the | ||||||||||
| larger the loan requirement. If EPC has high bad debts combined with a high % of late payers, it will have a | ||||||||||
| very high loan requirement. | ||||||||||
| 16-12 Short-Term Bank Loans | ||||||||||
| Traditional Bank Loan to Businesses | ||||||||||
| Regular (Simple) Interest | ||||||||||
| Most short-term working capital bank loans to businesess are documented with a promissory note that indicates an indexed interest rate that resets periodically, probably daily but perhaps weekly, or monthly, or quarterly. The index could be the prime rate, or LIBOR, or the T-bill rate. Typically, the loan has a maximum amount that can be borrowed, and the firm can "take down" the loan daily, up to the limit, and also repay it entirely or partially daily. Thus, the outstanding balance can vary daily. Interest could be added to the previous day's balance, but more typically it must be paid in cash periodically, say monthly. The interest is thus not compounded daily, but it is compounded periodically. Because interest is not compounded daily, the banks call this a "simple interest loan." That name is somewhat misleading, but it is the term used. The bank could use a 360- or a 365-day year. In our experience, 360 is used more often as this provides the higher return to the bank. | ||||||||||
| Example: Borrow $10,000 for one year @ 5.25% simple interest, paid monthly. | ||||||||||
| Inputs: | ||||||||||
| Amount borrowed | $10,000.00 | |||||||||
| Stated annual rate | 5.250% | |||||||||
| Days per year | 365 | |||||||||
| Days in month | 30.4166666667 | |||||||||
| Stated rate per day: Nominal rate/Days per year | 0.0001438 | |||||||||
| Results: | ||||||||||
| Interest cost: (Daily rate)(Number of days)(Amount borrowed) | $43.75 | |||||||||
| Months per year | 12 | |||||||||
| Effective annual interest rate (using Excel function): | 5.378% | |||||||||
| Effective annual interest rate using algebra: (1 + INom/12)^12 – 1 | 5.378% | |||||||||
| Add-On Loan Rate | ||||||||||
| With a monthly payment add-on loan, the amount borrowed is multiplied by the stated interest rate to get the total interest cost, and that amount is "added on" to the amount borrowed to find the stated amount of the loan. Next, the loan amount is divided by the number of months to find the monthly payment. The monthly rate is then found by determining the rate that causes the PV of the monthly payments to equal the amount actually borrowed. The monthly rate times 12 gives the nominal (APR) rate. The effective rate is somewhat higher. | ||||||||||
| If the loan is for more than a year, say 2 years, use the same procedures except use 24 rather than 12 for the number of months. The rate will turn out to be higher than the one for the 1-year loan because more interest is paid in advance. | ||||||||||
| Example: Borrow $10,000 for one year @ 5.25% simple interest, paid monthly. | ||||||||||
| 1-month | 1-Year | 2-Year | ||||||||
| Amount borrowed | $10,000.00 | $10,000.00 | $10,000.00 | |||||||
| Stated annual rate | 7.250% | 7.250% | 7.250% | |||||||
| Payments per year | 12 | 12 | 12 | |||||||
| Stated rate per month: Annual rate/12 | 0.604% | 0.604% | 0.604% | |||||||
| Months loan will be outstanding | 1 | 12 | 24 | |||||||
| Total interest: (Stated rate/month)(Borrowed)(no. of months) | $60.42 | $725.00 | $1,450.00 | |||||||
| Total loan: Amount borrowed + Total Interest | $10,060.42 | $10,725 | $11,450.00 | |||||||
| Monthly payment: Total loan / Months of loan | $10,060.42 | $893.75 | $477.08 | |||||||
| Rate/month: N=1,12, or 24, PV=-10000, PMT= varies. | 0.604167% | 1.093585% | 1.112845% | |||||||
| Nominal APR = monthly rate × 12 | 7.25% | 13.12% | 13.35% | |||||||
| EFF% = (1+monthly rate)^12 – 1 | 7.50% | 13.94% | 14.20% | |||||||
| Note that the nominal and effective interest rate increases as the term of the loan increases. More interest is charged in advance, a smaller percentage of the total amount borrowed is actually available for use, and thus the money that is available has a higher cost. | ||||||||||
Late Pay Effect
0 0.15 0.3 0.45 0.6 214.8 244.8 274.8 304.79999999999995 334.79999999999995% Late
Max Loan
Bad Debt Effect
0 0.03 0.06 0.09 0.12 234.79999999999995 257.29999999999995 279.79999999999995 302.29999999999995 324.79999999999995% Bad Debts
Max Loan
Sales Effect
-0.3 -0.15 0 0.15 0.3 311.35999999999996 273.07999999999993 234.79999999999995 196.51999999999992 158.23999999999984% Change in Sales
Max Loan
Combined Effect: Bad Debts and Late
0% late 0 0.03 0.06 0.09 0.12 214.8 237.3 259.8 282.3 304.8 15% late 0 0.03 0.06 0.09 0.12 244.8 267.3 289.8 312.3 334.8 30% late 0 0.03 0.06 0.09 0.12 274.8 297.3 319.8 342.3 364.8 45% late 0 0.03 0.06 0.09 0.12 304.79999999999995 327.29999999999995 349.79999999999995 372.29999999999995 394.79999999999995 60% late 0 0.03 0.06 0.09 0.12 334.79999999999995 357.29999999999995 379.79999999999995 402.29999999999995 424.79999999999995% Bad Debts
Max Loan Required
Late Pay Effect
0 0.15 0.3 0.45 0.6 214.8 244.8 274.8 304.79999999999995 334.79999999999995% Late
Max Loan
Bad Debt Effect
0 0.03 0.06 0.09 0.12 234.79999999999995 257.29999999999995 279.79999999999995 302.29999999999995 324.79999999999995% Bad Debts
Max Loan
Sales Effect
-0.3 -0.15 0 0.15 0.3 311.35999999999996 273.07999999999993 234.79999999999995 196.51999999999992 158.23999999999984% Change in Sales
Max Loan
Combined Effect: Bad Debts and Late
0% late 0 0.03 0.06 0.09 0.12 214.8 237.3 259.8 282.3 304.8 15% late 0 0.03 0.06 0.09 0.12 244.8 267.3 289.8 312.3 334.8 30% late 0 0.03 0.06 0.09 0.12 274.8 297.3 319.8 342.3 364.8 45% late 0 0.03 0.06 0.09 0.12 304.79999999999995 327.29999999999995 349.79999999999995 372.29999999999995 394.79999999999995 60% late 0 0.03 0.06 0.09 0.12 334.79999999999995 357.29999999999995 379.79999999999995 402.29999999999995 424.79999999999995% Bad Debts
Max Loan Required
Late Pay Effect
0 0.15 0.3 0.45 0.6 214.8 244.8 274.8 304.79999999999995 334.79999999999995% Late
Max Loan
Bad Debt Effect
0 0.03 0.06 0.09 0.12 234.79999999999995 257.29999999999995 279.79999999999995 302.29999999999995 324.79999999999995% Bad Debts
Max Loan
Sales Effect
-0.3 -0.15 0 0.15 0.3 311.35999999999996 273.07999999999993 234.79999999999995 196.51999999999992 158.23999999999984% Change in Sales
Max Loan
Combined Effect: Bad Debts and Late
0% late 0 0.03 0.06 0.09 0.12 214.8 237.3 259.8 282.3 304.8 15% late 0 0.03 0.06 0.09 0.12 244.8 267.3 289.8 312.3 334.8 30% late 0 0.03 0.06 0.09 0.12 274.8 297.3 319.8 342.3 364.8 45% late 0 0.03 0.06 0.09 0.12 304.79999999999995 327.29999999999995 349.79999999999995 372.29999999999995 394.79999999999995 60% late 0 0.03 0.06 0.09 0.12 334.79999999999995 357.29999999999995 379.79999999999995 402.29999999999995 424.79999999999995% Bad Debts
Max Loan Required
Late Pay Effect
0 0.15 0.3 0.45 0.6 214.8 244.8 274.8 304.79999999999995 334.79999999999995% Late
Max Loan
Bad Debt Effect
0 0.03 0.06 0.09 0.12 234.79999999999995 257.29999999999995 279.79999999999995 302.29999999999995 324.79999999999995% Bad Debts
Max Loan
Sales Effect
-0.3 -0.15 0 0.15 0.3 311.35999999999996 273.07999999999993 234.79999999999995 196.51999999999992 158.23999999999984% Change in Sales
Max Loan
Combined Effect: Bad Debts and Late
0% late 0 0.03 0.06 0.09 0.12 214.8 237.3 259.8 282.3 304.8 15% late 0 0.03 0.06 0.09 0.12 244.8 267.3 289.8 312.3 334.8 30% late 0 0.03 0.06 0.09 0.12 274.8 297.3 319.8 342.3 364.8 45% late 0 0.03 0.06 0.09 0.12 304.79999999999995 327.29999999999995 349.79999999999995 372.29999999999995 394.79999999999995 60% late 0 0.03 0.06 0.09 0.12 334.79999999999995 357.29999999999995 379.79999999999995 402.29999999999995 424.79999999999995% Bad Debts
Max Loan Required
Scenario Summary
| Scenario Summary | ||||||
| Current Values: | Base Case | Big Discount | Short Net Period | Long Net Period | Terrible Economy | |
| Created by Mike Ehrhardt on 6/22/2012 | Created by Mike Ehrhardt on 6/22/2012 | Created by Mike Ehrhardt on 6/22/2012 | Created by Mike Ehrhardt on 6/22/2012 | Created by Mike Ehrhardt on 6/22/2012 | ||
| Changing Cells: | ||||||
| $G$348 | Base Case | Base Case | Big Discount | Short Net Period | Long Net Period | Terrible Economy |
| $G$349 | 20% | 20% | 70% | 50% | 5% | 20% |
| $G$351 | 10% | 10% | 10% | 10% | 90% | 10% |
| $G$352 | 100% | 100% | 100% | 100% | 100% | 100% |
| $G$353 | 60% | 60% | 60% | 60% | 60% | 60% |
| $G$354 | $30 | $30 | $30 | $30 | $30 | $30 |
| $G$355 | $150 | $150 | $150 | $150 | $150 | $150 |
| $G$356 | $10 | $10 | $10 | $10 | $10 | $10 |
| $G$357 | 2% | 2% | 4% | 2% | 1% | 2% |
| $G$358 | 10 | 10 | 10 | 10 | 10 | 10 |
| $G$359 | 60 | 60 | 30 | 30 | 90 | 60 |
| $G$360 | 0% | 0% | 0% | 0% | 15% | 0% |
| $G$361 | 0% | 0% | 10% | -10% | 20% | 0% |
| Result Cells: | ||||||
| $H$396 | −$26.2 | −$26.2 | $0.8 | −$21.7 | −$103.2 | −$26.2 |
| $I$396 | −$52.8 | −$52.8 | $25.9 | −$30.8 | −$224.4 | −$52.8 |
| $J$396 | −$234.8 | −$234.8 | −$103.5 | −$199.3 | −$550.7 | −$234.8 |
| $K$396 | −$86.2 | −$86.2 | −$21.7 | −$118.0 | −$491.9 | −$86.2 |
| $L$396 | $17.8 | $17.8 | $40.1 | −$61.7 | −$276.1 | $17.8 |
| $M$396 | $27.0 | $27.0 | $29.4 | −$80.0 | −$198.2 | $27.0 |
| Notes: Current Values column represents values of changing cells at | ||||||
| time Scenario Summary Report was created. Changing cells for each | ||||||
| scenario are highlighted in gray. |
16-3
| SECTION 16-3 | |
| SOLUTIONS TO SELF TEST | |
| A company has $20 million in inventory, $5 million in receivables, and $4 million in payables. Its annual sales revenue is $80 million and its cost of goods sold is $60 million. What is its CCC? | |
| Inventory | $20 |
| Receivables | $5 |
| Payables | $4 |
| Sales | $80 |
| COGS | $60 |
| Inventory conversion period = | 121.67 |
| Average collection period = | 22.81 |
| Payables deferral period = | 24.33 |
| Cash conversion cycle = | 120.15 |
16-4
| SECTION 16-4 | |
| SOLUTIONS TO SELF TEST | |
| A company has $20 million in cost of goods sold and an inventory turnover ratio of 2.0. If it can reduce its inventory and improve its inventory turnover ratio to 2.5 with no loss in units sold and no change in cost of goods sold, by how much will FCF increase? | |
| Inputs (Dollars in Millions) | |
| Cost of goods sold (COGS) | $20 |
| Old inventory turnover ratio | 2.0 |
| New inventory turnover ratio | 2.5 |
| Intermediate Calculations | |
| Old inventory = COGS / (Old Inv. Turnover) = | $10.00 |
| New inventory = COGS / (New Inv. Turnover) = | $8.00 |
| Final Result | |
| Increase in available free cash flow | $2.00 |
16-5
| SECTION 16-5 | |
| SOLUTIONS TO SELF TEST | |
| A company has annual sales of $730 million. If its DSO is 35, what is its average accounts receivables balance? | |
| Annual sales | $730 |
| DSO | 35.0 |
| Daily sales | $2.00 |
| Accounts receivables = DSO × Daily Sales | $70.00 |
16-6
| SECTION 16-6 | ||||
| SOLUTIONS TO SELF TEST | ||||
| A company has credit terms of 2/12 net 28. What is the nominal annual cost of trade credit? The effective annual cost? | ||||
| Discount percentage | 2% | |||
| Dicount period | 12 | |||
| Regular credit period | 28 | |||
| Nominal annual cost of credit = | Cost per period | × | Number of periods per year | |
| 0.0204 | × | 22.8125 | ||
| Nominal annual cost of credit = | 46.6% | |||
| Effective annual cost of credit = | [ (1 + Cost per period) | ^ | (Number of periods per year) ] | – 1 |
| 1.0204 | ^ | 22.8125 | – 1 | |
| 1.5855 | – | 1 | ||
| Effective annual cost of credit = | 58.5% |
16-12
| SECTION 16-12 | |||
| SOLUTIONS TO SELF TEST | |||
| Simple interest loan | |||
| If a firm borrowed $500,000 at a rate of 10% simple interest with monthly interest payments and a 365-day year, what would be the required interest payment for a 30-day month? | |||
| Nominal rate | 10% | ||
| Amount borrowed | $500,000.00 | ||
| Days/year | 365 | ||
| Rate/day = nominal rate / 365 = (fraction, not %) | 0.0002739726 | ||
| Interest / month = | Amount borrowed × rate/day × 30 = | $4,109.59 | |
| If interest must be paid monthly, what would be the effective annual rate? | |||
| If interest had to be paid daily, the effective rate would be found as follows: | |||
| Effective rate = (1 + nom rate/365)^365 – 1.0 = | 0.1051557816 | or | 10.52% |
| However, interest must be paid monthly, so the effective rate is lower, found as follows: | |||
| Effective rate = (1 + nom rate/12)^12 – 1.0 = | 0.1047130674 | or | 10.47% |
| It would be easy to go wrong on this problem for two reasons. First, you must recognize that the monthly interest payment will vary depending on how many days are in the month, and second, you must differentiate from daily interest compounding and monthly compounding. | |||
| Add-on Loan | |||
| If this loan had been made on a 10% add-on basis, payable in 12 end-of-month installments, what would be the monthly payments? | |||
| Find the total interest: | 0.1 × $500,000 = | $50,000.00 | |
| Find the total amount of the loan: | $500,000 + $50,000 = | $550,000 | |
| Find the monthly payments: | $550,000/12 = | $45,833.33 | |
| What is the annual percentage rate? | |||
| Use the RATE function to find the rate that causes the PV of the monthly payment stream to equal the amount borrowed. This is the nominal rate. | |||
| APR = Rate = | 17.97% | ||
| What is the effective annual rate? | |||
| EFF using algebraic formula = | 0.1953 | = | 19.53% |
| EFF using Excel function = | 0.1953 | = | 19.53% |
