Field Services (Buses - Capacity and Utilization of Seats) Capital Budget - Case Study

This field services case study is that of a transportation NPV (net present value) model for bus purchases.  There are initially two options, that of a purchasing of six, 32-Passenger buses or four, 52-Passenger buses, but I have included the use of solver to maximize the NPV if we were to select a mix of the two types using the same budget constraints.  I always try to include additional analytics into the options, but I will walk through the analysis and the details. 

The buses will be used on a full day schedule of 480 miles, a 16 hour day, of which is included a 4 hour rush hour segment that the buses will operate at full capacity.   

Below are the specifications collected for each of the buses.

The available information suggests that the budget is at least $720,000 given that we are able to purchase six 32-Passenger buses at $120,000 each.  This will provide the budget constraint for the maximization of NPV when running the solver for mixed purchases. 

We will use a straight line depreciation over 8 years with the respective salvage values just for modeling.  The wages per hour are assumed to be the same for a full-time driver and a part-time driver and the operational expenses are per bus per annum. 

Next is the daily schedule for the operations of the buses and the projected passengers for the times during the day.  The buses will run 260 days per year.  There are two periods to consider, Rush Hours and the Remaining Hours.  This simplifies the forecasting for the anticipated passengers.  During rush hours it is assumed that the buses used will be at full capacity for all 12 trips and that all buses available will be used.  During the remaining 12 hours it is assumed that a total of 500 passengers will use the buses and 4 buses will be used.  This requires 4 full-time drivers and 2 part-time in the event that six 32-Passenger buses are selected.

As mentioned, I added a mix option.  Often a mix of options can produce a maximization.  Let's look at the end result and walk through the numbers after.

If we were simply making a selection on NPV between the initial options, we can see that the selection of four 52-Passenger buses has the higher NPV.  This is primarily hinging on the the revenue forecast, the passengers per annum.  Since this is the case, I included the annual passengers as a refence to the NPV.  The mix is the Solver optimization to maximize the NPV by choosing the mix of buses.  The mixture selected was constrained by our budgeting spend as mentioned.  It should be no surprise that the Solver selected four 52-Passenger buses and one 32-Passenger bus since the 32-Passenger bus, having a positive NPV for one, would add to the overall NPV.  In order to assess a bit more information, I listed the respective sensitivity of NPV to annual passenger totals for the Mix selection.

Essentially, this is a question about capacity and specifically rush hour capacity.  If we can be sure that in either initial option of six 32-Passenger or four 52-Passenger buses that there are still passengers available to pick-up, then our mix option is our best option.  We also have the further option to reduce costs slightly in the remaining hours by using the one 32-Passenger bus and three 52-Passenger buses for the four total used in the remaining hours.  Not only can this help to rotate out one 52-Passenger bus for maintenance but it saves on wage and fuel costs.  In fact, we could trim off about $4000 per year in labor and fuel costs using this method.

​The cash flow numbers starting with revenues.

In order to arrive at a selection for the Mix, a weighted average was used in the formula.  As an example, given the 1, 4 selection and capacity of passengers, the 48 is arrived at using 1/5*32 + 4/5*52 = 48.  You can also see the Rush Hr passenger total and now relate the sensitivity above in the NPV.  Were we to dip to only 2496 passengers as in the 52-Passenger model, then the NPV for the Mix would fall below this option.  Hence my mention of the question of capacity.

Below is the labor cost breakout.

Since the Mix column calculation was based on a weighted average for the Solver, the calculation off to the right reflects the costs, specifically, if the one 32-Passenger, three 52-Passenger was used for the remaining hours in the mix selection. Note that this is unlikely for the entire year since the 32-Passenger would have to be maintained at some point.

Below is the Fuel cost.  Again, the far right represents the specific selection of ​one 32-Passenger, three 52-Passenger for the entire year during the remaining hours.

Putting this all together.

Off to the right, you see the few thousand from the usage of the one 32-Passenger, three 52-Passenger for the entire year.  

While this analysis lays out a NPV relative to passenger revenue generation it still really is a question of capacity and assumptions.  The passenger forecasts are assuming full seating during rush hours which is why Solver selected the additional 32-Passenger bus that was within the budget constraint because it added to NPV.  However, this is also why I suggested the usage of the one 32-Passenger, three 52-Passenger buses for the remaining hours because it reduces the number of unused seats relative to the forecasted 500 passengers that would use the buses over the remaining hours. 

That said... what we really want is to maximize revenue per seat AND maximize NPV while still constraining our budget.  This includes ALL seats available.  The total capacity, regardless of whether the bus is being used is all seats for all trips.  As an example, we have six 32-Passenger buses for a total of 48 trips, 12 during rush hour and 36 for the remaining hours, 6*32*48 = 9,216 available seats to generate revenue.  This is a question of utilization of capacity for the generation of revenues.  Can we achieve a high rate of revenue per seat and NPV?

Let's look.

In our current distribution

With that extra bus in the Mix selection, we probably knew we weren't maximizing our utilization of capacity to generate revenues.  Using Solver we can add the additional constraints to maximize revenue per seat and NPV greater than either of the other options.

Again, it is important to remember that a very influential assumption is that rush hours operate at full capacity as we proceed through this new data relative to the selection of two 32-Passenger buses and three 52-Passenger Buses.

​The revenue per seat is below.

We managed to increase the revenues per seat to $115.97.  The trade-offs here occur in the rush hour capacity (full of paying customers) and the Remain Hr - Pass/Seat ratio (the off hours utilization).  If we selected, instead, three 32-Passenger and two 52-Passenger, then our rush hour passengers falls to 2400 ... making less overall revenue that the four 52-Passenger option.

​The advantage with this new setup is that we can run the one 32-Passenger and three 52-Passenger bus arrangement in the remaining hours consistently, but also have the option to run a two and two, respectively which reduces costs further.

To the far right is the cost if we could consistently run the two and two set up. Similar to the Fuel model below.

The cash flows below.

And finally, the NPV valuation.

This time, the Mix NPV will not flex on passengers, but will look at the NPV based on the cash flows from a two and two run for the remaining hours, $414k, or a changing of the types of buses, $406k.  The NPV in either case is still greater than the original options, the initial investment is within budget and we managed to maximize revenue per seat and utilization for the higher NPV.

When conducting capital budgeting it is important to not only consider assumptions, but also how those assumptions are going to impact the model.  In this case if we didn't consider the further metric of utilization, we may suggest a different capital investment plan. 

​Them comparison between the Mix plans.

As you can see, the annual passengers is the factor, especially since we looked at maximizing revenue per seat and reduced costs in the upper mix.  We can see that the 816,400 annual passengers is comparable to the lower 819,000 annual passengers and the NPV is substantially more in the upper regardless of how we run our buses in the remaining hours.  Of course, if we were to see a considerable increase in passengers to the level of the 878,800 we would have a potential for a higher NPV, but what is the probability of seeing a full capacity usage during rush hour for that selection of buses? 

This is something that we would have to look into... but given the assumptions laid out, our best investment within budget constraints, maximizing our utilization and revenues per seat, and our maximum NPV with all factors considered is to purchase two 32-Passenger buses and three 52-Passenger buses.