Sales Goal Forecast - Distribute Totals across pre-defined cells

[dkorbat]

Hello good folks,
Hope this doesn't break any rules! If in which this do, I apologize sincerely..

I have an idea of an excel formula which calculates my sister's monthly sales goal. She basically does this by hand and I was wondering if I can help her by automating it in excel.

The basic breakdown is as follows:
1. She has a certain goal to be reached per month, doesn't have to be exactly the amount, but can't be lower than the aforementioned goal. (margin around 2%-5%)
2. Each weekend she wishes to sell more than the usual workdays i.e., in mondays or thursdays she can sell around 10k each day while in weekends (saturdays and sundays) she can sell around 40-50k.
3. She distributes these amounts evenly into each day of the month.

My current breakdown is as follows:
1. Get her to enter a certain goal
2. Multiply this goal by around 20%-25% to get goal per weekend (sats, suns)
3. Get monthly weekend count (I used =NETWORKDAYS)
4. Enter weekend goals into weekends
5. Calculate difference between current sales and goal
6. Find out remaining workdays
7. Distribute the remaining goals into those workdays (max sales per day is 20% from remaining goal, min is 10% from remaining goal)

note: the amount of daily sales doesn't have to be exactly that amount (if that makes sense, e.g. random numbers between min/max max_sales_per_day)

The Question
I have made a worksheet up to step 6 and am completely stuck on how to proceed.. I don't know how to distribute the remaining goal randomly into those workdays.

And the plot thickens..
I also wanted to enable her to enter her daily sales amount perhaps in another column beside the daily sales goal, and if for example she didn't reach her daily goal, the amount difference should be added to her remaining workdays, thus changing the whole distribution of goals.. but of course, this is my secondary objective. The primary remains as above.

Is this even possible? or my logic isn't in the right direction?
I suck very much at VB and macros, so I avoid using it if it's possible..
I know this is probably not the most elegant way of tackling her problem, but.. I'm currently experimenting :p

Or... you can just ignore me completely because I am crazy

ps: I've cross-posted this here with no replies as of today.

In any case, here's what I've come up with so far, and thank you very much for your time.

SalesGoal.xlsx

[David A Coop]

Nee how dkorbat,

This seems very complex to me! Since you have a MONTHLY target, does it really matter that the bulk of sales occur at the weekend? Why is it necessary to distribute across weekdays?

Also confused by statement that sales at the weekend are about 40-50K, but you are showing 80K as the target for both Sat and Sun.

Are you simply aiming to produce a budget here? If so, does it matter if the workdays have an even distribution of the remaining balance after weekend days?

Perhaps you could fill out one of the columns with the desired result.

By the way, you have gone to a lot of trouble to enter the formulas into the Sat and Sun rows - there is a better way! I'll show you when I hear next.

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[dkorbat]

Ni hao David!

Sorry for the confusion, I say she can sell around 40-50K at weekends because that's when the customers really pours in! In workdays she usually has only 2-3 customers. (She's selling children painting lessons in forms of voucher). And in peak season such as summer holidays, she can actually reach 80k in those two days, hence the 40-50K at the beginning.

I guess it really doesn't matter if the workdays don't have a random distribution, if that's the case I can perhaps just enter the formula to divide the remaining goal with the remaining workdays.

I know I don't have the most elegant formula to tackle this particular task, I just put in whatever I think works because I'm not that good at excel haha! But really, I used to think excel is boring; now I am very interested in it! Some of the formulas I've entered here are actually ripped of someone else's and hacked, because I'm not planning on selling this, so I can safely assume that it's okay to dig in and learn by doing..

Thank you, David, for your time

[David A Coop]

dkorbat - SalesGoal.xlsx

Ni hao dkorbat,

Thank you for correcting my spelling of ni hao. My daughter-in-law is Taiwanese (from Kaohsiung). My son lived there for 8 years, and I have been there a few times (also to Taipei twice)! They are now here in Australia, but I couldn't check the spelling with them, but I could remember the greeting!

I have made a suggestion in the first month then copied it across. It appears to work! I changed the shading for the undated days to a Conditional Format - if the date is nul ("") then shade the cell.

I changed the format of the sales column to be consistent - you had some cells formatted to show NT$ - I didn't think this was necessary!

The formula in the sales column now places the forecast weekend sales in the Saturday and Sunday rows, then averages the non weekend sales over the remaining days. (The weekend days are based on a simple test - does the day start with "S"?)

I hope this helps, and that I have understood your problem correctly!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

When you reply please make it clear WHO you are responding to by mentioning their name.