Overview
You are a new hire at a tax accounting firm that is expanding rapidly. The company has been successful because it hires the exact number of employees needed for each year. Your boss asked you to create a simple report in excel that shows how many employees will be needed by country/state. Your boss provided two tables: 1) Number of tax returns to be completed by country and by state, and 2) Number of tax returns each employee within a country can complete on average. (This differs from country to country because of the level of training, tax code complexity, and technology available to them)
Instructions
Download the data.
Fill columns D and E in the “# of Returns by location” sheet
Show the number of employees needed by country in a table.
Show the number of employees needed by country/state on a MAP.
Suggestions and Hints
There are many ways to combine the two tables. One way is to use the XLOOKUP function.
You can calculate the number of employees needed by dividing the # of returns to be prepared by # of Returns prepared by employee.
When calculating the number of employees needed, ensure that you round up any numbers. A fraction of a person can’t do any work J)
You are right, it was a very quick challenge. After, I explored mapping a bit more and learned about 3D maps in Excel. I played around with that and it is a pretty cool feature.
Wow, I like the map graph in Excel, I didn’t know there was one! I can see how this would be especially useful for presentations. I wasn’t sure which one to graph at first, but decided to graph both and liked the map with more detail in it. Nice quick challenge!!
Thank you for introducing me to this tool. A visual representation of data often makes the analysis much more understandable. I can see this being very useful for smaller organizations that cannot afford more expensive data visualization tools, or is inexperienced with them. In my map, I included the number of returns for each state:
Interesting mapping function I didn’t know about! I figured out how to put data labels with the number of preparers needed. If you put all of the data from “# of Returns by location” sheet as the chart data, you can delete the first two series and show the number of preparers. However, the data spans a large area of the map and can’t show all of the data labels unless zoomed in very closely.
Fun and simple challenge reminding me of the power that excel has. I forgot about the mapping function and it was nice to play around with it a little more. This was a fairly simple challenge that simply dabbles on the power that excel has.
I’ve also never used the maps before, but this was pretty neat! A very simple exposure to a new tool, but it’s a great way to visualize while staying in a familiar environment like Excel. Here’s my map:
That 3D map is one of the coolest things I’ve ever seen in excel. I had a lot of fun creating a tour of my globe. Certainly an incredible useful presentation tool within excel.
This challenge helped me with the XLOOKUP Function.
To find the average number of returns prepared by employees, I used the following formula:
=XLOOKUP(A2,'Avg # of Returns by Employee'!A:A,'Avg # of Returns by Employee’!B:B)
Prior to this challenge, I had never used the UNQIUE function before. I can now see how useful it is in eliminating duplicates from data. This is the unique function I used:
=UNIQUE(A2:A59)
The first map I made contains the number of employees needed by country/state. After I made that map, I decided to make a second one so it was easier to see the number of employees needed by US state.
After completing this challenge, I watched the answer video and I loved the “New Window” feature! I didn’t know it was possible to create a new window of the same workbook so that you can work on multiple sheets at the same time! Super useful! I used a vlookup and the a sumif for the number of employees needed.
This was a fun challenge to do after not using excel for a while. It helped me remember some of the basic functions and gave me a chance to play around with some of Excel’s amazing tools.
