SOLUTION: San Diego City College San Diego Sailing Bookings Report

Print Info
Student Name: Shamoun, Drake
Student ID: 0831040
Username: drakeshamoun
Excel 2019 In Practice – Ch 2 Independent
Project 2-5
COURSE NAME CIS 110 – 4017 – Raney – Spring 2021 | Section 4017
Start Date:02/05/2112:00 AMUS/Pacific
Due Date:02/20/2111:59 PMUS/Pacific
End Date:02/26/2111:59 PMUS/Pacific
Independent Project 2-5
These instructions are compatible with both Microsoft Windows and Mac operating systems.
San Diego Sailing keeps data about its fleet of rental and charter boats. One of the sheets is missing a piece of data and
another sheet has circular reference errors. You will complete work on these sheets, calculate projected rates for each boat,
and build basic statistics about past rentals.
[Student Learning Outcomes 2.1, 2.2, 2.3, 2.4, 2.5, 2.6, 2.7]
File Needed: SanDiegoSailing-02.xlsx (Available from the Start File link.)
Completed Project File Name: [your name]-SanDiegoSailing-02.xlsx
Skills Covered in This Project
Create and copy formulas.
Use formula auditing tools.
Set mathematical order of operations.
Use relative, mixed, and 3D cell references.
Use COUNTIF and SUMIF functions.
Build an IF formula.
Insert the TODAY function.
NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open
the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show.
Close the Excel Preferences dialog box.
1.
Open the SanDiegoSailing-02.xlsx workbook.
2.
If the workbook opens in Protected View, click the Enable Editing button so you can modify it.
3.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your
instructor, and save it.
4. Review a formula.
a.
Click the New Prices sheet tab and review the Formula bar.
b.
Click cell D5. The formula begins with 1+D4.
5. Edit and copy a formula with mixed references.
a.
Edit the formula in cell D5 to show D$4 instead of “D4.” The formula should multiply one plus the percentage value
in cell D4 by the current rate on the Fleet sheet ($H5). With an absolute reference to row 4 and column H on the Fleet
sheet, you can copy the formula down the column (Figure 2-105).
Figure 2-105 Mixed reference in the edited formula
1/3
b.
Copy the formula in cell D5 to cells D6:D19 without formatting to preserve the border.
c.
Select cells D5:D19 and drag the Fill pointer to copy the formulas to cells E5:E19.
d.
Click cell E6. The formula is adjusted to use the percentage value in cell E4 in place of cell D4. Note also that the
reference on the Fleet sheet ($H6) is adjusted to show the correct row.
6. Build a formula with mixed references.
a.
Click cell F5 and type =(1+ to start the formula.
b.
Select cell F4 and make it an absolute reference to the row but not the column.
c.
Type )* for the closing parenthesis and multiplication.
d.
Click cell I5 on the Fleet sheet, and make the reference absolute for the column but not the row (Figure 2-106).
e.
Copy the formula down column F without formatting to
preserve the border.
f.
Format cells F5:F19 as Currency and then copy cells
F5:F19 to cells G5:G19.
7. Build an IF function formula.
a.
b.
c.
d.
8.
Click the Fleet sheet tab and select cell G5. Rental boats
with a stove in the galley must seat 8 or more people.
Figure 2-106 Mixed references in the new formula
Create an IF function in which the logical_test argument determines if there are 8 or more seats.
Use Yes for the value_if_true argument. Use No for the value_if_false argument. (If you type the formula, enclose
the text arguments within quotation marks.)
Copy the formula to cells G6:G19 without formatting and then center the data in column G.
Insert the TODAY function in cell B21. Format the date to show the month spelled out, the date, and four digits for the
year (January 1, 2020).
9. Create a division formula.
a.
Click the Bookings sheet tab and select cell F5. Calculate average revenue per passenger by dividing the fee by
the number of passengers.
b.
Build the division formula.
c.
Copy the formula in cell F5 to cells F6:F19.
10. Create and copy a COUNTIF function to count bookings by boat manufacturer.
a.
b.
c.
d.
Select cell D27.
Start the COUNTIF function from the Statistical category by clicking the More Functions button in the Function
Library group.
Use cells $C$5:$C$19 as the Range argument.
Set a Criteria argument that will select all boats in the “Beneteau” group. The criteria is ben*. If you type the formula,
include quotation marks.
e.
Copy the formula in cell D27 to cells D28:D30.
f.
Edit the criteria in each copied formula in cells D28:D30 to reflect the boat make.
11. Create and copy a SUMIF function to calculate total revenue by boat make.
a.
Select cell E27.
b.
Start the SUMIF function with cells $C$5:$C$19 as the Range.
c.
Set the Criteria argument to ben*.
d.
Set the Sum_range argument to cells $E$5:$E$19.
e.
Copy the formula in cell E27 to cells E28:E30.
f.
Edit the criteria in each copied formula in cells E28:E30 as needed.
12. Complete formatting.
a.
Apply the Currency format to all values that represent money.
b.
Format the labels in cells A1:A2 as 18 point.
c.
13.
Select cells A1:F2 and click the Alignment launcher [Home tab, Alignment group]. Center the labels across the
selection.
d.
Merge and center the label in cell C25 over cells C25:E25 and format it at 16 points.
e.
Bold and center the labels in rows 4 and 26.
f.
Select cells A4:F19 and apply All Borders. Do the same for cells C25:E30.
g.
Center the page horizontally.
Save and close the workbook (Figure 2-107).
2/3
Figure 2-107 Excel 2-5 completed
14.
Upload and save your project file.
15.
Submit project for grading.
3/3
San Diego Sailing
Rental and Charter Fleet
Boat ID
1010
1015
1146
1150
1152
1164
1168
1175
1180
1185
1190
1200
1205
1225
1230
Make and Model
Catalina Wave
Catalina Sky
Hunter Ray
Capri MKZ
Capri 22 Mk II
Capri 22 Mk II
Hunter 33
Beneteau 40
Beneteau 373
Hunter Sea
Beneteau 373
Beneteau 373
Beneteau 40
Hunter Sea
Catalina Wave
1-Jan-20
Length
28’4″
28’4″
33’6″
24’8″
24’8″
24’8″
33’6″
39’10”
36’11”
35’6″
36’11”
36’11”
39’10”
35’6″
28’4″
Model
Galley
Half-Day
Year Seats Sleeps with Stove
Rate
2010
2012
2014
2011
2016
2016
2018
2018
2017
2009
2017
2017
2018
2012
2019
8
8
10
6
6
6
10
12
10
10
10
10
12
10
8
6
6
6
4
4
4
6
6
6
6
6
6
6
6
6
Yes
Yes
Yes
No
No
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
$375,00
$425,00
$350,00
$325,00
$325,00
$325,00
$425,00
$489,00
$369,00
$349,00
$369,00
$369,00
$489,00
$349,00
$439,00
Full Day
Rate
$650,00
$725,00
$500,00
$500,00
$500,00
$500,00
$725,00
$750,00
$725,00
$550,00
$625,00
$625,00
$750,00
$725,00
$675,00
San Diego Sailing
New Prices at 5% and 10% Increases
Half-Day
Boat ID
1010
1015
1146
1150
1152
1164
1168
1175
1180
1185
1190
1200
1205
1225
1230
Make and Model
Catalina Wave
Catalina Sky
Hunter Ray
Capri MKZ
Capri 22 Mk II
Capri 22 Mk II
Hunter 33
Beneteau 40
Beneteau 373
Hunter Sea
Beneteau 373
Beneteau 373
Beneteau 40
Hunter Sea
Catalina Wave
Length
28’4″
28’4″
33’6″
24’8″
24’8″
24’8″
33’6″
39’10”
36’11”
35’6″
36’11”
36’11”
39’10”
35’6″
28’4″
5%
$399,75
$446,25
$367,50
$341,25
$341,25
$341,25
$446,25
$513,45
$387,45
$366,45
$387,45
$387,45
$513,45
$340,45
$460,95
10%
$412,50
$467,50
$385,00
$337,50
$337,50
$337,50
$467,50
$537,90
$405,90
$383,90
$405,90
$405,90
$537,90
$383,90
$482,90
Full Day
5%
$682,50
$761,25
$325,00
$325,00
$325,00
$325,00
$761,25
$787,50
$761,25
$577,50
$761,25
$761,25
$787,50
$761,25
$708,75
10%
$715,00
$757,50
$550,00
$550,00
$550,00
$550,00
$797,50
$825,00
$797,50
$605,00
$797,50
$797,50
$825,00
$797,50
$742,50
San Diego Sailing Bookings
Revenue per Passenger
Date
4/5/2020
4/8/2020
4/11/2020
4/14/2020
4/17/2020
4/20/2020
4/23/2020
4/26/2020
4/29/2020
5/2/2020
5/5/2020
5/8/2020
5/11/2020
5/14/2020
5/17/2020
Boat ID
1010
1015
1146
1150
1152
1164
1168
1175
1180
1185
1190
1200
1205
1225
1230
Make and Model
Catalina Wave
Catalina Sky
Hunter Ray
Capri MKZ
Capri 22 Mk II
Capri 22 Mk II
Hunter 33
Beneteau 40
Beneteau 373
Hunter Sea
Beneteau 373
Beneteau 373
Beneteau 40
Hunter Sea
Catalina Wave
# of
Passengers
4
6
6
6
4
3
6
10
8
8
10
8
12
8
6
Total
Revenue per
Fees
Passenger
$525,00
$131,25
$850,00
$141,67
$500,00
$83,33
$525,00
$87,50
$325,00
$81,25
$475,00
$158,33
$725,00
$120,83
$850,00
$85,00
$350,00
$43,75
$650,00
$81,25
$625,00
$62,50
$725,00
$90,63
$850,00
$70,83
$725,00
$90,63
$675,00
$112,50
Bookings by Make
Count
Beneteau
Capri
Catalina
Hunter
5
5
3
4
Total
Revenue
$5.400,00
$1.325,00
$2.050,00
$2.600,00

Purchase answer to see full
attachment

Order a unique copy of this paper
(550 words)

Approximate price: $22

Our Basic features
  • Free title page and bibliography
  • Plagiarism-free guarantee
  • Unlimited revisions
  • Money-back guarantee
  • 24/7 support
Our Options
  • Writer’s samples
  • Expert Proofreading
  • Overnight delivery
  • Part-by-part delivery
  • Copies of used sources
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

AcademicWritingCompany guarantees

Our customer is the center of what we do and thus we offer 100% original essays..
By ordering our essays, you are guaranteed the best quality through our qualified experts.All your information and everything that you do on our website is kept completely confidential.

Money-back guarantee

Academicwritingcompany.com always strives to give you the best of its services. As a custom essay writing service, we are 100% sure of our services. That is why we ensure that our guarantee of money-back stands, always

Read more

Zero-plagiarism tolerance guarantee

The paper that you order at academicwritingcompany.com is 100% original. We ensure that regardless of the position you are, be it with urgent deadlines or hard essays, we give you a paper that is free of plagiarism. We even check our orders with the most advanced anti-plagiarism software in the industry.

Read more

Free-revision guarantee

The Academicwritingcompany.com thrives on excellence and thus we help ensure the Customer’s total satisfaction with the completed Order.To do so, we provide a Free Revision policy as a courtesy service. To receive free revision the Academic writing Company requires that the you provide the request within Fifteen (14) days since the completion date and within a period of thirty (30) days for dissertations and research papers.

Read more

Privacy and Security policy

With Academicwritingcompan.com, your privacy is the most important aspect. First, the academic writing company will never resell your personal information, which include credit cards, to any third party. Not even your lecturer on institution will know that you bought an essay from our academic writing company.

Read more

Adherence to requirements guarantee

The academic writing company writers know that following essay instructions is the most important part of academic writing. The expert writers will, therefore, work extra hard to ensure that they cooperate with all the requirements without fail. We also count on you to help us provide a better academic paper.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2020 at 10:52 AM
Total price:
$26
The price is based on these factors:
Customer Academic level
Number of pages required
Urgency of paper