Help me solve this mathematical problem I am having

Factual Questions
1

I am having a serious brain cramp here. I need help to solve what should be a simple algebra problem. This is not a homework problem - I promise.

I’m trying to generate a detailed forecast of letter volumes by letter type. There are 2 general letter types - I’ll call them X and Y. Within each type, there are between 6 to 12 different letters. There are also 4 letters that we can not associate specifically to either X or Y, so we arbitrarily split them 50/50 to total all the X and Y letters every month.

I have a forecasted total number for X and Y each month for next year. What I want to do is use the monthly ratios of the individual letters to the total for the letter type from last year and apply them to the X and Y forecast to generate a forecast for each letter. The problem I am having is the “mixed” letter type that gets split 50/50 - it is screwing me up.

When I apply the ratios as I have calculated them to the volumes for X and Y, the totals do not match what X and Y should be…

What formula should I use to calculate the detailed forecast for each X, Y, and “mixed” letter given total X and Y and having the detail X, Y, and “mixed” from the previous year?

2

I’m confused. :confused: This sentence trips me up:

3

I have a tracking spreadsheet that records the monthly volumes from 2003 for each letter. There are 23 different letters, and each one is either an X type, a Y type, or a “mixed” that gets allocated 50/50 when totaling the Xs and Ys.

Now for 2004, I have the forecasted total Xs and Ys for each month. What I want to do is allocate them to the 23 different letters in the same ratio as the actuals happened in 2003. This would not be a problem for me were it not for the “mixed” letters.

So for example: in January 2004, I have a forecast of 10,000 X letters. I want to allocate those 10,000 to the 10 different letters that are of X type. In January 2003, there were 8,000 X letters. So I would take “Letter A in 2003”/8,000 * 10,000 to get the forecasted Letter A volume for 2004. Repeat for all letters in type X, and do the same for type Y.

The problem I have is that there are 4 letters that are “mixed”. When they are totalled at the end of the month 50% of them are considered X and 50% are considered Y. So now, part of my 10,000 X letters from above needs to be given to these 4 “mixed” letters. But I can’t figure out how. Every time I try, while the total X + Y letters are right, my calculated forecasted Xs do not match the original forecasted X.

4

What type are your mixed letters? In the example above, if the 10,000 X letters include the mixed ones, do they go in A, B, C…or J? Would it be possible to have a new letter category within X for the mixed letters? (Within Y also.) Then you could use the actuals for the mixed in 2003 to make a prediction for the mixed in 2004.

If that is not clear enough, I can produce an example.

5

If you do not know what proportion of X and Y were ‘mixed’ letters, then I think that there is no formula that will work for you.

For example, if you have 10 X-letters, 100 Y-letters, and 10 ‘mixed’ letters, when the mixed letters are split equally between X and Y, the ratio of X to Y will be 15:105 (i.e., 3:35). However, if you are given this ratio with no more information, you have no way of knowing what proportion of the nominally X and Y letters were mixed. For example, a ratio of 3:35 could arise from:

10 X, 100 Y, 10 mixed
5 X, 95 Y, 20 mixed
1 X, 91 Y, 28 mixed

In these three cases, the overall ratio after including the mixed letters (split equally between the X and Y categories) is 3:35. However, the ratio of X:Y was 1:10, 1:19 and 1:91 respectively.

The problem - assuming I’ve understood the scenario - is that you’ve irrepairably lost data when you add the mixed letters into the X and Y letter categories. Or, to put it another way, equally splitting the mixed letters between X and Y categories does not necessarily maintain the original ratio between X and Y categories.

6

What’s your current (non-working) approach? It might help us understand your difficulty.

If I may hazard I guess, I think you might be attempting to constrain the mixed type values to be both a 50/50 split between X and Y and the same proportion of all X or Y that they were in the last year.

I’ll do an example to clarify whether I’m reading the problem correctly.

Assume there are only three secondary types :

a is X-only, b is Y-only, and c is mixed X-Y.

Say that last year X was 13000 and Y was 19000, and there were 4000 of mixed type (already added in to those numbers).

Then X was 84.6% a and 15.4% c; Y was 89.5% b and 10.5% c.

Now say the predicted X (Call it X’) is 14000 and predicted Y (Y’) is 16000.

If we use the same ratios for X and Y individually as we had last year, we end up with :

X’ is 11846 a and 2154 c.
Y’ is 14316 b and 1684 c.

Obviously not a 50-50 split. But if you went with that method you shouldn’t expect that to happen; the even split is not part of that prediction.

If X and Y don’t change much relative to each other (i.e. they’re both predicted to change by about the same amount), then I’d just let the split be 51-49 or so. On the other hand, you could force a 50-50 split, using the total amount, and adjust the amounts of a and b accordingly.

Using the above numbers:

In total, there were 11000 a, 17000 b, and 4000 c last year (34.4% a - 53.1% b - 12.5% c).

Applying the same total ratio of c type to the predicted total ( 12.5% of 30000 ) gives us an expected total of 3750 c. Splitting evenly, we get 1875 c for each.

This gives new ratios as follows :

X’ is 86.6% a, 13.4% c.
Y’ is 88.3% b, 11.7% c.

I don’t really like this method, though - it essentially distributes the constraint on c type into a change in the ratios for a and b type.
I don’t know if I’m just showing you stuff you already know, but any prediction you make you’ll have to subject to certain constraints. I suppose it depends on which aspects of the prediction are most valid.

7

Thank you all for your solutions. My problem was that I was trying to fit the mixed letters in too neatly.

Essentially, I have created a third category for the mixed letters - Type Z.

Now, I take the 2003 Type Z total and subtract half of them from 2003 X and 2003 Y, and also calculate them as a % of the total 2003 letter volumes. I now have the base numbers to calculate the ratios.

In 2004, I use the 2003 % for Type Z to calculate that number and subtract 50% from the Type X and Type Y forecasted volumes. Now I have all I need to do the forecasts.

Why did I not try and do this sooner? Why was I making it more difficult than I needed it to be? These are questions that might never get answered…