2020: Week 6

This week's challenge is all about Conversion Rates. Any business that either buys or sells in multiple currencies will face the issue of what exchange rate to take.

Sadly for Chin & Beard Suds Co. the team have been capturing all the sales of the business in British Pounds as they have totalled all the sales together. Luckily C&BS Co have recorded what % of sales split is between the US and UK. The question our executives have is what is the total of USD revenue each week? Therefore, we have agreed to provide a best case and worst case conversion rate for the US sales each week. Now we just need to work out what that range and variance is...

Requirements

Exchange Rates (GBP to USD)

Sales Value by Week

  • Input the data set
  • Determine the best and worst GBP to USD exchange rates on a calendar week basis
  • Take the Sales data and determine the UK / US Split
  • Apply the UK / US sales split to the value sold in GBP
  • For the calendar week US sales, determine:
    • What's the Maximum Sales value for US sales in USD
    • What's the Minimum Sales value for US sales in USD
    • What's the weekly variance could be
  • Output the data

Output


  • 5 columns:
    • Week
    • UK Sales Value (GBP)
    • US Sales (USD) Best Case
    • US Sales (USD) Worst Case
    • US Sales Potential Variance
  • 22 Rows (23 including headers)
The outputs can be found here for comparison. Don’t forget to fill the participation tracker and share your solutions using #PreppinData on Twitter.

Comments

Popular posts from this blog

2019: Week 1

How to use... Custom SQL in Prep Builder

What's Preppin' Data about?