Client Name Address Phone Number City ZIP Product Service Da ✓ Solved

```html

Client Nameaddressphone Numbercityzipproductservicedate

Independent Project 4-4 Eller Software Services has received contract revenue information in a text file. You import, sort, and filter the data. You also create a PivotTable, prepare a worksheet with subtotals, and format related data as an Excel table.

Files Needed: EllerSoftware-04.xlsx and EllerSoftwareText-04.txt.

Completed Project File Name: [your name]-EllerSoftware-04.xlsx

Skills Covered in This Project: Import a text file, Use AutoFilters, Sort data by multiple columns, Create a PivotTable, Format fields in a PivotTable, Use the Subtotal command, Format data in an Excel table, Sort data in an Excel table.

This project involves a series of steps to effectively manage and analyze client and service data using Microsoft Excel. You'll explore various functionalities including importing, sorting, filtering, and presenting data in an insightful manner. Below are the steps you can follow:

Step 1: Preparing the Excel File

Begin by opening the provided EllerSoftware-04.xlsx file. Make sure to enable editing and save it under a new name comprising your own name for easy identification.

Step 2: Importing Data

Import the text file EllerSoftwareText-04.txt, which contains the relevant data needed for analysis. This data should be loaded starting from cell A4, and since it's tab-delimited, Excel should correctly parse the information into appropriate columns.

Step 3: Formatting Columns

Format column H as currency with zero decimal places. This will give a clear indication of contract amounts without unnecessary decimal points affecting readability.

Step 4: Sorting Data

Use AutoFilters to sort the data first by date (oldest to newest) and then by product/service name in ascending order. This ordered arrangement will simplify locating specific contracts related to various services.

Step 5: Filtering Data for September

Apply a filter to the Date column to show only contracts that fall under the month of September. Adjust the label in cell A2 to clearly indicate that the data displayed pertains to contract amounts for September.

Step 6: Formatting the Header

Select the header cells A1:H2 and change their alignment by opening the Format Cells dialog box, where you can center the titles. Change the font size for these cells to 20 pt for better visibility.

Step 7: Creating a Data Sheet

Copy the current Contracts sheet to the end of the workbook and rename the copy as Data. After that, clear any filters applied so that the entire dataset is visible.

Step 8: Creating a PivotTable

Select the range for the PivotTable using the PivotTable button located in the Insert tab. Make sure New Worksheet is selected, and do not add the data to the Data Model. This PivotTable will allow you to summarize the revenue data effectively.

Step 9: Configuring the PivotTable

In the PivotTable, display the Product/Service and Contract fields. Add the Contract field twice in the Values area to allow for different calculations.

Step 10: Customizing Field Settings

Customize the settings for the Total Contracts and Average Contract fields in the PivotTable to reflect accurate financial figures formatted in currency with zero decimal places. This will enhance the financial analysis aspect of the project.

Step 11: Copying and Labeling

Copy identifiers from the Data sheet to the top of the PivotTable sheet to provide context for the displayed information. Adjust alignment and font size accordingly.

Step 12: Creating a Subtotals Sheet

Create another copy of the Data sheet and rename it to Subtotals. Sort this new sheet by City (A to Z) and utilize the Subtotal command to calculate a sum for contract amounts by each city.

Step 13: Final Formatting

Convert the table into a standard range, apply any necessary formatting for clarity, and ensure that the report is oriented correctly to fit on a single page for easier presentation.

Step 14: Building Hours Report

Navigate to the Billable Hours sheet, create a formatted table, and add a 5% add-on entry. Formulate comparisons through multiplication and alignment adjustments for reporting purposes.

Step 15: Final Steps

Save and close your workbook. The project is now ready for submission. Remember to ensure all changes are saved correctly and that your workbook's content is reflective of all requirements detailed above.

Conclusion

This project demonstrates the comprehensiveness of Excel in managing business data. By following these steps, you will have successfully extracted, analyzed, and presented client contract data effectively.

References

  • Microsoft Support. (n.d.). Import text files in Excel. Retrieved from https://support.microsoft.com/en-us/excel
  • Microsoft Support. (n.d.). Create a PivotTable. Retrieved from https://support.microsoft.com/en-us/excel
  • Excel Easy. (n.d.). Excel Pivot Table Tutorial. Retrieved from https://www.excel-easy.com/data-analysis/pivot-tables.html
  • Chandoo. (n.d.). Learn Pivot Tables. Retrieved from https://chandoo.org/wp/excel-pivot-table-tutorial/
  • Contextures. (n.d.). Excel Subtotals Tutorial. Retrieved from https://www.contextures.com/xlSubtotals01.html
  • Excel Campus. (n.d.). Introduction to Excel Tables. Retrieved from https://www.excelcampus.com/tables/
  • ExcelJet. (n.d.). Excel Filter Basics. Retrieved from https://exceljet.net/excel-filter-basics
  • Ablebits. (n.d.). The Best Guide to Excel Tables. Retrieved from https://www.ablebits.com/office-addins-blog/2015/08/17/excel-tables/
  • Excel Off The Grid. (n.d.). How to Create and Use an Excel Pivot Table. Retrieved from https://exceloffthegrid.com/excel-pivot-table/
  • Ablebits. (n.d.). Working with Text Files in Excel. Retrieved from https://www.ablebits.com/office-addins-blog/2015/10/01/work-with-text-files-in-excel/

```

Related Assignments