Independent project 2-5

Independent project 2-5

Independent Project 2-5

San Diego Sailing keeps data about its fleet of rental and charter boats. One of the sheets is missing a piece of data. 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: EX-OO-SanDiegoSailing-02.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]EX-OO-SanDiegoSailing-02.xlsx

 

Skills Covered in This Project

• Create and copy formulas.
• 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.

Downloading and opening project files for Office Online Projects:

Download the Start file from SIMnet.   Navigate to Office.com and log in to your account if necessary. Click Upload and open…   Navigate to the location where your files are downloaded.   Click Open to open the file in the Office Online Excel app.   Only use the Office Online Excel web app to work on and edit your project file. Never open the start file to work on it or edit it using Google Sheets.

 

Open the EX-OO-SanDiegoSailing-02.xlsx start file. 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.

 

1. Review a formula.

Click the New Prices sheet tab and review the Formula bar.

 

Click cell D5. The formula begins with 1+D4.

 

1. Edit and copy a formula with mixed references.

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

 

Copy the formula in cell D5 to cells D6:D19.

 

Select cells D5:D19 and drag the Fill pointer to copy the formulas to cells E5:E19.

 

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.

 

1. Build a formula with mixed references.

Click cell F5 and type =(1+ to start the formula.

 

Select cell F4 and make it an absolute reference to the row but not the column.

 

Type )* for the closing parenthesis and multiplication.

 

Click cell I5 on the Fleet sheet, and make the reference absolute for the column but not the row (Figure 2-106).

 

Copy the formula down column F.

 

Format cells F5:F19 as Currency and then copy cells F5:F19 to cells G5:G19.

 

1. Build an IF function formula.

Click the Fleet sheet tab and select cell G5. Rental boats with a stove in the galley must seat 8 or more people.

 

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

 

1. Create a division formula.

Click the Bookings sheet tab and select cell F5. Calculate average revenue per passenger by dividing the fee by the number of passengers.

 

Build the division formula.

 

Copy the formula in cell F5 to cells F6:F19.

 

1. Create and copy a COUNTIF function to count bookings by boat manufacturer.

Select cell D27.

 

Start the COUNTIF function from the Statistical category by clicking the Insert Function button.

 

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.

 

Copy the formula in cell D27 to cells D28:D30.

 

Edit the criteria in each copied formula in cells D28:D30 to reflect the boat make.

 

1. Create and copy a SUMIF function to calculate total revenue by boat make.

Select cell E27.

 

Start the SUMIF function with cells $C$5:$C$19 as the Range.

 

Set the Criteria argument to ben*.

 

Set the Sum_range argument to cells $E$5:$E$19.

 

Copy the formula in cell E27 to cells E28:E30.

 

Edit the criteria in each copied formula in cells E28:E30 as needed.

 

1. Complete formatting.

Apply the Currency format to all values that represent money.

 

Format the labels in cells A1:A2 as 18 point.

 

Select cells A1:F1 and click the Merge & Center drop-down arrow [Home tab, Alignment group]. Choose Merge & Center. Repeat these steps for cells A2:F2 to merge and center the text in row 2.

 

Merge and center the label in cell C25 over cells C25:E25 and format it at 16 points.

 

Bold and center the labels in rows 4 and 26.

 

Select cells A4:F19 and apply All Borders. Do the same for cells C25:E30.

 

Acquiring your completed file for submission:

Click File and choose Save As.   If you would like to rename the file, click Rename and name the file as desired. Press Enter to finalize the name.   Click Save As to save a copy online.   In the Save As dialog, click Save or click the Save to another folder button. Click Save to save the copy on OneDrive.   Click the App launcher in the upper left hand corner.   Click All apps if necessary, and then OneDrive.   Navigate to your project file, select it, and click the Download button at the top of the page to download your completed file to your computer. Proceed on to the next steps of the project.

Upload and save your project file.

 

Submit project for grading.

 

1. FIGURE 2-105 MIXED REFERENCE IN THE EDITED FORMULAIndependent Project 2-5

San Diego Sailing keeps data about its fleet of rental and charter boats. One of the sheets is missing a piece of data. 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: EX-OO-SanDiegoSailing-02.xlsx (Available from the Start File link.)

Completed Project File Name: [your name]EX-OO-SanDiegoSailing-02.xlsx

 

Skills Covered in This Project

• Create and copy formulas.
• 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.

Downloading and opening project files for Office Online Projects:

Download the Start file from SIMnet.   Navigate to Office.com and log in to your account if necessary. Click Upload and open…   Navigate to the location where your files are downloaded.   Click Open to open the file in the Office Online Excel app.   Only use the Office Online Excel web app to work on and edit your project file. Never open the start file to work on it or edit it using Google Sheets.

 

Open the EX-OO-SanDiegoSailing-02.xlsx start file. 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.

 

1. Review a formula.

Click the New Prices sheet tab and review the Formula bar.

 

Click cell D5. The formula begins with 1+D4.

 

1. Edit and copy a formula with mixed references.

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

 

Copy the formula in cell D5 to cells D6:D19.

 

Select cells D5:D19 and drag the Fill pointer to copy the formulas to cells E5:E19.

 

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.

 

1. Build a formula with mixed references.

Click cell F5 and type =(1+ to start the formula.

 

Select cell F4 and make it an absolute reference to the row but not the column.

 

Type )* for the closing parenthesis and multiplication.

 

Click cell I5 on the Fleet sheet, and make the reference absolute for the column but not the row (Figure 2-106).

 

Copy the formula down column F.

 

Format cells F5:F19 as Currency and then copy cells F5:F19 to cells G5:G19.

 

1. Build an IF function formula.

Click the Fleet sheet tab and select cell G5. Rental boats with a stove in the galley must seat 8 or more people.

 

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

 

1. Create a division formula.

Click the Bookings sheet tab and select cell F5. Calculate average revenue per passenger by dividing the fee by the number of passengers.

 

Build the division formula.

 

Copy the formula in cell F5 to cells F6:F19.

 

1. Create and copy a COUNTIF function to count bookings by boat manufacturer.

Select cell D27.

 

Start the COUNTIF function from the Statistical category by clicking the Insert Function button.

 

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.

 

Copy the formula in cell D27 to cells D28:D30.

 

Edit the criteria in each copied formula in cells D28:D30 to reflect the boat make.

 

1. Create and copy a SUMIF function to calculate total revenue by boat make.

Select cell E27.

 

Start the SUMIF function with cells $C$5:$C$19 as the Range.

 

Set the Criteria argument to ben*.

 

Set the Sum_range argument to cells $E$5:$E$19.

 

Copy the formula in cell E27 to cells E28:E30.

 

Edit the criteria in each copied formula in cells E28:E30 as needed.

 

1. Complete formatting.

Apply the Currency format to all values that represent money.

 

Format the labels in cells A1:A2 as 18 point.

 

Select cells A1:F1 and click the Merge & Center drop-down arrow [Home tab, Alignment group]. Choose Merge & Center. Repeat these steps for cells A2:F2 to merge and center the text in row 2.

 

Merge and center the label in cell C25 over cells C25:E25 and format it at 16 points.

 

Bold and center the labels in rows 4 and 26.

 

Select cells A4:F19 and apply All Borders. Do the same for cells C25:E30.

 

Acquiring your completed file for submission:

Click File and choose Save As.   If you would like to rename the file, click Rename and name the file as desired. Press Enter to finalize the name.   Click Save As to save a copy online.   In the Save As dialog, click Save or click the Save to another folder button. Click Save to save the copy on OneDrive.   Click the App launcher in the upper left hand corner.   Click All apps if necessary, and then OneDrive.   Navigate to your project file, select it, and click the Download button at the top of the page to download your completed file to your computer. Proceed on to the next steps of the project.

Upload and save your project file.

 

Submit project for grading.

 

1. FIGURE 2-105 MIXED REFERENCE IN THE EDITED FORMULA

"96% of our customers have reported a 90% and above score. You might want to place an order with us."