2

I'm trying to produce an optimiser for a client, and they have several possible outcomes.

Let's call the outcomes "Motorbikes" and "Cars".

The client is interested in how their advertising mix affects sales. But it is clear from the data that the more they advertise "Cars" the fewer "Motorbikes" they sell.

So for example if the equations are like this:-

Cars sold = 100 (base sales) + 4 x Brand Advertising + 5 x Car Advertising - 1 x Motorbike Advertising

Motorbikes sold = 200 (base sales) + 3 x Brand Advertising + 6 x Motorbike Advertising - 3 x Car Advertising

So for solver I would need to turn these into "cars sold per unit advertising" and the same for motorbikes.

Can I use solver to optimise the advertising mix for this client?

Or do the negative values make it impossible?

The negative values appear correct and are the result of customers switching from one product to another.

Simon Hayward
  • 121
  • 5

1 Answers1

0

In fact it can.

Amazing what taking a shower will do for one's ability to think about a problem!

All we need to do is to stop think about how much advertising will generate 1 unit of car or motorbike sales, and instead, think how much value does 1 unit of advertising generate.

Let's say that a car sale is worth $1000 profit and a motorbike sale is worth $500 profit.

Then - a unit of brand advertising will generate 3 motorbike sales and 4 car sales so 1 unit of brand advertising (which might be in 1000 dollar units or more) will generate $4000 dollars of car profit and $1500 of motorbike profit.

The car advertising will generate $5000 of car profit and -$500 of motorbike profit, for a total of $4500.

The motorbike advertising will generate $3000 in motorbike profits, but will lose us $3000 of car profits! Bad motorbike adverts!

Viewed in this way the problem becomes trivial.

Obviously the numbers here aren't terribly realistic, but it shows how when viewed in a certain way the problem becomes much easier.

In my actual data there are many outcomes that have more complex positive and negative relationships with each other, as well as some non linear parts.

But the approach outlined here should make it completely solvable with Excel solver.

Simon Hayward
  • 121
  • 5