dont do the sheet 1 we already did it

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 2021 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. 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 retums to town. Your interview with the office manager has revealed the following facts. Cash Inflows - The ending cash balanee for the prior year just ended is $37,500. - 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 devosit to reserve a sbace, and the remainder paid in two equal pavments at Your interview with the office manager has revealed the following facts. Cash Inflows - The ending cash balance for the prior year just ended is $37,500. - 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 hatwean 11 and 35 and 6 are licad if thara ara mora than 35 crletomare Fach is 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 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 Page 2 of 6 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. -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 vremiums are paid each month. -Workers' compensation insurance is $14 per $1,000 of salary/wages for Type I 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 $3,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 $250 per month to rent. -Vehicle lease rates per month are: 850 per month per vehicle if lease 13 velicles, 380 per month per vehicle for 46 vehicles, 800 per month per vehicle for 79 vehicles, 750 per month per vehicle if lease more than 9 vehicle. Use a formula - Vehicle lease rates per month are: 850 per month per vehicle if lease 1-3 vehicles, 380 per month per vehicle for 46 vehicles, 800 per month per vehicle for 79 vehicles, 750 per month per vehicle if lease more than 9 vehicle. Use a formula with Vlookup function to estimate monthly vehicle lease costs. Number of temporary employees needed is as per below: 1 Download the FXCFI, workhook called kavakCashFlow from D2I. It is Download the EXCEL workbook called kayakCashFlow 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 efc. 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 the total 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. 2. If the expedition fee is increased to $5500, then the number of customers goes down by 6% 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 6%. 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 and then use those mumhere while dofinind the erenarine Create a summary renat and using the 2. If the expedition fee is increased to $5500, then the number of customers goes down by 6% 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 6%. 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 and then use those numbers while defining the scenarios. Create a summary report and using the information in the summary report to 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. 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. You may use any of the three scenarios to plot the graph. This will be your 4th worksheet. 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 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 monthly 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 "KayakCashFlow". What to submit: Leave your workbook and memo in the drop box on D2L. Submit hard copy of your workbook. KayabCashflonciax - Excel 26. Salary 45 Vehicle dafa 46 ottice tipnt 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 2021 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. 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 retums to town. Your interview with the office manager has revealed the following facts. Cash Inflows - The ending cash balanee for the prior year just ended is $37,500. - 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 devosit to reserve a sbace, and the remainder paid in two equal pavments at Your interview with the office manager has revealed the following facts. Cash Inflows - The ending cash balance for the prior year just ended is $37,500. - 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 hatwean 11 and 35 and 6 are licad if thara ara mora than 35 crletomare Fach is 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 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 Page 2 of 6 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. -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 vremiums are paid each month. -Workers' compensation insurance is $14 per $1,000 of salary/wages for Type I 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 $3,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 $250 per month to rent. -Vehicle lease rates per month are: 850 per month per vehicle if lease 13 velicles, 380 per month per vehicle for 46 vehicles, 800 per month per vehicle for 79 vehicles, 750 per month per vehicle if lease more than 9 vehicle. Use a formula - Vehicle lease rates per month are: 850 per month per vehicle if lease 1-3 vehicles, 380 per month per vehicle for 46 vehicles, 800 per month per vehicle for 79 vehicles, 750 per month per vehicle if lease more than 9 vehicle. Use a formula with Vlookup function to estimate monthly vehicle lease costs. Number of temporary employees needed is as per below: 1 Download the FXCFI, workhook called kavakCashFlow from D2I. It is Download the EXCEL workbook called kayakCashFlow 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 efc. 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 the total 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. 2. If the expedition fee is increased to $5500, then the number of customers goes down by 6% 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 6%. 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 and then use those mumhere while dofinind the erenarine Create a summary renat and using the 2. If the expedition fee is increased to $5500, then the number of customers goes down by 6% 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 6%. 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 and then use those numbers while defining the scenarios. Create a summary report and using the information in the summary report to 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. 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. You may use any of the three scenarios to plot the graph. This will be your 4th worksheet. 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 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 monthly 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 "KayakCashFlow". What to submit: Leave your workbook and memo in the drop box on D2L. Submit hard copy of your workbook. KayabCashflonciax - Excel 26. Salary 45 Vehicle dafa 46 ottice tipnt