Ask Management Information System Expert

Management Information System Decision Support

Hands-on 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?)

Management Information System, Management Studies

  • Category:- Management Information System
  • Reference No.:- M92039074

Have any Question?


Related Questions in Management Information System

Search the csu library the internet or any specific

Search the CSU library, the Internet, or any specific websites, and scan IT industry magazines to find an example of an IT project that had problems due to organizational issues. Write a paper summarizing the key stakeho ...

Question how can company protect the new emerging

Question : How can company protect the new emerging technology ventures from profit pressures of the parent organization (APA format required, Turntin check required . Minimum 250 words essay) How do companies overcome l ...

Communication and team decision makingpart 1 sharpening the

Communication and Team Decision Making Part 1: Sharpening the Team Mind: Communication and Collective Intelligence A. What are some of the possible biases and points of error that may arise in team communication systems? ...

Question provide an explanation of ifwherehow does active

Question : Provide an explanation of if/where/how does Active Directory support network security,14 pages (2,000-2,500) in APA format. Include abstract and conclusion. Do not include wikis, message boards, support forums ...

Question how companies could effectively use emerging

Question : How companies could effectively use emerging technology to win over its competitors. APA format required. 250 words essay required. The response must be typed, single spaced, must be in times new roman font (s ...

Question how customers could effectively use emerging

Question : How customers could effectively use emerging technology to win over its customers. APA format required. 250 words essay required. turntin check require. The response must be typed, single spaced, must be in ti ...

Part 1 - create an 8 slide powerpoint presentation on

Part 1 - Create an 8 slide PowerPoint presentation on foundational concepts specific to physical security. Part 2 - Write 4 pages detailing the framework for the design of an integrated data center. Assessment Instructio ...

In chapter 2 of the text - managing amp using information

In Chapter 2 of the text - Managing & Using Information Systems: A Strategic Approach, the chapter discusses why information systems experience failure often because of organizational strategy. A classic example of this ...

Review at least 4 articles on balanced scorecard and

Review at least 4 articles on Balanced Scorecard and complete the following activities: 1. Write annotated summary of each article. Use APA throughout. 2. As an IT professional, discuss how you will use Balanced Scorecar ...

Data resources management questionsq1 the dama dmbok

Data Resources Management QUESTIONS Q1. The DAMA DMBOK textbook describes the following two core activities as part of the Data Architecture management exercise: "Understanding enterprise information needs" and "Develop ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As