Please post all Excel formulas.
Prob. 1 Prob. 2 a) Effective Annual Rate (EAR) b) Average Collection Period c) One-Time Client Notional purchase Discount %) Days difference Gross revenue Avg. receivables before new policy % paying early Avg, receivables after new policy Change in receivables Cost of capital Projected savings in capital costs minus: discounts Projected savings net of discounts Gross margin Gross revenues must rise by: - in dollars - in percent Repair cost Default probability NPV of client Break-even probability Extend credit if probability of getting paid is higher than Discount ($) Rate(%) Days difference in 1 year EAR Apache sells maintenance services to various private jet operators. For these, Apache's demands payment within 30 days. Apache is considering changing this policy to 1/5, net 30. What is the implicit effective annual rate in this payment policy? Apache's maintenance service business grosses some $20M per year before discounts and its average days receivable is 30 (unlike the overall business where this number is -40). If 25% of Apache's clients opt to pay earlier and get the discount, what will be the change in the service business's receivables? If Apache's cost of capital is 8%, what are the projected savings of this change in policy? If Apache's gross margin is 40%, by how much will gross dollar revenues have to rise to offset the loss from discounts? In percent? A new client from out of town is quoted $6,000 for a repair. The service people ask you to approve this. You do a quick check on the client and assess a 15% default risk. What is the NPV of the client? What is the break-even probability? What is the minimum probability of collecting for you to approve the service? 1Q19 Income Statement (in M$) Sales Cost of Goods Sold Gross Margin Sales, General, and Admin. Interest Expense Taxable income Taxes Net Income Jan 123 78 45 12 3 30 6 24 Feb 131 83 48 13 2 33 7 26 Mar 144 89 55 12 2 41 9 32 1Q19 Balance Sheet (in M$) Jan 625 160 105 890 1,176 2,066 120 Feb 814 177 123 1,114 1,176 2,290 122 Mar 900 200 124 1.224 1,176 2,400 131 Cash Receivables Inventory Current Assets PP&E Total Assets Payables Notes Payable Accruals LTD Current Liabilities LTD Equity Total L&E 38 50 30 50 200 300 1,566 2,066 34 50 206 300 1,784 2,290 219 300 1 881 2,400 Prob. 1 Prob. 2 a) Effective Annual Rate (EAR) b) Average Collection Period c) One-Time Client Notional purchase Discount %) Days difference Gross revenue Avg. receivables before new policy % paying early Avg, receivables after new policy Change in receivables Cost of capital Projected savings in capital costs minus: discounts Projected savings net of discounts Gross margin Gross revenues must rise by: - in dollars - in percent Repair cost Default probability NPV of client Break-even probability Extend credit if probability of getting paid is higher than Discount ($) Rate(%) Days difference in 1 year EAR Apache sells maintenance services to various private jet operators. For these, Apache's demands payment within 30 days. Apache is considering changing this policy to 1/5, net 30. What is the implicit effective annual rate in this payment policy? Apache's maintenance service business grosses some $20M per year before discounts and its average days receivable is 30 (unlike the overall business where this number is -40). If 25% of Apache's clients opt to pay earlier and get the discount, what will be the change in the service business's receivables? If Apache's cost of capital is 8%, what are the projected savings of this change in policy? If Apache's gross margin is 40%, by how much will gross dollar revenues have to rise to offset the loss from discounts? In percent? A new client from out of town is quoted $6,000 for a repair. The service people ask you to approve this. You do a quick check on the client and assess a 15% default risk. What is the NPV of the client? What is the break-even probability? What is the minimum probability of collecting for you to approve the service? 1Q19 Income Statement (in M$) Sales Cost of Goods Sold Gross Margin Sales, General, and Admin. Interest Expense Taxable income Taxes Net Income Jan 123 78 45 12 3 30 6 24 Feb 131 83 48 13 2 33 7 26 Mar 144 89 55 12 2 41 9 32 1Q19 Balance Sheet (in M$) Jan 625 160 105 890 1,176 2,066 120 Feb 814 177 123 1,114 1,176 2,290 122 Mar 900 200 124 1.224 1,176 2,400 131 Cash Receivables Inventory Current Assets PP&E Total Assets Payables Notes Payable Accruals LTD Current Liabilities LTD Equity Total L&E 38 50 30 50 200 300 1,566 2,066 34 50 206 300 1,784 2,290 219 300 1 881 2,400
