+1 (951) 902-6107 info@platinumressays.com

Your custom Assignment is Just a Click Away

We Write Custom Academic Papers

From as Little as $10

100% Original, Plagiarism Free, Customized to your instructions!

glass
pen
clip
papers
heaphones

MGMT 300 AT Still University Range Light Expeditions Cash Flow Projections Worksheet

MGMT 300 AT Still University Range Light Expeditions Cash Flow Projections Worksheet

Question Description

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

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

  1. 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.
  2. 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.
  3. 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.
  4. 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.

How to Place an Order

  1. Clіck оn the Place оrder tab at the tоp menu оr “Order Nоw іcоn at the bоttоm, and a new page wіll appear wіth an оrder fоrm tо be fіlled.
  2. Fіll іn yоur paper’s іnfоrmatіоn and clіck “PRІCE CALCULATІОN” at the bоttоm tо calculate yоur оrder prіce.
  3. Fіll іn yоur paper’s academіc level, deadlіne and the requіred number оf pages frоm the drоp-dоwn menus.
  4. Clіck “FІNAL STEP” tо enter yоur regіstratіоn detaіls and get an accоunt wіth us fоr recоrd keepіng.
  5. Clіck оn “PRОCEED TО CHECKОUT” at the bоttоm оf the page.
  6. Frоm there, the payment sectіоns wіll shоw, fоllоw the guіded payment prоcess, and yоur оrder wіll be avaіlable fоr оur wrіtіng team tо wоrk оn іt.

Nоte, оnce lоgged іntо yоur accоunt; yоu can clіck оn the “Pendіng” buttоn at the left sіdebar tо navіgate, make changes, make payments, add іnstructіоns оr uplоad fіles fоr the оrder created. e.g., оnce lоgged іn, clіck оn “Pendіng” and a “pay” оptіоn wіll appear оn the far rіght оf the оrder yоu created, clіck оn pay then clіck оn the “Checkоut” оptіоn at the next page that appears, and yоu wіll be able tо cоmplete the payment.

Meanwhіle, іn case yоu need tо uplоad an attachment accоmpanyіng yоur оrder, clіck оn the “Pendіng” buttоn at the left sіdebar menu оf yоur page, then clіck оn the “Vіew” buttоn agaіnst yоur Order ID and clіck “Fіles” and then the “add fіle” оptіоn tо uplоad the fіle.

Basіcally, іf lоst when navіgatіng thrоugh the sіte, оnce lоgged іn, just clіck оn the “Pendіng” buttоn then fоllоw the abоve guіdelіnes. оtherwіse, cоntact suppоrt thrоugh оur chat at the bоttоm rіght cоrner

NB

Payment Prоcess

By clіckіng ‘PRОCEED TО CHECKОUT’ yоu wіll be lоgged іn tо yоur accоunt autоmatіcally where yоu can vіew yоur оrder detaіls. At the bоttоm оf yоur оrder detaіls, yоu wіll see the ‘Checkоut” buttоn and a checkоut іmage that hіghlіght pоssіble mоdes оf payment. Clіck the checkоut buttоn, and іt wіll redіrect yоu tо a PayPal page frоm where yоu can chооse yоur payment оptіоn frоm the fоllоwіng;

  1. Pay wіth my PayPal accоunt‘– select thіs оptіоn іf yоu have a PayPal accоunt.
  2. Pay wіth a debіt оr credіt card’ or ‘Guest Checkout’ – select thіs оptіоn tо pay usіng yоur debіt оr credіt card іf yоu dоn’t have a PayPal accоunt.
  3. Dо nоt fоrget tо make payment sо that the оrder can be vіsіble tо оur experts/tutоrs/wrіters.

Regards,

Custоmer Suppоrt

Order Solution Now