Customer optimization

[Eighty888]

Hello,

I am trying to solve a customer optimization problem where I have 3 different kinds of customers, each of whom bring in a different amount of revenue and have different costs associated with them. I am really stumped on this and I'm not sure where to begin. The goal is to find the optimal mix of customers to maximize revenue on a monthly and yearly basis.

I have tried using solver and scenario manager but I may not be using them correctly. I've watched "how-to" videos on Youtube to learn how to use these tools appropriately but they all focus on products and their parameters are different, leaving me confused still.

Below is a link to a screenshot I took of the worksheet
Imgur: The most awesome images on the Internet (i checked the faq and I think links are OK?)

Is anyone familiar with these types of problems and how I might go about solving them?

Thank you for your time!

 

[iliace]

It sounds like you are dealing with two separate issues: one, modeling out what your customer mix should be; and two, analyzing the scenarios of acquiring those types of customers. You should separate them out into two separate models.

Another issue you are dealing with, pertaining specifically to the customer mix model, is how many of each type of customer you can realistically obtain. You need to have a separate model for your prospects, which will dictate the maximum number of customers of each type. For example, if your most profitable customer is the Large Business, having 1 million of them would be your ideal scenario. However, you most likely do not have that many prospects; you probably also do not have the capacity to service that many Large Business customers.

In the model you showed, it looks like you need to do two types of projections: cash flow, and something like IRR or similar long-term investment measure. Because the cash flow projection will determine whether you can continue operations, this needs to be a constraint.

So, to sum it up, here is how I think you should set up your model.

1. Customer mix, based on the number of customers and/or prospects from each customer category.
2. Cash flow from the customer mix, based on expected payments, net of uncollectibles. This should be based on timing of the billings and receivables.
3. Expense associated with the customer mix, based on your costs incurred for each customer. This should match the timing in 2.
4. Net cash available - a constraint to ensure you can keep operating at any given time. This also includes any borrowings you have to make against credit lines, if necessary.
5. Total future returns - again, associated with 4. This will allow you to calculate an ROI, or similar measures.
6. R&D and SG&A expenses related to future returns, to arrive at Net Income

Those are all the items you will need to provide Solver so that it can give you an optimized solution. But you have to be very careful making sure all your inputs and constraints are clearly defined, otherwise it will take you to infinity and beyond, or (worse) tell you there is no feasible solution.

This is a complex problem that will require a lot of trial and error on your part. I hope you succeed.

 

[Eighty888]

Hi Iliace,


Thank you for your detailed response! My main issue is using Solver correctly here. I am not sure how to use it to find the optimal customer mix. I was watching Youtube videos but they are always just a bit different then my current project, making it difficult for me to translate the instructions from those videos into a feasible solution for my current project. Do you have any idea how to apply Solver towards finding an optimal customer mix? I can figure out the rest from there!

Again, thank you for your kind response!

 

[iliace]

Well, that's the thing - Solver is the last part of the equation here. Before using it, you need to set up the model, using some semblance of parameters I listed above.

Once you do that, in the Solver dialog, you will want your "Objective" cell to be wherever your Net Income or ROI is. Set it to "Max", and "By changing variable cells" is the number of customers in each category.

Some of your basic "Constraints" will be (again, these are examples):
* Number of customers in each category is >= 0
* Number of customers in each category is <= total prospects in that category
* Net cash available for each period >=0
* Credit borrowing for each period >= 0 and <= available credit limit

 

[Eighty888]

Hi Iliace,

You're right. Solver is the last part so I am not going to worry about that for now, thank you!

Do you know anything about ROI or margin? I'm not really sure what they're supposed to mean to me here. Let's say I have a consumer, I pay $300 for the sales and marketing to acquire that customer. I see monthly revenues from that customer of $500. ROI in months is 7, meaning I don't see a return on my full investment for 7 months right? The reason why is because my margin 10%; 10% of $500 is $50. $50 * 7 = $350 > $300. In terms of margin, is it fair to say that the cost of producing the app is $450 then? So really, I have direct costs of $450 for the production of the app and $300 for the sales and marketing required to obtain that one customer? Am I understanding this correctly? It works for the consumer but not for the Large Business.

I understand this is outside of the realms of Excel help so please don't feel like you have to help me with this, but any advice is always appreciated!

 

[iliace]

We can take it there.

ROI is expressed as a percentage. You might think of the 7 months as your payback period, or break-even period. If you want to establish true ROI, you would need to estimate the lifetime value of the customer. However, you may want to take a discounted cash flow approach, or even some kind of opportunity cost calculation - i.e., had you spent that S&M money elsewhere, could you have acquired a bigger customer, better margin, or realize economies of scale. Sometimes it is simpler to estimate that as Required Rate of Return percentage instead, and discount it that way.

Now, because your IRR for all customer types is about the same, there is not much difference based on ROI alone (for 12 months, it's 100%). The variables that come into play will be, basically rehashing what I listed above:
* Delay in payment (1 month for small corp, 2 months for large corp)
* Cash flow - how much money do you need to collect each month to keep doors open? Also cost of financing.
* Prospects - how many of each customer type can you realistically expect to bring in each month?
* Economies of scale - does mass production for a larger customer allow you to reduce some expenses?

Hope that makes sense.