Many Variables Linear Equation

[Nathalie Sicard]

Hello everyone.

I am trying to solve this and I need it for work, for tomorrow. I've been thinking of it the whole day.

It's about a sales projection for a year, regarding about 50 company items.
I am supposed to split out 3 objectives in each item.

Objectives:
- GIVEN VOLUME SALES (oz)
- GIVEN VALUE SALES ($)
- GIVEN INVESTMENT ($)

I have a portfolio of about 50 items as I said. Each item has different price and different percentage of investment.
e.g.

Chocolate Cereal, price $4.3, discount 5%

So I need to find out the correct mix of items to be sold, in volume, to reach the given volume sales, value sales and investment. None of these can change, we have this as a fixed goal.

So the equations I can picture in my head are these:

GIVEN VOLUME SALES (oz) = a+b+c+...+n
GIVEN VALUE SALES ($) = 5.4*a+3.2*b+2.2*c+...+3.4n
Assuming 5.4, 3.2, 2.2, 3.4 etc are the prices, this is data I already have (pricelist)
GIVEN INVESTMENT ($) = 0.05*a+0.01*b+0.01*c+...+0.02*n
Assumming 0.05, 0.0.1 and 0.02 are percentages of discount (i.e. 5%, 1% and 2%)

The problem here is I have so many unknown variables, as I said more than 50 roughly.

I also have some other concern. I need to maintain certain item mix, for example, to assign more sales to those top seller items instead of small items.

I've been reading this is possibly done with a thing named SOLVER in Excel. But I don't think I have it installed and would definitely like better to have the calculations on a spreadsheet.

Any ideas?

Thank you very much.

 

[nightcrawler23]

USE THIS LINK TO INSTALL SOLVER.

http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010021570.aspx

 

[Nathalie Sicard]

Any other solution not involving Solver? My computer is a company computer and I am not allowed to install anything.

Thanks.