Assignment Excel Case 4

Capital Budgeting

Background

Jack Turner is your client. He recently graduated from College and is considering purchasing a business. While in College, Jack drove a cab to pay his way through school. Jack learned a great deal about the cab business and the transportation business in general. He is aware of new transportation modes like UBER.

Jack has discovered a business opportunity but is uncertain about the financial aspects of the opportunity. Jack believes the type of transportation service offered below will compete very effectively with newer transportation modes like UBER. You are his consultant. You have worked collaboratively with Jack and you and he believe the projections below are realistic and achievable.

The Opportunity

KCI Express is owned by Henry Knox. Henry is 68 and wants to retire. Henrys business consists of providing transportation by appointment for individuals from the Omaha area who need transportation to KCI International airport in Kansas City. Henry owns two 17-passenger vans and four 6-passenger mini vans. The chief advantage of purchasing KCI Express will be the established customer base and the established reputation of KCI express which is excellent.

KCI Express has the following financial data:

Assets (At estimated market value)

Two 17-Passenger Vans $20,000

Four 6-Passenger Mini Vans 30,000

Phone Equipment 14,000

Current Capacity

58 Passengers per day each way.

2. 40,000 Passengers per year (Because of occasional maintenance this number is less than daily capacity multiplied by 365)

The business operates 365 days per year.

Revenue

The business charges a standard one-way fee of $25 for a trip from Omaha to KCI or from KCI to Omaha. All sales are cash sales.

2016 Performance (Excludes payments associated with owner):

Sales (16,000 passengers @ $25) $400,000

Dispatcher Salaries (Fixed) 56,000

Drivers Salaries (37% of Sales) 148,000

Payroll Taxes (11% of Salaries) 22,440

Other Employee Benefits (10% of Salaries) 20,400

Gas, Oil, & Maintenance (8% of Sales) 32,000

Insurance (Fixed) 8,000

Rent & Utilities (Fixed) 18,000

Advertising - Yellow pages (Fixed) 3,000

Advertising - Newspaper & radio (3% of Sales) 12,000

Miscellaneous (Fixed) 15,000

Total Cash Expenses 334,840

Sales	$400,000
Dispatcher Salaries (Fixed)	56,000
Drivers Salaries (37% of Sales)	148,000
Payroll Taxes (11% of Salaries)	22,440
Other Employee Benefits (10% of Salaries)	20,400
Gas, Oil, & Maintenance (8% of Sales)	32,000
Insurance (Fixed)	8,000
Rent & Utilities (Fixed)	18,000
Advertising - Yellow pages (Fixed)	3,000
Advertising - Newspaper & radio (3% of Sales)	12,000
Miscellaneous (Fixed)	15,000

-3-

The New Business

The new Business will be called Turners KCI Express, Inc.

For 2017, the volume of passengers is expected to be 16,000 (the same as the previous year)

Simplifying assumption - All cash receipts and cash payments are assumed to happen at the end of the year. (This will simplify Net Present Value Computations)

Starting in the year, 2017, the new owner receives a $35,000 yearly salary. This salary is subject to the same payroll taxes and employee benefit costs as the other employees.

The Business is organized as a Corporation and pays a tax at the rate of 22%.

For the new owner, the cost of the new vehicles will be $50,000. Vehicles will be depreciated on a straight line basis. The current vehicles have a useful life of 2 years. Then they will need to be replaced at a cost of $145,000. The new vehicles will have a life of 5 years. There is no salvage value.

The phone equipment (cost $14,000) will last 7 years. Use straight line depreciation.(zero salvage value)

Fixed Salaries (including the owners salary) will increase at the rate of 3% per year. The 3% increase will apply to Dispatcher Salaries in the Year 2017. The 3% increase will not apply to the Owners salary until the Year 2018.

Variable Costs will maintain their current relationship to sales.

Other Fixed Costs will not change.

The advertising and promotion strategies will increase the number of passengers by 4% per year. The first increase will take place in 2018.

The Transportation Fee will remain at $25 for the year 2017. Thereafter it will increase at the rate of $1 per year.

All cash expenses are paid in the same year in which they are incurred.

13. Variable costs as a percentage of sales will not change.

-3-

Required:

Prepare a Budgeted Income Statement for the next 7 years (Starting with 2017).

Prepare a Cash Flow Analysis for the next 7 years. (Starting with 2017).Your cash flow analysis should be linked to your income statement so that changes on the income statement automatically flow through to the cash flow statement. A simplified Cash Analysis may be developed from the Income Statement.See the sample.

Compute the net present value of the business assuming that the owner wants a 15% rate of return before financing costs are considered.Use the cash flow projections for the next 7 years as the basis for the present value computations.You must Use the NPV function in the spreadsheet to do this requirement.

If Jack Turner can purchase the business for $125,000, and if the above projections are accurate, what will be the businesses internal rate of return?Hint - Use trial and error to discover the Internal Rate of Return.Alternatively, you may use the function for the Internal Rate of Return (IRR) to calculate the internal rate of return for this company.See the instructor for help.

Spreadsheet Activities

The 2016 data should be organized in the first column of the data area of your worksheet. The 2016 data represents your initial inputs.

You will need to create a 7 year cash flow projection Your 7 year projection is for the years 2017 through 2023

You will need to compute net present value. This should be done with the Net present value function in Excel (NPV).

You will need to calculate the internal rate of return. This can be done with your own formula or with a financial function - The function is easier than your own formula see the instructor for help)