Calculate usage and sales using new estimate

[Caly]

Hi I have an assortment of items where sales totals $3 million based on customer usage they gave for 12 months

Customer will start buying in April though so the usage they have asked to calculate based that based on $2 million since buying will start in April

Is it fair to divide the quantity usage they gave based on 12 months to instead 1.5. Am I doing this properly?

For one line they have 15 units but is it fair to do a roundup dividing that by 1.5?


=roundup(15 / 1.5)

I tried doing by 9 since that is April through December but the number is too low

 

[etaf]

assuming no seasonality and its just a linear usage

then i would take the yearly total / 12
and apply that as a monthly amount

3/4 of the usage
not sure whay you are divinding by 1.5

1200 used in a calendar year - Jan-Dec then that would = 1200/12 = 100 per month
so for 9 months - April to December they will use 100*9 = 900 - assuming a linear usage , no seasonality or other impact on usage

but if you divide by 1.5 = 1200/1.5 = 800

I tried doing by 9 since that is April through December but the number is too low

perhaps you could show a sample , why is it too low ? , must be seasonal

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Caly]

Thank you. Using the 3/4 at .75 worked. It is a little over but that is using 3/4 of the months like you said which is what was needed. Thank you so much. This was incredibly helpful and worked perfectly

 

[etaf]

you are welcome