Hi -- before I dive in on learning MS Access, I just want to make sure that I'm going to be able to accomplish what I'm looking to do and which I'm struggling to do in Excel. Any thoughts on this would be much appreciated!
So this is for a professional services firm and the general business model is employee hours X bill rates = revenue. Most employees are salaried. I'm trying to build a model that calculates billing capacity and also salary expense. Both of these are driven by various attributes about individual employees (their standard bill rates, their PTO allowance for the year, what % of their annual working hours should be billable, their monthly salary, their planned salary increases, promotions that change some of the previously mentioned attributes, etc., and a number of effective dates for all these things).
So when I'm planning for the full year, there are a number of changes that get planned for by employee for specific dates. I want to be able to have salary cost and billing capacity automatically re-calculate as I enter various changes and effective dates of changes. For example, if I have a new hire planned for 4/1/16 and then change it to be for 5/1/16, I want the salary expense and billing capacity for April to be zeroed out. That itself is easy enough to do in Excel (and this is a very simple example), but the hard part comes with the fact that we do monthly re-forecasts and have to be able to explain the changes from one forecast to the next. So I need to save what was planned in one forecast and then add to it (sort of like copying and pasting it all and tagging it with a different forecast name and then making specific changes to it -- or adding new records that add to / subtract from the prior forecast). Additionally, changes need to be tagged with one of about 10 different "reason" categories. So all this ends up being a lot of "records" to store and do calculations on, and there are a lot of dimensions related to all these records / used in the calculations. I probably could build this in Excel / PowerPivot, but it feels like I'm trying to use Excel as a database which I know people frown upon. I'm sure I'll uses Excel / PowerPivot for some of the analysis, but I think what I'm needing to do is to use something else (Access) for holding all the records and maybe for calculating all the monthly amounts.
A big part I'm trying to solve is how to have monthly records added as needed based on effective dates. In Excel, I have to make 12 spots for data to flow into if something is happening for an employee for every month. But what I want is to not have to add 12 spots "just in case" -- I want the model to just look at the effective dates and create the monthly calculations for the months they are actually needed for. I don't know if that means it automatically adds records to tables based on effective dates, or if it just calculates all these monthly amounts via queries? I hope this question makes sense. Is this something that Access would do better than Excel? I don't know how to make Excel add rows / records to a table based on how many months are between two dates (I would guess it could be done with VBA, but that feels like a long way off for me).
Thank you for any help in understanding which direction I should go with this.