PLEASE SHOW FORMULAS IN EXCEL

1.You are in a team compiling a report to send to your firm's bank. You are supposed to take the firm's last three months and provide the following for each month: Days receivable Days inventory Operating cycle Days payable Cash cycle You have all the financials in the spreadsheet so all you need to do is make the calculations.

2.ABC Maintenance Service A. ABC FBO sells maintenance services to various private jet operators.For these, it demands payment within 30 days. It is considering changingthis policy to 0.66%/7, net 30. What is the implicit annual rate in the new policy? Use a notional purchase of $10,000. B.ABC's maintenance service business grosses some $22M per year beforediscounts and its average days receivable is 30. If 15% of its clients opt tofollow the new policy, what will be the change in receivables? If ABC's WACCis 7.0%, what are the projected savings of the new policy? If its gross marginis 22%, by how much will gross dollar revenues have to rise to offset the lossfrom discounts? In percent?

in MS Nov. 44,438 Dec. 47,007 Oct. Operating Revenues 41,138 Operating Expenses: Salaries, wages and benefits 10,058 Aircraft fuel and taxes 6,756 Landing fees and other rents 1.501 Depreciation/amortization 2.222 Maintenance and repairs 1,591 Other operating expenses 13,044 Total Operating Expenses 35,172 Operating Income 5,966 Other income (expense) (466) Income before taxes 5,500 Income tax expense 2,295 Net Income 3,205 10,743 9,020 1,662 2.329 1,575 13,845 39,174 5,264 (113) 5.151 1.216 3,935 11,225 8,519 1,762 2,581 1,751 14,551 40,389 6,618 (420) 6,198 1,431 4,767 in MS Oct. Nov. Dec. Cash and equivalents 1,565 1,581 2.882 Accounts receivable 23.141 23,327 28,546 Fuel inventory 5,922 5,980 5,730 Parts and supplies inventory 463 468 521 Prepaid expenses 1,406 1,420 1.262 Total Current Assets 32,497 32,775 38,941 Property, Plant & Equipment 44,158 44,600 48,337 Allowance for depreciation 15.823 15,981 17,027 Net PP&E 28,335 28,618 31,310 Goodwill 9.781 9.879 9,781 Other assets 15,810 15.968 15.192 Total Assets 86,423 87,241 95,224 in MS Oct. Nov. Dec. Accounts Payable 12,976 13,006 13,266 Air traffic liability 4,661 4,708 5,116 Accrued liabilities 1.117 1.128 1,078 Curr. maturities of LTD 1,518 1,533 2,287 Other current liabilities 1,694 1,892 1,543 Total Current Liabilities 21,966 22,267 23,290 LTD and lease obligations 8,253 8,336 8,873 Pension obligations 9.163 9.255 8,452 Deferred revenue 3.652 3,689 3,509 Non-current operating lease 5.801 5.859 5.294 Other long term liabilities 1,132 1,143 2.842 Stockholder's equity 36.456 36,693 42,964 Total Liab. and equity 86,423 87.241 95,224 Key Ratios Oct. Nov. Dec. Days Receivable Days Inventory Operating Cycle Days Payable Cash Cycle A) Effective Annual Rate (EAR) B) Average Collection Period Notional purchase Discount) Days difference Discount (S) Rate(%) Days difference in 1 year 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 EAR