Excel 2022 Project Exp 22: Employees

excel 2022 Projectexp22 Excel Ch07 Ml2 Employees

Work for a clothing distributor with locations in Iowa, Minnesota, and Wisconsin. Use date and logical functions to complete the main employee data set, use database functions to calculate key summary statistics and create a map, and use financial functions to complete a loan amortization table.

Paper For Above instruction

The project requires comprehensive use of Excel functions encompassing data analysis, logical operations, database functions, and financial calculations. The goal is to develop an organized, dynamic employee data sheet, visualize regional salary data, and produce financial projections related to employee retirement planning.

Initially, the task involves data transformation using the SWITCH function to translate city names into their corresponding states. Utilizing the city data in cell D7, the SWITCH function evaluates the city and outputs the state—Iowa for Des Moines, Minnesota for St. Paul, and Wisconsin for Milwaukee. Following this, the function is copied down to fill the cells G8:G31, ensuring every employee's city is mapped accurately. This step aids in regional analysis of employee data.

Next, the focus shifts to date analysis. The hire dates in column C are used to extract the hire year through the YEAR function, filling out column H. This allows identification of hire decade or duration trends over the years. Subsequently, the YEARFRAC function calculates the total years each employee has been with the company by referencing the latest date in cell C2, providing insights into employee tenure durations.

The WEEKDAY function is then employed to determine which day of the week each employee's hire date falls on, returning numeric values which are formatted as weekday names with custom formatting (dddd). This could facilitate understanding of hiring trends related to days of the week.

Further, the EDATE function forecasts the month six months from each employee's hire date, aiding in targeted review scheduling or benefits planning.

Bonus calculation incorporates logical conditions through the IFS function, assigning bonuses based on performance scores. The scores are compared against thresholds stored in cells E2 to E4, with corresponding bonus amounts from F2 to F4. If an employee's performance score is 4.5 or above, they receive a $3,000 bonus; scores between 4.0 and 4.9 get $2,000; scores between 3.0 and 3.9 earn $1,000; below 3.0 earns none.

Similarly, salary adjustments for reps earning below a specified threshold are automated with nested IF and AND functions. If the job title is 'Representative' and the salary is less than a minimum specified in cell C3, a 2% increase (multiplied by current salary stored in C4) is applied. Otherwise, zero is returned.

The FV function helps project future retirement savings based on the initial salary, interest rate (APR), number of years, and additional contribution rate, illustrating long-term financial planning for employees.

Summary statistics are created via COUNTIF, SUMIF, AVERAGEIFS, MAXIFS, and MINIFS functions to analyze bonuses earned, total bonuses paid, average salaries by city, and maximum/minimum salaries for specific groups. These statistical summaries facilitate managerial insights into salary distributions and bonus allocations across the company.

A visual map representing average representative salaries by state is integrated into the workbook. The map chart is formatted with a title 'Average Rep Salary' and customized to display only regions with data, including labels for clarity, providing geographic visualization of salary disparities.

The Days Hours worksheet captures employee vacation and work hours data. The NETWORKDAYS function calculates actual workdays on vacation, considering predefined holidays (cell C1), which helps in planning and tracking employee leave. Additionally, the HOUR and MINUTE functions measure hours and minutes worked during special projects, enabling detailed analysis of project hours worked over specified timeframes.

In conclusion, this project combines multiple Excel functions to automate data analysis, generate visualizations, and support strategic decisions based on employee data. The final step involves saving and submitting the Excel file, ensuring all data calculations and visualizations are correctly implemented for comprehensive employee management and planning.

References

  • Heffernan, J. (2019). Excel Data Analysis: Your visual blueprint for analyzing data, charts, and PivotTables. John Wiley & Sons.
  • Walkenbach, J. (2018). Excel 2019 Bible. Wiley Publishing, Inc.
  • Chandoo, P. (2020). Excel Charts & Graphs (2nd Edition). Chandoo.org.
  • Microsoft Support. (2023). Use logical functions in Excel. https://support.microsoft.com
  • Microsoft Support. (2023). Database functions (DCOUNT, DCOUNTA, DAVERAGE, etc.). https://support.microsoft.com
  • Fastertrain. (2021). Advanced Excel functions: Mastering IFS, SWITCH, and nested IF statements. https://www.fastertrain.com
  • ExcelJet. (2022). Excel formulas and functions. https://exceljet.net
  • Investopedia. (2020). Future Value (FV) formula in Excel. https://www.investopedia.com
  • Excel Campus. (2022). How to create and format geographical maps in Excel. https://www.excelcampus.com
  • IBM Knowledge Center. (2021). Using financial functions in Excel. https://www.ibm.com

Related Assignments