Description

Project #1 (20 points)

A DECISION SUPPORT SYSTEM FOR CASH FLOW ANALYSIS

Background

          Range of Light Expeditions is located in a small town in the foothills of the Sierra Nevada Mountain Range in California. The company is engaged in the business of taking small groups of outdoor enthusiasts on outings in the Sierra Nevada. The outings range from weekend trips to multiweek expeditions. John Muir Whitney (named after the famous naturalist and explorer) started the company in the spring of 2015 and, within a few months, had assembled a core of three leading outdoor recreationists to assist him.

          Whitney, an early riser, strapped on his cross-country skis and took a short trip to his favorite spot for watching the sunrise behind the Le Conte Divide. As he sat there, he pondered the future of Range of Light Expeditions and the direction he should take the firm. He now had a staff of fourteen full-time trip leaders/guides, demand for their outings clearly outstripped capacity, and he was planning to branch out into white water recreation this spring. The move into white water outings was seen as a way to help smooth out the cyclical nature of their business and occupy the spring period between their winter and summer outings.

          John has asked you for help with modeling the company's cash needs as they expand. This would be essential if it is to apply for the line of credit needed to help with future expansion and maintenance of an even cash flow. The company has had a problem with its cash flow since the cycle of cash coming into the business does not always match the cycle of cash going out. This disparity will be worse as it gears up for its new line of ventures. John has decided he will meet with you as soon as he returns to town.

          Your interview with the office manager has revealed the following facts.

Cash Inflows

- The ending cash balance for the prior year just ended is $44,593.

- Overall, 30 percent of customers pay in full when reserving a space on an outing. Remaining 70 percent of customers use the following quarterly payment plan: 40 percent deposit to reserve a space, and the remainder paid in two equal payments at thirty and sixty days after the original reservation. Customers go on the outing 2 months after their first payment. So, customers who pay in full in January go on the trip in March, and customers who make their initial deposit in January also go on the trip in March.

- Of the 70 percent of the customers who use the quarterly payment plan, approximately 10 percent make the deposit but do not make the remaining two payments. These customers lose their deposit.

- Customers can get a full refund if they cancel within fifteen days of booking an outing. Approximately 20 percent of customers from each category cancel and receive full refunds.

In the spreadsheet you are given the number of customers who reserve a place by either making a full payment or initial deposit of 40% of trip price. In the month of January, the company will be getting 2nd payment from last year December installment customers and 3rd payment from last year November installment customers.

Cash Outflows

There are three types of employees at Range of Light Expeditions.

-Type 1 employees are trip guides/leaders (John Whitney is considered a Type 1 employee). If there are 10 or less customers going on the expedition, then 2 Type 1 employees are needed. 4 Type 1 employees are used if the number of customers is between 11 and 25, and 6 are used if there are more than 25 customers. Each is paid an average of $4,500 per month. Use IF function to estimate number of type 1 employees needed.

-Type 2 employees hold administrative positions, and average $4,100 per month. There are two Type 2 employees.

-Type 3 employees are temporary help hired on a seasonal basis. They average $2,900 per month and have all living expenses (room/board/at work travel) provided by Range of Light Expeditions. Lodging and boarding expenses for Type 3 employees average $60/day when number of Type 3 employees is 6 or fewer and $50/day if there are more than 6 Type 3 employees. These expenses are incurred every day of the month. Use IF function to estimate Lodging & boarding expenses.

All employees receive medical, dental and vision benefits, which costs an average of $550 per employee per month. If the number of employees during any month exceeds 10, then the company gets a 10% discount for that month from the healthcare insurance company. The firm carries a liability policy, which is quite expensive due to the type of work they are in. During the months when the total number of people on the expedition including customers and staff is 25 or less, the company carries a 4 million dollar policy that costs $3,500 per month but if the total number of people is greater than 25 then the company carries a 6 million dollar policy that costs $5000 per month. Use If function to estimate this expense.