Office 2019 Integrated Applications – Project 4 Access and Excel

by


  • Open the start file OF2019-Integrated-Project4.xlsx workbook. The start 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. 
  • If the workbook opens in Protected View, enable editing so you can make changes to the workbook. 
  • Enter a function in cell B1 on the Documentation worksheet to show the current date and time. You may need to change the cell formatting to properly display the information. 
  • Enter your name in cell B2 on the Documentation worksheet. 
  • Open the OF2019-Integrated-Project4.accdb database. If the database opens in Protected View, enable content so you can make changes to the database. Save the database to your local computer. 
  • Open the Stores table in design view, change StateProvince to State with a field size of 2. Save and close the table. 
  • Create a summary query that summarizes the total revenue for each store. 
  1. Use the following tables and fields to create your query:
    TableFieldStoresStoreIDTransactionsRevenue
  2. Your query will have 10 records. Modify your query by inserting the State field after the StoreID field, set the query so results will be in ascending order by State. Re-run the query.
  3. Close and save your query as query_RevenueByStore. 
  • Copy the query_RevenueByStore database object to a new query named: query_TransactionsPerStore
  1. Modify the query to count the total number of revenue transactions for each store instead of the total revenue. 
  2. In Query Design view, add a caption to the Revenue field so in the query results instead ofCountOfRevenue the column is named: NumberOfTransactions
  3. Run, save, and close your query.
  • Copy query_RevenueByStore to a new query named: query_RevenueByState
  1. Modify the query to show just the total revenue for each state. Do not include revenue by store. 
  2. Run, save, and close your query.
  • Create a new summary query that summarizes the total revenue generated for each product.
  1. Use the following tables and fields to create your query:
    TableFieldField2ProductsProductNameTransactionsUnitsSoldRevenue
  2. The query should display the total number of units sold and the total revenue for each product. 
  3. Set-up the query so the results are shown in ascending order by ProductName. 
  4. Resize the columns for best fit.
  5. Run the query. Your query will have 1,559 records. Close and save your query as: query_RevenueByProduct
  • Compact and Repair the database. Close the database and exit Access. 
  • Import query_RevenueByStore and format data. 
  1. Import the query_RevenueByStore as a table on a new worksheet into the OF2019-Integrated- Project4workbook. Confirm that all records were imported. 
  2. Rename the worksheet: RevenueByStore
  3. Move the worksheet after the Documentation worksheet. 
  4. Rename the SumOfRevenue column to: StoreRevenue
  5. Format the StoreRevenue column as Currency with no decimals.
  • Import query_TransactionsPerStore and format data. 
  1. Import the query_TransactionsPerStore as a table on a new worksheet into the OF2019- Integrated-Project4 workbook. Confirm that all records were imported. 
  2. Rename the worksheet to TransactionsPerStore
  3. Rename the CountOfRevenue column to: Transactions
  4. Move the worksheet to the end of the workbook.
  • Import query_RevenueByState and format data. 
  1. Import the query_RevenueByState as a table on a new worksheet into the OF2019-Integrated- Project4workbook. Confirm that all records were imported. 
  2. Rename the worksheet: RevenueByState
  3. Move the worksheet to the right of RevenueByStore if needed. 
  4. Rename the SumOfRevenue column to: StateRevenue
  5. Format the StateRevenue column as Currency with no decimals.
  • Import query_RevenueByProduct and format data. 
  1. Import the query_RevenueByProduct as a table on a new worksheet into the OF2019-Integrated- Project4 workbook. Confirm that all records were imported. 
  2. Rename the worksheet to: RevenueByProduct
  3. Move the worksheet to the right of RevenueByState if needed.
  4. Rename the SumOfUnitsSold column to: UnitsSold
  5. Rename the SumOfRevenue column to: ProductRevenue 
  6. Format the ProductRevenue column as Currency with no decimals.
  • Import Stores and format data. 
  1. Import the Stores table as a table on a new worksheet into the OF2019-Integrated-Project4 workbook. Confirm that all records were imported. 
  2. Rename the worksheet: Stores
  3. Move the worksheet to the end of the workbook.
  • The partners are interested in the revenue per square foot for each store.
  1. Navigate to the RevenueByStore worksheet and add a new column to the right side of the table named: RevenuePerSqFt
  2. Use VLOOKUP with the table on the Stores worksheet to create a formula to determine the revenue per square foot for each store. Hint: revenue per square foot equals total revenue per store divided by that store’s total square feet.
  3. Format the field as Currency with two decimals.
  4. Resize all columns of the table to best fit.
  • The partners are interested in the average revenue for each state. 
  1. Navigate to RevenueByState worksheet and add a column to the right of the table named: NumberOfStores
  2. Use COUNTIF to create a formula to count the number of stores for each state using the table on the Stores worksheet. 
  3. Format the column as Number with no decimals.
  4. Add a column to the right of NumberOfStores named AveStoreRevenueByState. 
  5. Enter a formula in cell D2 to calculate the average state revenue. Hint: Average state revenue equals total revenue divided by the number of stores.
  6. Verify that the column is formatted as Currency with no decimals. Update the column formatting if necessary. 
  7. Resize all columns of the table to best fit.
  • Insert a new worksheet at the front of the workbook named Summary. Change the color of the tab to Light Green from the standard color set.
  • Using the data in the RevenueByState worksheet, create the following charts. 
  1. Create a pie chart showing total revenue by state.
  2. Change the chart style to Style 10. 
  3. Change the chart title to: Revenue By State
  4. Move the chart to the Summary worksheet. 
  5. Create a horizontal clustered bar chart showing average store revenue by state. 
  6. Change the chart title to: Average Store Revenue By State
  7. Move the chart to the Summary worksheet. 
  8. Move the charts so the Revenue By State chart is at the left side of the worksheet and the Average Store Revenue By State chart is to the right.
  • On the RevenueByProduct worksheet, add a column named RevenuePerUnit to the right side of the table 
  1. Enter a formula in cell D2 to calculate the revenue per unit. Hint: Revenue per unit equals product revenue divided by units sold.
  2. Format the column as Currency with two decimal places.
  3. Resize all columns of the table to best fit.
  • On the RevenueByProduct worksheet, filter the data to show the Top Ten items based on RevenuePerUnit. 
  • On the TransactionsPerStore worksheet, add a column named RevenuePerTransaction to the right side of the table. 
  1. Enter a formula in cell D2 to calculate the revenue per transaction. Hint: Revenue per transaction equals the store revenue from the RevenueByStore worksheet divided by the number of transactions. Incorporate a VLOOKUP function in the formula using the StoreID value to ensure that you are using the appropriate store revenue in the calculation.
  2. Format the column as Currency with two decimal places.
  3. Resize all columns of the table to best fit.
  • Save and close the workbook.
  • Upload and save the project file.
  • Submit project for grading.