Splitting a monthly figure into daily sales

[jananhk]

Dear all,
I am a newbie to the forum and have come across a situtation for which I urgently need some professional advice and help.

I need to create a balance sheet but I only have the total amount of each month for the year of 2018-2019. I am just wondering how to split this monthly figure into daily sales (not daily avergae sales - but an acutal figure) without altering the total sales of the month. Also, the table should show weekend sales higher than the normal weekdays.

If may use an analogy to better explain the situation. I have got a forest but need to shows trees for different regions (time: months).

The total sales figure for individual months are as follows
Jan 2800
Feb 1800
Mar 1700
Apr 2100
May 2400 and so on.

So the table should look like this:

Jan 2019 Daily sales
1 67.50
2 81.50
3 49.50
4 87.50
5 201. 00 (Sat)
6 85.00 (Sun)
7
8
9
10
11
12
13
14
15


This is what it shoudl look like. So whatever numbers are generated by the formula, the total should not exceed 2800.

Looking for your kind advice.


Regards

Janan

[FDibbins]

1. If you dont have the actual daily values, and you dont want an average, how would you suggest the monthly value get broken down?

2. upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[jananhk]

Hi FDibbins
Thank you for your reply. Please find the attached file for your reference.

Attachment 648986

[jananhk]

I don't mind daily average sale but it should not appear as uniform numbers. The data should display variant figures for each day but the total sum should should be absolute.

Thank you

[FDibbins]

You didnt really answer my question?

1. If you dont have the actual daily values, and you dont want an average, how would you suggest the monthly value get broken down?

[jananhk]

No, I totally understand the question. I dont have the actual daily values but I just have the monthly total figure. Im just checking the possibility if we could divide the total amount on 30 days so each day we have a different value for Daily Sale.

Thanks

[Pepe Le Mokko]

Please find the attached file for your reference.

Attachment 648986

Although it is clearly explained in the yellow banner how and why to attach a file, nothing is attached...

[jananhk]

Finally figured it out how to attached a file.

[Glenn Kennedy]

It's not that simple...

You need to apply more constraints. With a simple random number generator, you could get days with no sales at all, or the entire monthly sale in 1 or two days. So, you have to impose a minimum level per day... and a maxium level per day.

The formulae here are VOLATILE. They will recalculate every time anything changes on the sheet.

The parameters are all entered in pennies, to allow for pennies in the results columns, and to avoid arithmetical errors associated with floating point arithmetic:

In B8:
=RANDBETWEEN(MAX(B41,$B$44-(($B$43-ROWS($B8:B8))*$B$42)),MIN($B$42,$B$44-$B$43))/100

In B9, copied down:
=IFERROR(IF(ROW()=$B$43+ROW($B$8)-1,$B$44-100*SUM(B$8:B8),IF(ROW()>$B$43+ROW($B$8)-1,"",RANDBETWEEN(MAX($B$41,$B$44-(100*SUM(B$8:B8)+($B$43-ROWS(B$8:B9))*$B$42)),MIN($B$42,$B$44-(100*SUM(B$8:B8)+($B$43-ROWS(B$8:B9))*$B$41)))))/100,"")

[jananhk]

It's not that simple...

You need to apply more constraints. With a simple random number generator, you could get days with no sales at all, or the entire monthly sale in 1 or two days. So, you have to impose a minimum level per day... and a maxium level per day.

The formulae here are VOLATILE. They will recalculate every time anything changes on the sheet.

The parameters are all entered in pennies, to allow for pennies in the results columns, and to avoid arithmetical errors associated with floating point arithmetic:

In B8:
=RANDBETWEEN(MAX(B41,$B$44-(($B$43-ROWS($B8:B8))*$B$42)),MIN($B$42,$B$44-$B$43))/100

In B9, copied down:
=IFERROR(IF(ROW()=$B$43+ROW($B$8)-1,$B$44-100*SUM(B$8:B8),IF(ROW()>$B$43+ROW($B$8)-1,"",RANDBETWEEN(MAX($B$41,$B$44-(100*SUM(B$8:B8)+($B$43-ROWS(B$8:B9))*$B$42)),MIN($B$42,$B$44-(100*SUM(B$8:B8)+($B$43-ROWS(B$8:B9))*$B$41)))))/100,"")

Glenn thank you for effort and help.

However, i'm a newbie to the Excel and not familiar with formulas application. When I entered the formula in B8, it shows digit '0' in the cell B8 and nothing hapened when I entered the formula in the cell B9.

Kindly help by giving a bit information about the steps I should take.

Sorry for being ignorant!

[Glenn Kennedy]

Did you open the sheet?

You will see that the formual also refers to several cells above the formula and below the month end. These all need to have entries, too.

Without seeing what you have/have not done... it's impossible to tell.

Was it doing what you wanted on MY sheet?

[jananhk]

Yes, I did open the sheet. When I changed the entry in the minimum cell given at the bottom, fluctuations in the data takes place- change the minimum daily sales to 60/day. However, When I tried to change the entry in the maximum cell, I get error. I tried to change the maximum daily sales to 250- didn't work.

Please tell me what should I change so I get the total of 2800.

Thank you for your help and sorry for being nuisance.

[JeteMc]

I may not completely understand, however the following modifications to Glenn's formulas provide values when the min (B41) is 60 and the max (B42) is 250:
B8:

Please Login or Register  to view this content.

B9 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[jananhk]

THANK YOU Glenn and JeteMC for your effort and sparing your precious time in helping me out. I figured out how to use Glenn's workbook and it worked like a charm!. Kudos to Glenn once again.