Integration and Expansion of Two Demand Prediction Workbooks

In Progress

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. [url removed, login to view]

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!!!!

Skills: Business Analysis, Data Processing, Excel

See more: you pull and pay, you on demand, x on demand, what z data processing, what is the cost of a business plan, what can i get for five dollars, want to start new business need help, two's company, the five dollars, tell me about myself, source for buying better, pull and pay, next gear, new five dollars, money unit, make money with excel, make 3000 dollars, keeping track of clients, initial business plan, i need some help to start my own business, in demand, how to get five dollars, how to get a better paying job, how much does it cost to make a business plan, greater giving

Project ID: #5067814

Awarded to:

seaanddream

Hello; your 5-star excel and vba coding expert is ready to help you building up your excel files to help your decision making process for your business. You may check my profile and previous feedbacks to have some idea More

$750 USD in 10 days
(59 Reviews)
6.0

11 freelancers are bidding on average $806 for this job

priyal1997

Dear Sir Please send me the current worksheets So I can review all these requirements and confirm the output Thanks Priyal

$750 USD in 7 days
(87 Reviews)
5.9
happy2helpp

Respected sir, Experts here!!! We saw project description and got complete idea about project. We are expert in Data Entry, Data Mining, Data Processing, Web Search and Web Scraping!!! We have worked on many similar More

$789 USD in 10 days
(39 Reviews)
5.8
elMancha

Hello there. I have high Maths, Excel and Visual Basic skills with great professionalism. I study electronics and computer engineering at Oporto university and I'm looking for work to fill the blanks on my schedule More

$750 USD in 3 days
(37 Reviews)
4.9
inoussakabore

I am a statistician engineer working as a data analyst there is 4 years. i am specialise in finance actuariat. I am ready to do this job for youy

$750 USD in 3 days
(3 Reviews)
3.3
Pulkitagarwal

Hi I am a Chartered Accountant working as a finance strategist in a fortune 100 MNC, working in the projects division. I have carefully understood your requirements about how you need to construct your excel sheet More

$750 USD in 4 days
(1 Review)
2.0
jasrobi

From the description of what you are requiring, it sounds like you need a good Strategic Asset Management database (excel) with forecasting and trending add-ons. I have created several of these in the past and feel th More

$1000 USD in 14 days
(0 Reviews)
0.0
somoo89

Hello, I am an Associate Chartered Certified Accountant (ACCA), having more than 3 years of professional experience of providing business solutions. My team consist of qualified professionals lead by senior Cert More

$888 USD in 3 days
(0 Reviews)
0.0
gumball89

Thank you for viewing my application. I have extensive experience in financial analysis and financial modelling due to more than two years working in investment banks and creating financial models and financial analysi More

$750 USD in 3 days
(0 Reviews)
0.0
AGHASHAHI

I would be happy to help you in this project. I am a ETL developer, and have integrated data for genrerating reports. I also have developed some automation tools in excel. So the technical skills sets me perfectly for More

$944 USD in 4 days
(0 Reviews)
0.0
tito5615104

Hello, I am chief accountant and managerial for 7 years ago. i made some researches for finance and money flow for company's. Financial Analysis, Business evaluation, Financial statements preparation, Data Management B More

$750 USD in 3 days
(0 Reviews)
0.0