Solve Statistics Problem: Multiplication Factor for 90%+ R-Square

[mrk79blr]

In the sample excel file(extract from .zip file) I have explained the calculation of contribution and R-Square.

I need to solve 2 problems by adjusting the Multiplication factor or Coeff.

I need to make sure actual contribution of current year and Previous year will be within expected Current and previous year contribution as well as R-Sqaure value should be above 90%. This has to be achieved by adjusting the Multiplication factor. In the attached excel file see in "calc" sheet for more details

What is the best way to figure out the Multiplication factor and how to solve this problem?

 

[Aaron Bex]

The best way to figure out the multiplication factor is to use an optimization algorithm. This algorithm should be designed to minimize the difference between the actual contribution of the current and previous years and the expected contributions, while ensuring that the R-Square value is above 90%. The algorithm should use trial and error, adjusting the multiplication factor until it finds the optimal solution. Additionally, you may want to include constraints in the algorithm to ensure that the multiplication factor does not become too large or small. Once the optimal solution is found, the multiplication factor can be used to calculate the expected contribution and R-Square values.