Assume that you work for Account Able, CPAs, LLP. Mike Lewis is a tax client of Will B. Able, a senior partner in the firm. Mike has started and sold several businesses over the last 10 years. He recently sold his most recent business and he is ready to work on his next start-up. Yesterday, Mike had lunch with Will to discuss Mikes next venture. After lunch Will called you into his office to ask you for your assistance in helping Mike plan his way forward.

Following is the plan that Mike sketched out for Will over lunch, based on Wills notes.

Mike Lewis is considering starting a shuttle service between downtown hotels and the airport, beginning September 1, 2022. He is close to signing a deal for an exclusive arrangement with a major hotel in downtown Portland to provide round trip service between the airport and the hotel for its customers. Mike will be able to bill this major hotel $40,000 a month and the hotel will pay the following month. Mikes exclusive agreement with the major hotel is expected to use only about 45% of the companys capacity. The hotel is willing to let the company carry other passengers as long as he follows a regular schedule, and the hotels customers have first access to capacity.

Mike will have to operate three vans that will run between 6 a.m. and midnight. that he will have to pay the following monthly costs in the month incurred:

• Automotive and airport leases, $25,500.

• Gross payroll costs paid to drivers, $28,000.

• Insurance and other operating overheads, $2,500.

• Administrative overheads per month, $3,000.

He estimated

Mike will also have to incur the following costs and he expects that he can pay these costs in the month after the costs are incurred.

• The airport lease also includes a variable portion of 1% of accrual basis sales. The 1% of

  sales is paid in the first 10 days following the month when the sales are earned.

• Office equipment will need to be purchased in July 2021 for $45,000.

• Monthly fuel costs are estimated at $10,750.

• Maintenance costs are estimated as follows: July 2022 and August 2022 ($0), September

  2022 ($500), October 2022 ($750), November 2022 through June 2023 ($1,000), July 2023 through December 202 ($1,500).

  For riders that are in addition to the riders on the hotel contract, Mike expects that he can charge customers $15 for a one-way trip from the airport to downtown hotels, and $25 for a round trip ticket between the airport and downtown hotels. Cash from these customers will be collected in the month the trips are sold. Mike expects that he will be able to develop business as laid out in Exhibit 1 below. Once he reaches December ridership numbers Mike feels that he will be close to capacity. However, he also realizes that the travel business is seasonal. Based on information from the Port Authority, he is projecting ridership as follows (plus or minus 15%):

Mike will be able to start the business with $100,000 in cash. He feels that he should maintain minimum cash balances of $20,000. If he needs to borrow, he has a committed line of credit from a bank for borrowing up to $35,000 at prime rate plus 6% (use the prime rate as of December 31, 2021). At a minimum he is required to pay interest monthly. Interest is accrued and paid based on the balance outstanding at the end of the previous month. If there is adequate cash flow to repay debt, principle and interest should be paid with any excess of cash flow over expenditures. In the long run, principle must be repaid within a year. In fact, the loan must be rested with a zero balance for at least 30 consecutive days in a 12 month period. Mike can also potentially borrow up to $25,000 from a relative under at the same rate, prime rate plus 6%, and similar terms. However, in order to obtain a formal loan commitment he needs a 16 month cash flow projection for the bank.

Required:

Will needs to have you prepare the following deliverables in the next 24 hours, after which he wants to meet with you again.

1. Prepare the required cash flow projection in Excel using Mikes assumptions. Use the following format.

2. Take time to determine what assumptions, if any, are missing from the assumptions outlined by Mike above. Be prepared to discuss missing assumptions, and reasonable values for the missing assumptions with Will during your next meeting.

3. What are other possible scenarios? Be prepared to use Mikes assumptions and then factor in increases or decreases in ridership, increases or decreases in fuel costs, etc. (i.e. build the spreadsheet in such a way as to allow for maximum flexibility to facilitate your conversation with Will).

Round Trip Trips Sold 2022 Exhibit 1: Projected Trips One Way Trips Sold 2022 January February March April May June July August September 800 October 1,100 November 1,500 December 2,000 One Way Trips Sold 2023 1,100 1,100 1,750 1,200 1,200 1,750 1,750 1,750 1,200 1,200 1,750 2,000 Round Trip Trips Sold 2023 800 800 1,300 850 850 1,300 1,300 1,300 850 850 1,300 1,500 700 800 1,100 1,500 July August September etc. Cash Flow Details Cash Receipts List Cash Disbursements List Cash Flow Summary Cash at the Beginning of the Month $ 100,000 Total Cash Receipts Total Cash Disbursements Estimated Ending Cash Estimated Borrowing Estimated Repayment Cash at the End of the Month Borrowing Outstanding Round Trip Trips Sold 2022 Exhibit 1: Projected Trips One Way Trips Sold 2022 January February March April May June July August September 800 October 1,100 November 1,500 December 2,000 One Way Trips Sold 2023 1,100 1,100 1,750 1,200 1,200 1,750 1,750 1,750 1,200 1,200 1,750 2,000 Round Trip Trips Sold 2023 800 800 1,300 850 850 1,300 1,300 1,300 850 850 1,300 1,500 700 800 1,100 1,500 July August September etc. Cash Flow Details Cash Receipts List Cash Disbursements List Cash Flow Summary Cash at the Beginning of the Month $ 100,000 Total Cash Receipts Total Cash Disbursements Estimated Ending Cash Estimated Borrowing Estimated Repayment Cash at the End of the Month Borrowing Outstanding