Help with formula for sales forecasting

JrExcelTor

New Member
Hi All,

I am a rather novice user of excel and would appreciate any support from the community.

To give some context to the project, the attached workbook will be used for two purposes: First, to capture data of sales forecasts and second, to use previous year’s forecasts to apply to future years forecasts. I need assistance with the latter. There are four types of forecasts (highlighted in yellow). 2015 are actuals to be analyzed. The forecasts are manually inputted by customer and by item for each month of the four forecasts (i.e. A&C Changes in January for customer ‘A”).

You will notice that for customer type, it can be specific, i.e. customer “A” or broad such as “East” (which includes customers A,B, and C) as well as “All customers.” Looking to future years forecasts, employees can be specific when inputting a value (i.e. apply a value of 5 to customer “A” or broad inputting a value of 10 for “All customers.” I would like a formula that that looks at the previous years actuals (i.e. 2015 Actuals) and looks at the percentage make up of each customer to the months total. For example, a value for 10 was applied to all customers which represented approximately 18% of January’s total. I want a formula that will look to 2015’s actuals, determine what percentage each customer represented for the months total, and apply those percentages for the future years forecast if the employee did not specifically identify what amount to apply to each customer. If you refer to 2016 Forecast 1, A value of 300 was inputted for all customers, a value of 400 for all customers excluding C, a value of 500 for customer A, and a value of 400 for West. I need a formula to recognize that the value of 500 for customer A can be inputted as is, but a formula to determine the allocation of the values pertaining to All customers, all customers excluding C, and West based on the prior years month.

I realize this is a rather long post so I apologize in advance and appreciate any feedback.

Thank you ALL!
[/IMG]

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Replies
0
Views
198
Replies
3
Views
218
Replies
3
Views
216
Replies
1
Views
337
Replies
0
Views
175

1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back