Consumption of Coffee and Sodas over a Period of 48 Days - Essay Example

Part 1. The diagram above shows the scatterplots for the consumption of coffee and sodas over a period of 48 days. From the diagram we wtiness thatthe consumption of coffee was steady for the first twenty days before it picked in between the 21st to the 24th days. In the case of sodas, the consumption is erratic with changing consupmtion especially in the first 30 days. From the 30th day, the consumption goes to higher levels with the same erratic trend. 2. The figure above shows the results of the regression model conducted on the consumption of sodas over a 48 day period. From the regression model we get to know that 42% of the data fits the goodness of fit test. Therefore, we can conclude that time used in this dataset does not represent or affect the consumption of sodas. The figure below shows the results of the regression model conducted on the consumption of coffee over a 48 day period. From the regression model get to know that 52.8% of the variation in consumption of coffee is explained by time (Lawrence 321). This is obtained from the R square value. 3. From the analysis of the regression model, we notice that both trends are negative and therefore, there is little relation between time and the consumption of either coffee or sodas. The R squared values are low since there exists no relation between the variables and the change in time does not affect the consumption of either coffee or sodas. 4. From the analysis and investigation of the data on the consumption of coffee or sodas, we come to notice that the average daily temperature affect the consumption of coffee and sodas. When the average daily temperature increases then the consumption of sodas increase while that of coffee decreases and the same occurs vice-versa (Lawrence 85). 5. The correlation analysis conducted on the data in the three columns results are shown below: Column 1 reps. Sodas, Column 2 = Coffees, Column 3 = Max. Daily Temp. From the correlation analysis conducted above, we get to know that the variable sodas and coffees have little correlation since the value is -0.38. Thus no correlation exists while the correlation between sodas and the variable Max. Daily Temperature is positive standing at 0.52. On the other hand there is a negative correlation between coffees and Max. Daily temperature. 6. When the two variables sodas and coffees are combined we come up with a variable known as total soda and coffee. The p value for this variable is 0.82 and this is a positive trend for the sale of the drinks. From a business perspective, we can analyse and conclude that there is an increase in sales when the two variables are combined (Berk 106). 7. The trend that is obtained from the analysis done above shows that the regression analysis is positive. This is obtained from the r squared values shown in the diagram below. The measure of using only time in measuring sales is a good indicator since it is the main determinant in this scenario. Moreover, I would recommend on using this model for café managers. Part 2 8. The model that we get when a multiple regression analysis is conducted is a good model. However, from the figures obtained we can conclude that these figures do not assist us in predicting sales. Moreover, the model is not perfect since our R squared value show that 30% of the sales in different days do not depend on the overall sales. 9. From the regression analysis conducted based on the model above, we can conclude that the p value changes with time. The regression co-efficient for time is 1.31163970250962E-18 and this show that time does not have a significant relationship on time. Because the P value for these variables is low (Berk 98). 10. a) Each day of the week has different sales figures but all the week days have higher sales compared to Friday. The sales figures for each week day are higher by around 20% compared to Friday. b) Based on the sales figures for each day of the week, Tuesdays seem to record the highest sales figures compared to other days. While, Fridays have the lowest sales figures compared to other week days. 11. The equation for this model will be based on the multiple regression that shows the sales figures is: Y = bo + b1*X + e; where Y is the sales variable, X is the independent variable (sales for Monday, Tuesdays through to Friday), bo is the intercept and b1 is the coefficient of change on X. 12. Using the above model and equations we have mapped the equation for t =49 all the way to t=53. This is shown in the Excel Workbook. 13. The regression model for the diagram that includes sales and other figures is shown in the picture below. The variation is sales figures can be easily computed when all these factors are placed together to come up with a clear picture. 14. From the analysis conducted and based on the two regression models, it would be advisable to say that the model that includes Max. Daily Temperature is recommended for café managers. From the analysis, café managers are advised should include all factors that affect sales in the determination of the sales figures. Part 3 15. The time forecasts for the model are shown below: 16. The sales usually increase over time and they are increasing by around 10 to 20%. 17. Using the data and model above we get to know that Tuesdays is the day with the highest sales and Fridays is the day with lowest sales figures. 18. The trend that was used in measuring the sales figures took into consideration the sales for the first twenty 24 weeks using the TREND function in Excel. This is shown by in Sheet Part3 (18) cell C51. The trend is 27.9 19. The MAD was calculated through the use of the SLOPE function in Excel and from the analysis we came up with this figure. 20. The calculation of the figures for Seasonally Adjusted forecasts for sales gave accurate answers based of the SLOPE and MAD calculations. This is shown in the Excel worksheet. 21. The Calculation of MAD for the multiple regression models for question 11 are shown in the Excel Worksheet 6. The MAD figure for this calculation is 1.87. 22. Making use of multiple regression forecasts compared to MAD for seasonally adjusted forecasts give different answers. As a result, we can conclude that MAD is more accurate and gives better results for café managers compared to Multiple Regression forecasts. References Berk, Kenneth and Carey, Partrick. Data Analysis With Microsoft Excel: Updated for Office 2007. Chicago, IL: Cengage Learning, 2009. Print. Lawrence, Kenneth, Klimberg, Ronald and Lawrence, Sheila. Fundamentals of Forecasting Using Excel. Boston, MA: John Wiley and Sons, 2009. Print. Read More