FIN. 550 WEEK 5 .EXCEL HOMEWORK -due 5/6/18 RUSH
ms.zas
JobAid10-51EXAMP.EXCEL.xlsx10-5
| The idea behind this question is to determine if the off balance sheet items will affect the credit rating | |||||||||||||||
| of the bond. Right now the bonds are rated 'A', however, the off balance sheet items may affect this | |||||||||||||||
| rating. | |||||||||||||||
| See my notes for each of the adjustments. There is a red tab for each note. | |||||||||||||||
| Off-Balance Sheet Item | EBITDA Impact | Interest Expense Impact | Interest Coverage Impact | ||||||||||||
| Pre-adjustment | $4,450,000 | $942,000 | 4.72 islan_000: islan_000: This is the simple equation of EBITDA divided by Interest expense. |
||||||||||||
| - Guarantee of debt (n/a) | $0 | $0 | |||||||||||||
| - Sale of receivables (1) | $40,000 | $40,000 islan_000: islan_000: In adjusting for the sold accounts receivable and treating it as a secured loan, operating income (EBITDA) should be increased by the interest income ($40,000). This treatment assumes that the “loan proceeds” from the financed receivables would be invested to generate interest income (for simplicity's sake, the same rate of interest is assumed, but an alternative rate could be justified). The financing costs of the loan ($40,000) would be added to interest expense and will not affect EBITDA. |
|||||||||||||
| - Operating lease (2) | $0 | $614,400 islan_000: islan_000: In adjusting for the operating lease and treating it as a capital lease, interest expense for the first year of the lease ($614,400) should be added to adjusted interest expense. |
|||||||||||||
| Net Adjustment v | $40,000 | $654,400 islan_000: islan_000: This is the adjustment to the interest expense based on the sale of receivables and the off-balance sheet operating expense |
|||||||||||||
| Post-adjustment | $4,490,000 | $1,596,400 islan_000: islan_000: Add the net adjustment to the current interest expense and you have the post-adjustment interest expense, which is a result of the off-balance sheet items. | 2.81 islan_000: islan_000: As a result, the new interest coverage ratio is 2.81 |
||||||||||||
| (1) Sale of receivables = Interest income (EBITDA) & interest expense: $500,000 @ 8% = $40,000. | |||||||||||||||
| (2) Operating lease = Interest expense: $6,144,000 @ 10% = $614,400. | |||||||||||||||
| In adjusting for the sold accounts receivable and treating it as a secured loan, operating income (EBITDA) should be increased by the interest income ($40,000). | |||||||||||||||
| This treatment assumes that the "loan proceeds" from the financed receivables would be invested to generate interest income | |||||||||||||||
| (for simplicity's sake, the same rate of interest is assumed, but an alternative rate could be justified). | |||||||||||||||
| The financing costs of the loan ($40,000) would be added to interest expense and will not affect EBITDA | |||||||||||||||
| 5a(i) | 2.81 | ||||||||||||||
| Decrease from 4.72 | |||||||||||||||
| Off-Balance | Long-term Debt Impact | Equity | Leverage | ||||||||||||
| Sheet Item | Impact | Impact | |||||||||||||
| Pre-adjustment | $10,000,000 | $33,460,000 | 0.30 | ||||||||||||
| - Guarantee of debt (1) | $995,000 islan_000: islan_000: The company is guaranteeing debt, which is an obligation. While it may not be a direct obligation, if they are required to, they must pay this debt. Therefore, it should be shown as an obligation. | $0 | |||||||||||||
| - Sale of receivables (n/a) | $0 | $0 | |||||||||||||
| - Operating lease (2) | $5,758,400 islan_000: islan_000: The company has also entered into a non-cancelable operating lease in the amount of $6,144,000. The $5,758,400 is net of the current portion due in the amount of $385,000. This is calculated as the amount due of $1,000,000 minus the interest expense of $614,400 | |0 | |||||||||||||
| Net Adjustment | $16,753,400 islan_000: islan_000: The new debt level should add the existing long term debt plus the debt being guaranteed plus the discounted value of the operating lease | $0 | |||||||||||||
| Post-adjustment | $16,753,400 | $33,460,000 | 0.50 islan_000: islan_000: The new debt to equity is much higher. |
||||||||||||
| (1) Guarantee of debt = Long-term debt: $995,000. | |||||||||||||||
| (2) Operating lease = Long-term debt: $6,144,000 (PV of lease) - $385,600 (current portion) = $5,758,400. | |||||||||||||||
| 5a(ii) the new debt to equity ratio should be: | 0.50 | ||||||||||||||
| This is an increase from .3 | |||||||||||||||
| Off-Balance | Current Assets Impact | Current Liabilities Impact | Current Ratio Impact | ||||||||||||
| Sheet Item | |||||||||||||||
| Pre-adjustment | $4,735,000 | $4,500,000 | 1.05 | ||||||||||||
| - Guarantee of debt (n/a) | 0 | 0 | |||||||||||||
| -Sale of receivables (1) | $500,000 | $500,000 islan_000: islan_000: If Montrose sells their receivables, they do so with recourse, which means that they may have to buy them back. As such, this becomes a current liability. |
|||||||||||||
| - Operating lease (2) | 0 | $385,600 islan_000: islan_000: This is the net payment of principal on the operating lease as shown above. |
|||||||||||||
| Net Adjustment | $500,000 | $885,600 islan_000: islan_000: Add the two off balance sheet liabilities for a total of $885,600. |
|||||||||||||
| Post-adjustment | $5,235,000 | $5,385,600 islan_000: islan_000: The new current liabilities as a result of the off balance sheet items is $5,385,600. | 0.97 islan_000: islan_000: The resulting current ratio is now lower than the original current ratio. |
||||||||||||
|
islan_000: islan_000: The company has also entered into a non-cancelable operating lease in the amount of $6,144,000. The $5,758,400 is net of the current portion due in the amount of $385,000. This is calculated as the amount due of $1,000,000 minus the interest expense of $614,400 |
islan_000: islan_000: The new debt level should add the existing long term debt plus the debt being guaranteed plus the discounted value of the operating lease |
islan_000: islan_000: The new debt to equity is much higher. |
islan_000: islan_000: This is the simple equation of EBITDA divided by Interest expense. |
islan_000: islan_000: In adjusting for the sold accounts receivable and treating it as a secured loan, operating income (EBITDA) should be increased by the interest income ($40,000). This treatment assumes that the “loan proceeds” from the financed receivables would be invested to generate interest income (for simplicity's sake, the same rate of interest is assumed, but an alternative rate could be justified). The financing costs of the loan ($40,000) would be added to interest expense and will not affect EBITDA. |
islan_000: islan_000: In adjusting for the operating lease and treating it as a capital lease, interest expense for the first year of the lease ($614,400) should be added to adjusted interest expense. |
islan_000: islan_000: This is the adjustment to the interest expense based on the sale of receivables and the off-balance sheet operating expense |
islan_000: islan_000: Add the net adjustment to the current interest expense and you have the post-adjustment interest expense, which is a result of the off-balance sheet items. |
islan_000: islan_000: As a result, the new interest coverage ratio is 2.81 |
islan_000: islan_000: If Montrose sells their receivables, they do so with recourse, which means that they may have to buy them back. As such, this becomes a current liability. |
islan_000: islan_000: This is the net payment of principal on the operating lease as shown above. |
islan_000: islan_000: Add the two off balance sheet liabilities for a total of $885,600. |
islan_000: islan_000: The new current liabilities as a result of the off balance sheet items is $5,385,600. | (1) Sale of receivables = Accounts receivable (current assets) & notes payable (current liabilities): $500,000. | ||
| (2) Operating lease = Current portion of lease obligation (current liabilities): $1 ,000,000 (annual payment) - $614,400 (interest expense) = $385,600 (principal payment). | |||||||||||||||
| 5a(iii): The new current ratio should be: | 0.97 | ||||||||||||||
| 5b. Based on the different ratios, and then comparing them to what they should be in Exhibit 10.13 in order | |||||||||||||||
| to have an 'A' rating, it appears as if Montrose does not deserve an A rating. They should be rated BBB | |||||||||||||||
| Summary: | |||||||||||||||
| 5a(i) | 2.81 | ||||||||||||||
| 5a(ii) | 0.5 | ||||||||||||||
| 5a(iii) | 0.97 | ||||||||||||||
| 5b | BBB |
