Project: Decision Support
This project deals with business forecasting and decision support systems. You are encouraged to create a spreadsheet to answer the questions below. You do not need to submit the spreadsheet.
It is December 2016. You are planning to start a small airline in bush Alaska. The grand opening is January 2017. You have $250,000 startup capital. You have fixed payments to make for your airplanes, staff and office space, at $80,000 per month. Your variable costs are $120 per passenger and you charge an average of $180 per passenger.
You expect to have 800 passengers in January 2017 and you expect a uniform rate of increase in this number, some X % month to month. Set up a spreadsheet so that you can calculate your cash balance at the start and end of each month, given the number of passengers for that month. Link your cells to allow you to specify the month to month growth rate X% in a single cell.
1. In any given month, how many passengers do you need to be profitable? (to make enough money during that month just to cover your expenses for the month)
2. If you start with 800 passengers in January and the growth rate X is zero (no growth), what is the first month at the end of which you will have a negative cash balance?
3. Calculate the smallest rate of increase in the number of passengers per month X, to make sure you do not run out of cash at any time (you always end up with some cash left at the end of each month). You might want to use goal seek (try various starting values for X to help goal seek to coverage).
4. Calculate the rate of increase in the number of passengers per month X if each month you must maintain a cash reserve (at the end of the month) of at least 10% of the current month's expenses.
5. Redo the previous question if the inflation rate is at 1% per month (assume that all your expenses will increase 1% per month).
6. Faced with high demand on one of your routes, you charter a larger airplane for a one-time flight. You are able to sell first class tickets at $1200 per person (but will only be able to sell at most 10 tickets), economy tickets at $400 per person or you can carry cargo for $1.20/lb.
Each first class passenger comes with 600 lbs of weight (luggage, passenger and in-flight meals) and each economy passenger weighs in at 300 lbs (including luggage, passenger and in-flight meals). According to FAA specifications, the aircraft can carry no more than 25,000 lbs, including both passengers and the cargo weight.
Additionally, you need to figure out space limitations on board. Each first class seat takes 30 s. ft. of space and each economy seat takes 13 sq. ft. you can pile up cargo 50 lbs/sq. ft.The total floor space in the plane is 1000 sq. ft., which needs to accommodate all the passengers and the cargo. For simplicity, you do not need to have full rows of seats ( i.e., you could have 17 seats on the whole plane) and do not need to worry about aisle space.
Use solver to figure out how many passengers and how much cargo you can carry to maximize your revenue for the flight. Make sure you consider all the conditions you need for solver. The program does not understand the realities of life.
How many pounds of cargo will you need to carry to achieve this maximum?
7. You also need to purchase insurance for you employees. The three options available are given below: Plan A: Monthly charge -$30, Deductible-$1300, Out of pocket maximum-$5000
Plan B: Monthly charge-$60, Deductible-$500, Out of pocket maximum-$2000
Plan C: Monthly charge-$150, Deductible-$200, Out of pocket maximum-$750
Employees may elect to participate in any one of the three plans, or to opt out of insurance totally. Employees who select a health plan pay the monthly charges for all the twelve months per year; no fractions of a year are allowed.
We use the term "medical care expenses" for the amount billed by the medical providers. This amount is paid in part by the patient, with the balance covered by the insurance. "Patient costs" are the charges incurred by the patient ( which include monthly charges and the patient's portion to the medical providers' bill).
As employees incur medical expenses, they pay for part of the medical case and the insurance pays for the balance. Given a certain cost of medical care expenses, the relative share of the employee and the insurance company are as described below. The employee must pay for the full cost of the medical care until the expenses exceed the Deductible. For the medical care expenses in excess of the Deductible, the plan pays for 80% for the expenses, and the employees are responsible for the remaining 20%. Finally, once the expense incurred by the employee reaches Out of pocket, the plan pays for 100% of the medical charges.
The Out of Pocket charge does not include the Monthly Charges, not the Deductible. Both the deductible and the out of pocket amounts are for the year; at the end of the year, the patient needs to start over and meet the deductible and out of pocket as new.
If the employee selects Plan B, what is the maximum amount of patient costs they will spend on health care by the end of the year (including Monthly charges and their portion of the medical care, not covered by insurance)?
8. How much do the medical care expenses need to be (at least) for the employee to have to pay the maximum figure, as in the question above?
9. At what cost of medical care is the employee paying the same amount whether using insurance (the lowest cost plan) or paying for medical care entirely on her own? You might want to use goal seek for this question.
10. An employee expected the cost of medical care for the following year to be $7,000. Based on this assumption, the employee chose the plan with the lowest expenses for that level of medical care. IF the actual expenses are in fact $9,000 at the end of the year, this choice of plan might not be the best anymore. How much worse off is the employee because of the error in estimating medical expenses? ( What is the difference between what the employee would have paid under the best plan and what s/he is actually paying in the scenario above?)