04 May Revenue Source Online Reservations
You will need Excel 2007, 2010, 2013, or 2016 for project. You will also need the following file(s): 1010excel.xlsx.
Project Background: You have been hired by the Bahamas Cruise Line as an analyst. You will assist the cruise line staff in record-keeping, revenue generation, and overall efficiency. Your project will be broken down into the following three sections: Worksheet& Workbook Management, Formulas & Functions, and Charts & Subtotals.
Should you need additional help for adding numbers in Excel, using functions, formulas, subtotals, or charts, please watch the following video: Rocks, D. (August 9, 2008). See the “big picture” with Excel’s subtotal function [Video file]. Retrieved from https://www.youtube.com/watch?v=X5w5uWJDct8
Part 1: Worksheet & Workbook Management—Rename and organize the worksheets within the workbook, and re-save the workbook itself by following the instructions below: CS 1010, Computer Essentials 2
1. Click here to access and open 1010excel.xlsx.
2. Save the 1010excel.xlsx file as your “student ID number_last name.xlsx” (ex: 12345_Jones.xlsx).
3. Rename Sheet 1 to Formulas&Functions.
4. Rename Sheet 2 to Chart.
5. Rename Sheet 3 to Subtotals.
6. Move the Chart worksheet to the right of the Subtotals worksheet.
7. Place a fourth worksheet using your student ID number as the name.
8. Save the file.
Part 2: Formulas & Functions—In this section, you will assist the sales manager in categorizing the company’s revenue sources and how each one relates to each other. You will use formulas and functions to create totals and/or averages of the revenue data following the instructions below. Make sure you are in the Formulas&Functions active worksheet before you begin:
1. In cell B2, place a function that totals Jan. through Dec. sales for Online Reservations.
2. In cell B3, place a function that totals Jan. through Dec. sales for Telephone Reservations.
3. In cell B4, place a function that totals Jan. through Dec. sales for Sales Calls.
4. In cell B5, place a function that totals Jan. through Dec. sales for Activity Companies.
5. In cell B7, place a formula that adds the annual totals of all four categories using cells B2, B3, B4, and B5, which will give you the total annual revenue for the Bahamas Cruise Line.
6. In cell C2, place a function that displays the average for online reservations from Jan. through Dec. Repeat this for the other three categories of revenue.
7. Save the file.
Part 3: Charts & Subtotals— Put a pie chart that compares each revenue source in the month of January, and use the subtotals feature in Excel to total three separate categories of revenue. Use the following instructions: Make sure the Chart worksheet is active before you begin.
1. Place a pie chart using the range A1 to B5.
2. Slightly detach the sales calls piece of the pie from the rest of the pie chart.
3. Save the file.
Make sure the Subtotals worksheet is active before you begin.
1. 1. Sort the list by category (Activities, Hotels, and Retail) and add a row underneath each category, titled “Subtotal.”
2. 2. Use the sum function in Excel to display totals for the three categories on this worksheet.
3. 3. Save the file and submit for grading.
Revenue Source
Annual Total
Monthly Average
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Online Reservations
$ 45,208
$ 40,000
$ 15,000
$ 65,000
$ 25,000
$ 39,000
$ 85,000
$ 85,000
$ 74,000
$ 34,000
$ 14,500
$ 26,000
Telephone Reservations
$ 6,717
$ 2,200
$ 20,000
$ 4,900
$ 3,100
$ 1,500
$ 12,100
$ 6,100
$ 3,000
$ 2,300
$ 10,600
$ 7,600
Sales calls
$ 7,271
$ 5,600
$ 7,500
$ 5,800
$ 4,000
$ 1,800
$ 2,300
$ 8,800
$ 6,200
$ 4,850
$ 26,000
$ 4,900
Activity companies
$ 80,449
$ 10,500
$ 65,000
$ 49,000
$ 55,000
$ 1,55,000
$ 1,05,889
$ 1,00,000
$ 69,000
$ 63,000
$ 68,000
$ 1,63,000
Grand total:
Revenue Source
Jan
Online Reservations
$ 45,208
Telephone Reservations
$ 6,717
Sales calls
$ 7,271
Activity companies
$ 80,449
Description
Date Paid
Category
Amount
Nassau Store
11-8-2014
Retail
$ 4,974.53
Atlantis Resort
11-5-2014
Hotels
$ 78.34
Atlantis Resort
11-8-2014
Hotels
$ 35.99
Atlantis Resort
12-20-2014
Hotels
$ 25.12
Bahama Activities
10-31-2014
Activities
$ 451.15
Tiamo Resort
11-5-2014
Hotels
$ 11.68
Tiamo Resort
11-16-2014
Hotels
$ 160.00
Tiamo Resort
12-7-2014
Hotels
$ 46.00
Tiamo Resort
12-10-2014
Hotels
$ 15.00
Fun in the Sun Tours
11-5-2014
Activities
$ 99.75
Bahama Adventure Tours
10-22-2014
Activities
$ 2,200.00
Bahama Water Park
10-26-2014
Activities
$ 13,300.00
Island Seas Resort
11-5-2014
Hotels
$ 9,133.00
Island Seas Resort
12-21-2014
Hotels
$ 52.75
Nassau Ocean Adventures
11-27-2014
Activities
$ 230.00
Coco Cay Store
12-8-2014
Retail
$ 445.33
Lucaya Store
12-9-2014
Retail
$ 175.00
Ocean Tours Bahama
12-2-2014
Activities
$ 590.00
Nassau Adventures
12-3-2014
Activities
$ 130.00
Musha Cay Resort
12-10-2014
Hotels
$ 45.97
Paradise Fun Company
11-29-2014
Activities
$ 750.25
Alice Town Store
12-30-2014
Retail
$ 1,452.67
Nassau Fun Activities
10-26-2014
Activities
$ 884.75
Bay Street Store
12-30-2014
Retail
$ 396.88
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.