• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Customer optimization

E

eighty888

New Member
Hello,

I am trying to find the optimal customer mix to maximize revenue on a monthly and yearly basis. My amount of initial funding to use is $500,000.

There are 3 different types of customers with different constraints and revenue that they bring in.

Here is a link to a screenshot I took of the worksheet:
http://imgur.com/52CwgWa

I've tried using 'Solver' and 'Scenario Manager' however I get confused in how I'm supposed to apply Solver to correctly solve this problem. I watched Youtube videos on how to use it but they all show simplified problems.

Any help or advice is greatly appreciated! Thank you!
 
Hi ,

I am sorry I cannot help , but I can offer some advice , which you are free to accept or reject.

1. The problem itself is extremely complicated , with more than a dozen independent variables.

You need to list out all of the conditions , and decide whether all of them need to be considered together , or can we start with a bare minimum set of conditions , to which we can add the remaining conditions in an incremental manner.

2. Instead of uploading a screenshot , see if it is possible to upload the workbook , with all of the data in it.


Narayan
 
Awesome Wally said:
Hi,
I'm not entirely sure what you are after (what is the finished product) but I'm intrigued and want to take a stab at it. I have attached the Excel file of your Screenshot.

Any other clarifications you can provide?
Click to expand...

Hi Wally,

I am supposed to find an optimal customer mix. The customer acquisition grid towards the right side of the sheet is supposed to be programmable in such a way that the manager can input a number of customers in there and have an output maximizing revenue. For example, there are three customers: consumer, small business, large business. They all have different costs and bring in different amounts of revenue. If the manage changes the customer mix let's say by entering 10 consumers, 5 small business, and 1 large business for January, and then 5 consumers, 8 small business, and 3 large business for February, the manager wants those inputs to change the amount of revenue coming into the business.

Upon taking this project I also realized I don't have a clear understanding of the margins or the "ROI in months". I'm assuming the margin % is tell me that my profit on that particular customer is 10% of my revenue? So I only making 10% of my costs on consumer, and then 50% for the small business?

Is there a way to set up the problem as a matrix and solve for an optimal solution in Excel?

Thank you for your time!
 
You must log in or register to reply here.
Back
Top