Independent Project 2-5 These instructions are compatible with both...

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

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.
• UseCOUNTIFandSUMIFfunctions.
• Build anIFformula.
• Insert theTODAYfunction.

1. Open theSanDiegoSailing-02.xlsxworkbook.
2. If the workbook opens inProtected View, click theEnable Editingbutton 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.
5. Click theNew Pricessheet tab and review theFormula bar.
6. Click cellD5. The formula begins with1+D4.
7. Edit and copy a formula with mixed references.
8. Edit the formula in cellD5to showD$4instead of "D4." The formula should multiply one plus the percentage value in cellD4by the current rate on theFleetsheet($H5). With an absolute reference to row 4 and column H on theFleetsheet, you can copy the formula down the column (Figure 2-105).
9. Figure 2-105 Mixed reference in the edited formula
10. Copy the formula in cellD5to cellsD6:D19without formatting to preserve the border.
11. Select cellsD5:D19and drag theFillpointer to copy the formulas to cellsE5:E19.
12. Click cellE6. The formula is adjusted to use the percentage value in cellE4in place of cellD4. Note also that the reference on theFleetsheet($H6)is adjusted to show the correct row.
13. Build a formula with mixed references.
14. Click cellF5and type=(1+to start the formula.
15. Select cellF4and make it an absolute reference to the row but not the column.
16. Type)*for the closing parenthesis and multiplication.
17. Click cellI5on theFleetsheet, and make the reference absolute for the column but not the row (Figure 2-106).
18. Figure 2-106 Mixed references in the new formula
19. Copy the formula down columnFwithout formatting to preserve the border.
20. Format cellsF5:F19asCurrencyand then copy cellsF5:F19to cellsG5:G19.
21. Build anIFfunction formula.
22. Click theFleetsheet tab and select cellG5. Rental boats with a stove in the galley must seat 8 or more people.
23. IFfunction in which thelogical_testargument determines if there are 8 or more seats.
24. UseYesfor thevalue_if_trueargument. UseNofor thevalue_if_falseargument. (If you type the formula, enclose the text arguments within quotation marks.)
25. Copy the formula to cellsG6:G19without formatting and then center the data in column G.
26. Insert theTODAYfunction in cellB21. Format the date to show the month spelled out, the date, and four digits for the year (January 1, 2020).
27. a division formula.
28. Click theBookingssheet tab and select cellF5. Calculate average revenue per passenger by dividing the fee by the number of passengers.
29. Build the division formula.
30. Copy the formula in cellF5to cellsF6:F19.
31. make and copy aCOUNTIFfunction to count bookings by boat manufacturer.
32. Select cellD27.
33. Start theCOUNTIFfunction from theStatisticalcategory by clicking theMore Functionsbutton in theFunctionLibrarygroup.
34. Use cells$C$5:$C$19as theRangeargument.
35. Set aCriteriaargument that will select all boats in the "Beneteau" group. The criteria isben*. If you type the formula, include quotation marks.
36. Copy the formula in cellD27to cellsD28:D30.
37. Edit the criteria in each copied formula in cellsD28:D30to reflect the boat make.
38. Create and copy aSUMIFfunction to calculate total revenue by boat make.
39. Select cellE27.
40. Start theSUMIFfunction with cells$C$5:$C$19as theRange.
41. Set theCriteriaargument toben*.
42. Set theSum_rangeargument to cells$E$5:$E$19.
43. Copy the formula in cellE27to cellsE28:E30.
44. Edit the criteria in each copied formula in cellsE28:E30as needed.
45. Complete formatting.
46. Apply theCurrencyformat to all values that represent money.
47. Format the labels in cellsA1:A2as18point.
48. Select cellsA1:F2and click theAlignmentlauncher [Hometab,Alignmentgroup]. Center the labels across the selection.
49. Merge and center the label in cellC25over cellsC25:E25and format it at16points.
50. Bold and center the labels in rows4and26.
51. Select cellsA4:F19and applyAll Borders. Do the same for cellsC25:E30.
52. Center the page horizontally.
53. Save and close the workbook (Figure 2-107).
54. Upload and save your project file.
55. Submit project for grading.