Integration and Expansion of Two Demand Prediction Workbooks

I have an equipment rental company and I need an effective way to plan my capital investments in inventory. I already have two predictive workbooks but I want to combine them and add another functionality. I currently source or "subrent" much of my inventory from third parties in exchange for a percentage of the rate I charge my clients. If, for example, I need a lens and I’m going to charge the client $50, for it, I can source it for that rental and may pay out 50% of the $50 dollars to the owner of the lens. I make $25, they make $25. This has been great for my business and has allowed me to build a large and loyal customer base with little or no investment in inventory. However, now that I have the clients and the revenue, it makes more sense for me to start owning more and more of the items I own and paying out less and less to third party owners. I here is download link to the two excel files I have already commissioned. [login to view URL] The first predictive workbook does a good job of calculating demand and estimating how many of each item I need to fill historical demand. However, it currently only predicts the performance of up to five items of each kind. While this is fine for much of my gear, there are some items I would estimate wanting to own up 20 of so that will need to be changed. The second predictive workbook does much the same but is more forward looking and predicts demand into the future better. It’s also in much prettier and easier to use package and does an excellent job of giving detailed information on an item by item basis. The major thing that’s lacking between these two worksheets and the reason I need the help of an excel wizard is in the end, instead of just telling what and how many of each item I can expect to rent. I need to know what to spend my money on. Let’s look it this way: If I buy one of item X the spreadsheets I have can already tell me that it will return income Y over the course of its rentable life. However, if I forgo buying it and instead source it from other people, it will return income Z over the same period. Again, the spreadsheets I have can tell me that as well. Here’s what’s new: If you divide Y by Z you get a new number Q. If Q is greater than 1, it makes more sense to purchase that item myself than to source it. The same calculation could then be applied to the second unit of that same item and so forth. Once that functionality is added to the workbooks, I would like to be able to pull a list that ranks all items in order of their Q value. That way I can look at the list and see that the first, second and third X all have positive Q values (better to buy than to rent) but the fourth doesn’t make sense to own. For example: I an item costs $5000 to purchase and is expected to rent 100 times per year for and average rate of $40 per rental and I predict it will remain rentable for 2 years from the date for purchase then it would be projected to make me $8,000 dollars total and provide a return of $10,000 on my initial investment of $5000. On the other hand, if I were to acquire that unit from other owners and I projected that it would cost me about 60% of what I charged my clients, I would be paying out $4800 of the $8000 I charged. Thus, I would net $3200 on the same unit and make $200 more by not buying but continuing to acquire it from other owners. This item would have a q value of 3000/3200 or .93 The tricky part here is going to be keeping track of multiple items. You’re going to see a lot of “C-Stand” rentals in the data. The first c-stand I buy may have a Q value of 10 , the next a value of five and so on until we get to number 12, when the Q value finally drops below 1. That means I need to buy 11 C-stands and subrent the 12th when and if I need it to fill orders. Please take a look at the spreadsheets I have and ask all the questions (I’m sure you’ll have them) before bidding. Looking forward to working with you!!!!