BYU Student Author: @Mark
Reviewers: @Alex_Garrett
Estimated Time to Solve: 45 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You work as a staff accountant (aka “Number Gunner”) at Big Bill’s, a company that sells high quality dart guns in many different countries worldwide. The company uses sales report software that doesn’t automatically change exchange rates each day. Big Bill’s management (aka “Top Gunners”) change the company’s fixed exchange rates in the system occasionally, but not very consistently. The CFO (aka “Combat Financial Officer”) wants to update the rates for 2023. She has given you orders to figure out the discrepancies in sales stemming from differences between the company’s fixed rates and the actual daily exchange rates in December. She sent you a file with the December Sales Totals in their foreign currency amounts and the current Fixed Rates.
Luckily, your fellow Number Gunner is very skilled with his Python Pistols and scrapes the daily rates for the currencies you need. He adds a sheet with the Actual Rates. You present him with a gold star for excellence and get to work…
Instructions
You determine that you need to create three specific columns in the “Dec Sales” sheet (though you may add as many as needed):
- The first is the Order Total in USD using the fixed rates found in the “Fixed Rates” sheet.
- The second is the Order Total in USD using the actual rates found in the “Actual Rates” sheet.
- The third is the difference between the two previous columns, calculated as (Actual USD Sales – Fixed USD Sales), which will help the CFO know the difference between recorded sales in USD and actual sales in USD.
The final product will include three Pivot Tables and Two Pivot Charts:
- The first Pivot Table will be a breakdown of the rate discrepancies in dollars by currency. The CFO wants to know which currencies have overstated sales and which have understated sales. Rank the currencies from greatest (understated) to least (overstated). Create a monochromatic Pivot pie chart with the discrepancy breakdown by currency. Add data labels that display the currency name and the percent of the whole that each currency represents. Make each data label easily readable, or else the CFO will demote you to “P-Shooter”.
- The second Pivot Table will not include a Pivot Chart. The CFO has ordered you to display the total USD sales based on the Fixed Rates by currency, the total discrepancy associated with the currency, and the discrepancy as a percentage of the sales column. Sort the table by the percentage of sales column ascending. Which fixed rates are “missing the mark” the most?
- The third Pivot Table will rank each country by its sales in USD. Each month, the top ten countries’ soldiers receive different prizes and medals based on their ranking among the other countries. This ranking is based on the USD Sales calculated using the fixed rate. In the Pivot Table, include only the top ten countries (full country names) for December based on the fixed rate sales. Include a column displaying the USD Sales based on the Actual Rate as well. Are there any countries whose sales rankings should change based on the sales numbers using actual rates? Create a Pivot clustered bar chart to investigate. Which countries’ soldiers need to trade prizes?
Data Files
Suggestions and Hints
- The sales already denominated in USD do not need to be changed (ie, the exchange rate discrepancy should be 0).
- If you want to use XLOOKUP to return results based on values in two different “lookup” columns, you can concatenate the two “lookup” columns and use the concatenated column as a unique identifier for the value you’re “looking up.”
- You will need to use a calculated field for the second Pivot Table.
Solution
