MGMT 300 AT Still University Range Light Expeditions Cash Flow Projections Worksheet Range of Light Expeditions is located in a small town in the foothills

MGMT 300 AT Still University Range Light Expeditions Cash Flow Projections Worksheet 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 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 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). 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

In thousands

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

Download the EXCEL workbook called kayak from D2L. It is partially completed. You have to complete this workbook to project the ending cash balance for the 12-month period starting in January. The completed workbook will have 4 worksheets. First worksheet is the title sheet. In the title sheet type your name, a Title for the spreadsheet, and Purpose. For purpose, type couple of sentences explaining what this spreadsheet does for the user. Second worksheet will be the Cashflow sheet. This worksheet will have two sections namely Input section and calculation section. All the data given to you goes in the Input section. I have typed some labels and some data for the input section. You have to enter the remaining data and labels in the input section. Below the input section there is a calculation section. In this section type all the formulas needed. I have typed some of the labels needed in this section. You have to make sure all the labels needed in the calculation section are present. Use meaningful labels. In the calculation section compute the expected cash inflows by month and sources (payment from customers who pay in full, deposit from installment customers, second payment from installment customers, and final payment from installment customers etc.). Total the revenue/(cash inflow) for each month. Below this compute the detailed payments/(cash outflows) related to wages/salary and Social Security tax etc. Compute the refunds also in this section. Your formulas should have cell addresses only. Do not have numbers in your formulas except for constants like number of minutes in an hour, which is always 60, or cents in a dollar which is always 100. Third worksheet would be the scenario summary, and the fourth worksheet will be the graph.

In the cashflow worksheet, for each month, calculate and totaling the revenue or cash inflow and cash outflow or payments, compute the difference between inflow and outflow, and the ending cash balance. Beginning cash for March is same as ending balance for February. Revenue is same as cash inflow and payments is same as cash outflow. Ending cash balance for any month equals (beginning cash + cash inflow – cash outflow). All negative cash balances should be displayed in black with a negative sign. All cash inflow and cash outflow numbers should be displayed using currency format with no numbers after the decimal.

If the expedition fee is increased to $5500, then the number of customers goes down by 5% as some customers get disinterested because of higher fees. On the other hand, if the expedition fee is reduced to $4500, then the number of customers increases by 5%. Define 3 scenarios including the current scenario. For the two new scenarios, you have to manually calculate the number of estimated customers for each month under each scenario. Create a summary report and using the information in the summary report identify which scenario is best for John. You may use the ending cash balance for December to identify the best scenario since this number reflects the profit for the year.
Create a header and a footer for your spreadsheet. Display your name and the sheet name in the header. In the footer display the workbook name and the page numbers.
Plot a column graph showing the ending cash balance for each of the 12 months. Give an appropriate title to the graph. Also give appropriate title to the X-axis and the Y-axis.
Write a memo to John Whitney. The memo should

– Identify the line of credit that John should attempt to negotiate with the bank. The credit is obtained on a yearly basis. The cost for getting a line of credit is only $200. Interest on the credit is computed based on how much was drawn from the credit line and for how long. It would be in the company’s best interest to payback the balance on the credit line as soon as possible. So, it is possible for a company to draw $3000 from the line of credit in March, pay it in April when the company has surplus, and again withdraw in May when the company is short on cash. You pay interest on outstanding balance only.

-Indicate the specific months (for the current scenario) in which Range of Light Expeditions can expect a need to draw on its line of credit. Also indicate the amounts it may have to draw. In this memo also indicate which scenario is best for the business.

NOTE:

Be sure all tables, columns and rows are appropriately titled, and that all columns/rows that can be meaningfully added are totaled. Formulas should have cell addresses only and should NOT have any numbers (only constants like number of minutes (60) in an hour are allowed) in the formula . Remember, someone else may use this work. As a result, you should make sure it is easy to read, understand, and use. Name your workbook “Kayak”.

What to submit:

Leave your workbook and memo in the drop box on D2L. Range of light expeditions
Cash flow projections
INPUT SECTION
Sales data
JAN
Previous year
Numer of customers signed up
18
Current year estimate
Numer of customers signed up
24
Expedition fee per person
$5,000
Advertising expenses
Vehicle leases
Gas expenses
Prior year ending cash
$44,593
Number of customers
Paying in full
30%
Paying in 3 installments
70%
Deposit in 1st month
40%
Pay in 2nd month
30%
Pay in 3rd month
30%
Forfeit deposit
10%
Receiving full refund
3%
Type I employees
Number of
14
Salary
$4,500
Type II employees
Number of
2
Salary
$4,100
Type III employees
Salary
$ 2,900.00
Type III employee
Daily boarding/lodging cost
Insurance Policy data
FEB
MAR
APR
MAY
JUNE
16
22
20
46
42
18
21
32
62
49
Delete this sentence and type the advertising expenses given in the wo
Type here from the word document.
Here create the table
Type here from the word document.
for the lookup
function to estimate
number of temporary
labor. Click on
merge& center
button to get
adds up to 100%
individual cells back
per month
Workers compensation data
Social security data
Vehicle data
Create a table with
vehicle lease data. This
table will be used by
vlookup function.
Create a table with
vehicle lease data. This
table will be used by
vlookup function.
Office rent
Other office expenses
Shipping container data
Loan data
Calculation Section
JAN
Revenue
Payment in full
1st installment
2nd installment
Final installment
Total revenue
Payments
Refund
Vehicle leases
Gas expenses
Wages
Type 1 employees
Type 2 employees
Type 3 employees
Lodging/boarding cost
Advertising expenses
Insurance expenses
Social security expenses
Worker’s compensation insurance
Vehicle expenses
Office rent
Other office expenses
Shipping container expenses
Total Payments
Beginning cash
Revenue-Payments
Ending cash
FEB
MAR
APR
MAY
JUNE
JUL
AUG
SEP
OCT
NOV
DEC
40
13
11
30
36
23
51
14
11
39
49
48
expenses given in the word document over here
Here create the table
for the lookup
function to estimate
number of temporary
labor. Click on
merge& center
button to get
individual cells back
JUL
AUG
SEP
OCT
NOV
DEC

Purchase answer to see full
attachment