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 70percent 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 3 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 2^nd payment from last year December installment customers and 3^rd 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). Currently there are 14 Type 1 employees. Each is paid an average of $4,500 per month.

-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.

The firm carries a liability policy, which is quite expensive due to the type of work they are in.

-During the months when the expected revenue is $125000 or less, the company carries a 4 million dollar policy that costs $3,500 per month

- but if the expected revenue is greater than $125000 then the company carries a 6 million dollar policy that costs $5000 per month. Use If function to estimate this expense.

-Workers' compensation insurance is $14 per $1,000 of salary/wages for Type 1 and 3 employees, and $2.10 per $1,000 of wages/salary for Type 2 employees. Employer contributions to Social Security (7.65 percent of wages/salary) and health insurance premiums are paid each month.

-Workers' compensation and liability insurance premiums are paid quarterly on the last day of March, June, September, and December.

-Range of Light Expeditions has taken out a loan to purchase kayaks etc. The total amount of loan taken out is $180,000 at annual interest rate of 4.5% payable over a 5 year period. Use PMT function to estimate this expense.

Office space is leased at $1,300 per month. Office expenses, other than salary, average approximately $1,900 each month. This includes telephone, utilities, paper, pencil, etc.

-Shipping containers are used for secure equipment storage. Three are currently under lease; an additional two will be needed to securely store the white water rafting equipment. These are slated to arrive April 1. Each container costs $225 per month.

-Vehicle lease rates per month are: 400 per month if lease 1-3 vehicles, 380 per month for 4-6 vehicles, 350 per month for 7-9 vehicles, 330 per month if lease more than 9 vehicle. Use a formula with Vlookup function to estimate monthly vehicle lease costs.

Gas Advertising

Expenses Vehicle expenses

Month (in 000s) Leases (in 000s)

January 12 5 1.3

February 14 5 1.1

March 11 5 7.3

April 16 7 2.8

May 18 7 1.9

June 21 10 0.9

July 22 10 0.9

August 16 10 0.9

September 14 10 0.6

October 13 8 0.6

November 12 5 1.2

December 12 5 1.5

Number of temporary employees needed is as per below:

Monthy revenues from	Monthly revenue up to	# of Type III employees needed
$0	$80000	4
$80000	$110000	5
$110000	$125000	6
$125000	$150000	7
$150000	$180000	8
$180000	Maximum limit	10