[Solved] Excel problem

chili

New member
Dear All,

"I have conducted a salary analysis comparing our company's salary scale with that of our competitors. I have gathered data and created a pivot table, but I am unsure how to present the results to management in an organized and informative manner. The report should include the following details:
  1. Our Executive employees are not allowed overtime, but we are considering giving them an allowance when comparing the average overtime cost of non-executive employees. How can I show this in the pivot table? Additionally, what is the recommended allowance amount and how does it compare to our competitors?"
2. need give the details what is recommended allowance for executive employees to when comparing non executive employees.

Highly appreciate if you can help on this to prepare the format for this
 

Attachments

  • Salary Details - Production Department- New.xlsx
    48.3 KB · Views: 4
Dear Chili,
Greetings. I had trouble fully understanding your question. Therefore, if you could please provide more details regarding the question "Our Executive employees are not allowed overtime, but we are considering giving them an allowance when comparing the average overtime cost of non-executive employees" I will be able to provide a more accurate response.
 
Understood the issue & thank you for response.
Question is ,

1. I need to summary sheet of salary details with executive & non executive category employees obtain and what Maximum Overtime amount to give each category

2. Salary comparison of the each category, basic salary , total OT amount , total salary.

Hope you will clear this
 
To create an organized and informative report, you can follow these steps:

Step 1: Create a summary sheet that shows the following details for executive and non-executive employees:

The number of employees in each category
The minimum, maximum, and average basic salary for each category
The minimum, maximum, and average overtime amount for each category
The minimum, maximum, and average total salary for each category

Step 2: Create a pivot table that compares the salary details of each category. You can include the following columns:

Category (Executive or Non-executive)
Basic Salary (Minimum, Maximum, and Average)
Overtime Amount (Minimum, Maximum, and Average)
Total Salary (Minimum, Maximum, and Average)

Step 3: To compare the recommended allowance amount with that of competitors, you can create a separate table that shows the recommended allowance for executive employees and the average allowance offered by competitors. You can also include a column that calculates the difference between the recommended allowance and the average allowance offered by competitors.

To give the details of the recommended allowance for executive employees when comparing non-executive employees, you can create a separate table that shows the recommended allowance for each category and the difference between the two.

Make sure to include relevant charts and graphs to visualize the data and highlight key findings.

[Note: It's difficult to recommend a specific allowance amount without knowing more about your company and industry. However, you can compare your allowance amount to that of your competitors to get an idea of how competitive your compensation package is. If your allowance amount is lower than that of your competitors, you may want to consider increasing it to attract and retain top talent.]

Your question: I need to summary sheet of salary details with executive & non executive category employees obtain and what Maximum Overtime amount to give each category.

Answer:

You must follow the steps below to create a summary sheet of salary information with executive and nonexecutive category employees and a maximum overtime amount corresponding to each category.
  • Click on any cell in the data ⇒ go to the Insert tab.
  • Click on the PivotTable button ⇒ Select New Worksheet or Existing Worksheet radio button.
  • Press OK ⇒ Drag and drop the fields.
  • After pressing OK, a new sheet appears to the left of the current sheet. If you go to this sheet, you will see that the sheet looks like the following image.
  • We have to select or drag the Category and Basic and Design field to the Row area first, then we will select or drag the Total OT fields one after another.
-wUepJouEr3zW_QTT2Q8oUOFvsuRpNy6iRXM27C975Gcz9InQhs6gwwYoCYW3-9EpMvCc1ozsPl_bPoZxMnPS22qzieSoIxJRzxefthG03tFHe8VLSLXUeccxu3GPx80Dr228nrBMSWuavwRP9IObnU

  • Consequently, you will get the following Sum of the Total OT table, Now we want to get the maximum overtime amount corresponding to each category.
j4knkEQFm9aLADHoRJbbp4m4pc-PZjjGFO6rtcLew45Ko6sEcoYK_4R4YBp4VE8GLzAC7dsEG77vdnBeWUo0u2cD4h9ev-tgeCt_ZcC9JW4u09IXgDufUUrxPi27GN8RQB557W_Mnjq7AqFZpOu7yZk

  • Next, click any cell on the pivot table and right click on it and select Summarize Values By And click on the Max option.
ByFGTU-1vbGzZMJzSkPHWnAiN-Ejrxik5sC1sZ69m60F_8h7eqp4RPJbhZEv-v4PXiXWrnrcVaofN_k9o6Zv8aV5eCmgcpSeZcG_wsezhKBQeE0qh6MCtOYwTJObm9PpsrFLe_Qb7373I2F0pCKzMsE

  • Therefore, you will get a summary sheet of salary information with executive and nonexecutive category employees and a maximum overtime amount corresponding to each category.
42bFIF9E7ucLwsmajhTfF-RfWqneur09XFuOhso8llSN3VZsQ59YxZ60OUP19zHMeRWkjKy-JdqK6CWzmf2SFzhxTdm8nDqDtjY34O7dCeUYqEtlTmYPtZJvhlZHLe0TI2148ET9Pey8cS7Mkxq5hiY

  • If you want to know all the data click on the +icon of Executive and Non-Executive Labels.
  • Therefore, you will following details of the maximum overtime amount corresponding to each category.
vGIjb6k7b50lI0bOl78uJFIbX4EkH5VZicn0XdKc_nmikNGabHfyCxpEa9rmcrFhnAkvzMfChV1UB8qCdEovx_m9txd0JIb9B2bPfLOBHjAozbMexcpD3bkg0nLzXC0j0TTZcQYwEFuzRAtP4eBOJRM

  • If you want to customize your table based on your report purpose, you can select and drag Category into the Columns field.
dvcgWvTlQ8LeV2yf6ws5PL_W0_O-EtQfP6h2pMDarqIHzChAXS-xXqZLM4nKg4flO5lOEPLRpqMsD21axG05hbM43GIRfA4M1oRIAhHyjUaEb62-mLW_qV0m4KTtO7Gya1KTZvj4ZoHeHCVZJLcFxso

  • Finally, you will get a summary sheet of salary information with executive and nonexecutive category employees and a maximum overtime amount corresponding to each category.

gopLDCJnNoVyGkZRspvnpCTYfWUw_jy1h3WvSVbv7r12x4g1iJ-IT0mZGYV9aNiR4wY3HYqnF60PGiBj1gdrG4T8Iu0smdTNFsk36Cd9D4rO-rSBvCNBpNyE_mlmNrquwIfNJSiHaei5DNBJ-t5aacY


Your Question:

2. Salary comparison of the each category, basic salary , total OT amount , total salary.
How can I show this in the pivot table?
Solution:

To get a summary of the salary comparison of each category, basic salary, total OT amount, and total salary, you need to follow the below steps.
  • Click on any cell in the data ⇒ go to the Insert tab.
  • Click on the PivotTable button ⇒ Select New Worksheet or Existing Worksheet radio button.
  • Press OK ⇒ Drag and drop the fields.
  • After pressing OK, a new sheet appears to the left of the current sheet. If you go to this sheet, you will see that the sheet looks like the following image.
  • We have to select or drag the following field to the Row area first, then we will select or drag the other fields one after another.

98qiYjwzxiJJ4I13F0SBFfNGR99L5kqqMX3cwxW5tRbKVdaOmuZfgJhmj-GYZ5kXWxf4D7gdDHQcuq2rRi53Ir3DtWImapHR4V5v_vlMgkllb3hAWpW9c01vVjysUBh3a9hkTQ7Kuxk2LnhU-NFbcVc

  • Therefore, you will get a summary of the Salary comparison of each category, basic salary, total OT amount, and total salary
DN_k2T8dIY81okfVoRRvI-FanpxrqL-T-KVJA07DbPVybctEvQW-jIjlOj0Bl1_S9e9QY9Lp1KKxsa9SbrE797yXfCW814FkpCFF3rdsgt2P-ccNs1wW7C3Z1JjhPhB-Vd80ZpN1mCHbAi-fO-vRaTY

I am also attaching the Excel Workbook below.
 

Attachments

  • Salary Details - Production Department- New.xlsx
    82.2 KB · Views: 2

Online statistics

Members online
0
Guests online
9
Total visitors
9

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top