Excel Workbook Assignment
On Sheet 1: Employee Compensation Table
1. Create the table shown below:
- Rename Sheet 1 to Compensation.
- Use Wrap Text and Merge Cells as appropriate.
- Apply Borders and Shading to style the table.
2. Formatting:
- Format B2 to display as a Short Date.
- Format E4:G6 to show currency with two decimal places.
3. Calculations:
- Gross Income: In cell E4, multiply Hourly Rate by Hours Worked.
- Tax Deduction: In cell F4, calculate 6% of Gross Income.
- Net Income: In cell G4, subtract Tax Deduction from Gross Income.
4. Header and Footer:
- Add a Header with your Name on the left and your Student ID on the right.
- Add a Footer with the Current Date centered.
On Sheet 2: Product Pricing Table
1. Rename Sheet 1 to Product Pricing.
2. Create a table with the following functions:
- Calculate TAX: If the ITEM PRICE is less than 100, the TAX is 50; otherwise, it is 100.
- Pre-Tax Total: Multiply Number of Items by Item Price.
- Post-Tax Total: Add Pre-Tax Total to TAX.
- Rate: Assign HIGH if Post-Tax Total > 3500, LOW otherwise.
3. Summary Calculations:
- Compute:
- Average of TAX Values
- Minimum Item Price
- Maximum Item Price
On Sheet 3: Sales Analysis Table
1. Create a table to analyze sales:
- Calculate:
- Average Sales and Maximum Sales for each City.
- Total Sales for each Month.
- Profit for each month (Profit = Total Sales - Cost).
2. Charts:
- Create:
- A 3D Clustered Column Chart showing Monthly Total Sales.
- A Pie Chart representing City-Wise Sales Distribution.
