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

Aug

ust 9, 2008). See the “big picture” with Excel’s subtotal function [Video file]. Retrieved from

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