For a good rating, please post all Excel formulas.

Prior information:

2. 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? 3. 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? 4. 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? 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 (S) Rate(%) Days difference in 1 year EAR 78 Problem Set 8 - Short-Term Finance 1. Apache's first quarter's 2019 financials are being prepared and the CFO wants you to use them to calculate days receivable, days inventory, operating cycle, days payable, and cash cycle for each of the three months of 1019. The financials are below: 1019 Income Statement (in M$) Jan Feb Mar Sales 123 131 144 Cost of Goods Sold 83 89 Gross Margin 45 48 55 Sales, General, and Admin 12 13 12 Interest Expense 3 2 2 Taxable income 30 33 41 Taxes 6 7 9 Net Income 24 26 32 1919 Balance Sheet (in M$) Jan Feb Mar Cash 625 814 900 Receivables 160 177 200 Inventory 105 123 124 Current Assets 890 1.114 1.224 PP&E 1,176 1.176 1.176 Total Assets 2,066 2.290 2,400 Payables 120 122 131 Notes Payable Accruals 30 34 38 LTD 50 50 50 Current Liabilities 200 219 LTD 300 300 300 Equity 1.566 1.784 1.881 Total L&E 2,066 2,290 2,400 206 B D E F G H L 1019 Income Statement (in M$) Jan. Feb. Sales 123 131 Cost of Goods Sold 78 83 Gross Margin 45 48 Sales, general & admin 12 13 Interest expense 3 2 Taxable income 30 33 Taxes 6 7 Net Income 24 26 Mar. 144 89 55 12 2 41 9 32 1919 Balance sheet (in M$) Jan. Feb. Cash 625 814 Receivbles 160 177 Inventory 105 123 Curr. Assets 890 1114 PP&E 1176 1176 Total Assets 2066 2290 Mar. 900 200 124 1224 1176 2400 Key Ratios Jan. 40.33 41.73 82.06 47.69 34.36 Feb. 37.83 41.49 79.33 41.16 38.17 Payables Accruals LTD Curr. Liabilities LTD Equity Total L&E Mar. 43.00 43.19 86.25 45.63 40.62 120 30 50 200 300 1566 2066 Days Receivable Days Inventory Operating Cycle Days Payable Cash Cycle 122 34 50 206 300 1784 2290 131 38 50 219 300 1881 2400 Key Ratios (Excel Formulas Ref.) Jan. Feb. Mar. Days Receivable (a) (H5/C4)*31 (15/04)* 28 (15/E4)*31 Days Inventory (b) (H6/C5)*31 (16/05)*28 (16/ES)"31 Operating Cycle a+b a+b a+b Days Payable (c) (H11/C5)*31 (111/05)* 28 (11/E5)*31 a+b+c a+b+c a + b + c Formulas. Days Receivable = ( Receivable / Sales) Days in a period. Days Inventory = (Inventory / Cost of Slaes) Days in a period. Operating cycle = Days Receivable + Days Inventory. Days Payable = (Payable / Cost of Slaes) * Days in a period. Cash Cycle = Operating Cycle - Days Payable. Note : Days in Jan. & Mar. are 31 days and Feb. are 28 days in 2019. Cash Cycle Note : Please Comment down for any further clarifications