CIS110 Cuyamaca Information System Profit Calculation Excel Assignment

Part 1 – ACCESS – Update and add content to the database topics covered in Access chapter 1

  1. Each student has a unique file for this assignment.
    Click the button to agree to the conditions of this assignment and create your data file downloads.
  2. Save the file with the name Yousif.Mikha_Exam5 (your file should automatically be named)
  3. Start Access and open the database file you downloaded.
    If you see the yellow security warning then click the Enable Content button.
  4. Open the tblCustomers and verify that there are 3830 records in the table. – View Example
  5. Create a new table named tblTours with the following database structure
    Field Name Data Type Size / Type Primary Key? Description
    TourID AutoNumber Long Integer Yes Tour ID key field
    TourName Short Text 22 Name of Tour
    Capacity Number Long Integer Maximum Per Tour Group
    Expense Currency Currency Cost to Provide Tour
    TicketPrice Currency Currency Ticket Price
    FuelSurcharge Yes/No Fuel Surcharge Applicable
    SurchargeAmount Currency Currency Amount of Fuel Surcharge

    Figure 1 – tblTours Structure

  6. Input 7 records of data into tblTours

    TourID TourName Capacity Expense TicketPrice FuelSurcharge SurchargeAmount
    1 Canyon Narrows 6 19 55 No 2.10
    2 Eagles Landing 6 22 55 Yes 3.25
    3 Painted Canyon 18 26 75 No 1.50
    4 River Float 12 31 65 No 1.00
    5 Silver Springs 12 21 45 Yes 2.50
    6 Sunset Photo 24 15 30 Yes 3.10
    7 Valley Explorer 50 10 25 Yes 4.50

    Figure 2 – tblTours Data

  7. Create a filter to display only the records which have FuelSurcharge set to Yes
  8. Save the changes to the table

Part 2 – Import Datatopics covered in Access chapter 1

  1. Import the tblReservations text file you downloaded in step 1 as a table named tblReservations.
    – Do not add an additional field as a key field or index during the import.
    – You will select a key field from the existing data in the imported file contents the table design update.
    – Any ImportErrors tables will be a deduction and indicate you had an error during the import.
    – Be sure to correct the import error and delete and ImportErrors tables if they exist.

Part 3 – Update Table Designtopics covered in Access chapter 1

Modify table design following for tblReservations following database design methods covered in your text. See your Access e-text chapter 2 SLO 2.1 & 2.2 to review setting PK and field properties.

  1. Set appropriate field types, format, and sizes.
  2. Analyze the fields in the table and select the field which would be a logical choice for key fieldfrom one of the fields you imported.
  3. Save changes to your tables.

Part 4 – Create Relationshipstopics covered in Access chapter 2

Note: Use the following as a guide in creating your relationships.

  • Customers make reservations.
  • The reservations are for tours.
    For additional information see your Access e-text chapter 2 SLO 2.6 beginning pg A2-112.
  1. Create a relationship between the tables by analyzing the fields in each table and creating appropriate relationships.
  2. Add Enforce Referential Integrity and Cascade Update Related Fields to all relationships.
  3. Save the changes to the relationships

Part 5 – Create 4 Queriestopics covered in Access chapter 3, examples on class video page

Note: Sorting queries must be done in the query design grid not on the data sheet.

  1. Create and save a query named qryAllReservations. This query will include the following fields in this order, CustomerID, TourName, FirstName, LastName, State, Adults, ReservationMonth, PaymentType. Sort the query on the CustomerID field in ascending order.
  2. Create and save a query named qryNMMayRiverFloatCustomers.
    This query will include only River Float reservations in May from New Mexico (NM) customers displaying only the following fields in the query results: FirstName, LastName and number of Adults.
    Sort the query
    by LastName in ascending order.
    See example (your data may be different)
  3. Create and save a query named qryMayTourTotals.
    This query should be grouped by Tour Names with the total number of Adults having reservations in May and listing the number of adults in descending order.
    Change the Caption of the SumOfAdults to Adults.
    The query results should only display the Tour Name and number of Adults fields.
    See example (your data may be different)

    California requires companies to collect tax if they make sales and have an office in California.
    The tour company has an office in California so you need to calculate how much to collect.
    The California tourism tax is $1.85 for each adult ticket sold to Californians.

  4. Create and save a query named qryCaliforniaTax from tblCustomers and tblReservations tables for the total of Adults from California.
  5. Add a calculated field to the qryCaliforniaTax query by adding an expression multiplying the sum of the of Adults from California times the $1.85 tax amount.
  6. Set the name of the calculated field heading to Tax and and the caption of the SumOfAdults to Adults.
  7. Format the calculated Tax field as currency. See example (your data may be different)

Part 6 – Create a Formtopics covered in Access chapter 4

  1. Create a form named frmReservations including only the following fields:
    ReservationID, FirstName, LastName, State, TourName, Adults, ReservationMonth, PaymentType.
  2. Select to view your data by tblCustomers as a single form.
  3. Change the title of the form to Red Rock Reservations. See example

Part 7 – Export Data to Excel, set option to compact on close

To perform further analysis and create a chart you need to export the reservations to a new workbook.

  1. Export the query qryAllReservations as an Excel file. See class video
  2. Name the new exported workbook file Yousif.Mikha_Exam5
  3. Set the compact on close option for the database. See class video
  4. Exit Access

Part 8 – EXCEL – Format Workbooktopics covered in Excel chapter 1

Use professional formatting except where you are given specific formatting instructions.

  1. Start Excel and open the spreadsheet you exported from Access
    Verify that you have 3830 rows of data, if not then you have an error in your Access export.
  2. Add the following 3 document properties via the Document Properties panel.
    Author: yousif.mikha
    Title: Exam5 Spring 2018
    Comments: location where you completed the exam examples
    if you completed it at home then list – “my home system
    if you complete it on campus then list the room and computer number
    using room E206 system 32 would be entered as – “E206 system 32
    using the Tech Mall system 15 then list – “Tech Mall system 15
  3. Change the worksheet tab name to All Reservations
  4. Insert a new row 1 (moving all the data down 1 row)
  5. Enter the title All Reservations into the inserted row, add a background color and increase the size.
  6. Format the column titles in row 2 to look exactly like the example below using line breaks and setting the column widths as follows: Customer ID 11, Tour 15, First Name 11, Last Name 11, State 8, Adults 8, Reservation Month 11, Payment Type 15. These widths are in pixels not inches.
    Note: Adding spaces to make the titles look correct will be graded as an error.
    row format
  7. Insert the current date header/footer element in the left header area and insert the file name header/footer element in the center header area and type your name in the right header area.
    Header Footer
  8. Freeze rows 1 & 2 and column A so they do not scroll.

Part 9Import Access Table into Excel topics covered in Excel chapters 1, 2 & 4

  1. Import all tblTours records from your Exam 5 database – Yousif.Mikha_Exam5 into a new worksheet.
    If you do not import the database table using the import command there will be a deduction.
  2. Adjust the column titles adding a space to split the words and adjust column widths so the text is completely visible.
  3. Calculate the Profit for each tour in column H. The profit is the difference between the Ticket Price and the Expense.
  4. Calculate the Surcharge Total in column I.
    The surcharge total is the Capacity times the SurchargeAmount.
  5. Insert a column between H and I. Enter a column title of Total Profit.
  6. Calculate the Total Profit for each tour where the total profit is the Profit times the Capacity.
  7. Hide columns F and G.
  8. Insert a new row 1 moving all the data down 1 row
  9. Enter the title Tour Calculations in row 1 and format it
    the first 2 rows in your worksheet should look like this
    tour calculations
  10. Calculate the following for the Surcharge Total column, Minimum in row 11, Average in row 12, Maximum in row 13. Should look like this:
    rows 11 - 13
  11. Format the values for the Minimum, Average, and Maximum with 0 decimal places
  12. Name the worksheet tab Calculations

Part 10Create the Filtered Reservations Worksheettopics covered in Excel chapter 4

  1. Copy the All Reservations Worksheet into a new worksheet
  2. Change cell A1 text to Filtered Reservations and set the background using a different color than the All Reservations worksheet
  3. Create a table from the data in the Filtered Reservations worksheet
  4. Sort the Sales data by Tour in ascending order
  5. Add a filter to display only River Float sales where the number of Adults is 1. See example
  6. Change the name of the worksheet tab to Filtered

Part 11 – Create the Pivot Tabletopics covered in Excel chapter 8

  1. Create a Pivot Table from the All Reservations worksheet
  2. Set the pivot table as follows:
  • ReservationMonth as the Row Labels
  • Sum of Adults as the Values
  • Tour and State as the Report Filters
  1. Apply a filter to display only the River Float reservations.
  2. Name the worksheet tab Pivot Table. See example

Part 12 – Create a Tour Chart topics covered in Excel chapter 3

  1. Create a Column Chart of the monthly totals from the Pivot Chart worksheet.
  2. Use the Move Chart command to move the chart to a new new worksheet and name it Tour Chart
  3. Add / Change the Chart Title above the chart to Monthly Totals
  4. Delete the chart legend. See example

Part 13Finish and Submit

  1. Create a new worksheet.
  2. Add a title to the worksheet and worksheet tab using the name Links.
  3. Move the Links worksheet tab so it is the first tab on the left.
  4. Add the name for each worksheet and then link the name to the respective worksheet.
    You don’t need to have a link to the Tour Chart.
  5. Format your links worksheet to look professional.
  6. Change the color of each worksheet tab as follows and arrange them in this order from left to right with Links being first and Tour Chart last.
    Links Red
    All Reservations Light Gray 25%, Background 2, Darker 25%
    Calculations Blue, Accent 1, Lighter 40%
    Filtered Gold, Accent 2, Lighter 40%
    Pivot Table Green, Accent 6, Lighter 40%
    Tour Chart Orange Accent 2, Lighter 40%

    view Theme colors

  7. Upload your Exam 5 files by clicking the
    Exam 5 Access link for your Database – Yousif.Mikha_Exam5 and
    Exam 5 Excel link for your Spreadsheet – Yousif.Mikha_Exam5
Grading Rubric
Points Requirements
10 Create new database and Tour table, enter data
10 Import data and update table design
10 Add relationships
20 Queries
10 Form
60 Total of Exam 5 Access file
10 Export data from Access and import to Excel All Reservations
10 Calculations worksheet
10 Filtered Reservations worksheet
10 Pivot Table
10 Tour Chart
10 Summary worksheet, correct tab order/color
60 Total of Exam 5 Excel file
120 Total possible for uploading
Yousif.Mikha_Exam5 Database
Yousif.Mikha_Exam5 Spreadsheet