I'm trying to create a dashboard on lost revenue/margin from open job postings. Part of this dashboard will be a lost revenue/margin rate, these rates are calculated in Excel for each open position based on their hourly pay and how many days the position has been open. The attached workbook uses a simplified version of the spreadsheet I'll be using, it is not feasible to change the spreadsheet in a way that could more easily accomplish my goals (i.e. have an entry for each day a requisition is open). The issues I'm having are detailed below along with what I would ideally like to accomplish, any nudge in the right direction would be much appreciated. Thanks!
- In the attached, I tried to get a line chart that shows the daily lost margins since 6/1/15. Unfortunately it only counts each open job requisition a single time when the job was posted, whereas I need that daily margin shortfall to be added together every day until the position has been filled. For example, the first listed job requisition costs the company $175 lost margin for every day it's open. There were 22 work days in June 2015, so the margin shortfall for June should start at $175 and grow to $3850. Then on July 1st, the second listed job requisition should add another $186 margin shortfall per day. These amounts should keep adding to the total until the positions are filled, with additional requisition rates being added in as they're opened.
- In a perfect world, I would be able to show the current lost revenue/margin, the lost revenue/margin trend over the last 30/60/90 days, and also give the ability for the user to pick their own timeframe using a sliding filter. Essentially, I want part of the dashboard to show "this is the current lost revenue/margin from all open positions as of XX/XX/XXXX, this has increased/decrease by Y amount over the last 1-3 months". I'm not sure how realistic it is to have the past 90 days trending info while also wanting to give the user the ability to pick their own timeframe, but hopefully someone knows for sure.
Thanks in advance for any help you might be able to give me!