You are the CFO for Mark's Company. Intuitively you know that there is a relationship between sales and collections in your company. You want to design a model that will allow the company to predict collections.
Historical credit sales data is shown for the current month, March, and the previous months. As the CFO you will employ linear regression analysis to arrive at visual presentation of the AR collection relationships and equations to predict collections for current, one month prior, and two months prior, based on the specified sales levels.
1) Chart the collections for current, one month prior, and two months prior, based on the related level of sales.
2) Comment on the relationship of collections to credit sales based on the charts that you created. For example, what value does this information have to the company? What does y and R2 tell us?
3) Use the trend function to predict sales for
4) For your Aug-Dec forecast predict the collections for each month.
5) Document your model so that you can give it to your AR Manager to use for planning.
Note: The above steps are guidelines, you may expand and enhance this project in any way you deem appropriate to accomplish your mission as the CFO and communicate to potential users. The project will be graded on the accuracy of calculations, knowledge and understanding of the modeling topic, demonstration of Excel skills, and presentation, in relation to these specified criteria, and with consideration of the end-product to those of peers.
I have created the charts, trend lines and regression equations for you and explained how you can create them in the future. The linear regression assumes that there is a linear relationship between the dependent variable and the independent ...
This solution employs linear regression analysis to arrive at visual presentation of the AR collection relationships and equations to predict collections for current, one month prior, and two months prior, based on the specified sales levels.