JrExcelTor
New Member
- Joined
- May 30, 2015
- Messages
- 15
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]
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!
