Answered step by step
Verified Expert Solution
Question
1 Approved Answer
This question is related to regression analysis in accounting. I've attached two extra files that are the same problem I just don't know exactly what
This question is related to regression analysis in accounting. I've attached two extra files that are the same problem I just don't know exactly what to do but it should make it pretty easy if you do. The one I want you to fill out is Extra Question 1, the highlighted cells are hats graded and have comments for what the should contain. Then the regression analysis should be placed in the cells in starting in row 85 that are highlighted.
Plantcity is a large nursery and retail store specializing in house and garden plants and supplies. Jean Raouth, the 2013. She assumes that in some way supplies expense is related to sales, either in units or in dollars. She has coll December 2012, and has estimated sales for 2013. Date March-17 February-17 January-17 December-16 November-16 October-16 September-16 August-16 July-16 June-16 May-16 April-16 March-16 February-16 January-16 December-15 November-15 October-15 September-15 August-15 July-15 June-15 May-15 April-15 March-15 February-15 January-15 December-14 November-14 October-14 September-14 April-17 May-17 June-17 July-17 August-17 September-17 October-17 November-17 December-17 January-18 Supplies Expense $1,833 $2,356 $1,917 $1,273 $1,566 $2,779 $2,845 $1,736 $1,679 $1,982 $1,991 $2,599 $2,122 $1,615 $1,910 $1,831 $1,376 $2,923 $3,236 $1,343 $1,160 $2,972 $1,909 $1,733 $1,885 $1,603 $2,224 $2,241 $1,803 $2,074 $2,373 Sales Units 300 276 398 119 165 312 722 161 195 133 172 405 248 207 315 158 163 250 645 71 109 228 214 138 198 193 249 127 168 150 752 132 180 122 293 254 314 345 474 192 210 Sales Dollars $1,289 $1,608 $2,194 $1,432 $1,215 $1,659 $1,965 $1,193 $1,419 $1,409 $1,669 $2,285 $1,959 $1,463 $1,491 $1,309 $1,201 $2,140 $1,978 $1,391 $1,620 $1,856 $1,277 $1,595 $1,401 $1,288 $1,732 $1,450 $1,330 $1,536 $2,405 $1,043 $1,799 $1,245 $1,395 $1,766 $2,002 $1,660 $1,969 $1,741 $1,518 February-18 March-18 316 274 $2,056 $2,704 1. Develop the regression that Jean should use based on the above data and using the regression procedure REG You may locate your ATP Regression output starting in row 85. Model: Supplies Expense = F(Sales Units and Sales Dollars) R Square Coefficients Standard Error t Stat P-value Intercept Sales Units Sales Dollars Intercept Sales Units 2. What are the predicted monthly figures for supplies expense for 2013? Model to use: Date April-17 May-17 June-17 July-17 August-17 September-17 October-17 November-17 December-17 January-18 February-18 March-18 Sales Units 132 180 122 293 254 314 345 474 192 210 316 274 Sales Dollars $1,043 $1,799 $1,245 $1,395 $1,766 $2,002 $1,660 $1,969 $1,741 $1,518 $2,056 $2,704 Predicted Supplies Expense d supplies. Jean Raouth, the assistant manager, is in the process of budgeting monthly supplies expense for ts or in dollars. She has collected these data for sales and supplies expenses for June 2010 through e regression procedure REGRESSION in Excel Analysis Toolpak - enter the following Regression results. Supplies Expense = F(Sales Units) Supplies Expense = F(Sales Dollars) R Square R Square Coefficients Standard Error t Stat P-value Coefficients Intercept Sales Dollars Supplies Supplies Supplies Expense = Expense = F(Sales Units F(Sales = Expense F(Sales and Sales Units) Dollars) Dollars) Standard Error e = F(Sales Dollars) t Stat P-value Excel Instructions No additional Excel instructions No pivot tables, no tables/lists, no range names, no charts. sts, no range names, no charts. 2143 2288 3059 2113 1977 3340 2648 1938 2254 2273 2012 3120 2274 1990 3073 1938 2563 2761 4316 1816 1760 3776 2405 1830 2748 1738 2072 2597 2068 2116 2765 314 361 489 113 195 419 1062 166 241 187 212 434 402 269 411 159 207 403 837 81 134 258 354 152 274 211 347 113 201 195 877 1773 1942 2683 1439 1692 1878 1967 1515 1861 1607 1805 2281 2399 2101 1844 1632 1988 2292 2726 1616 1723 2123 2054 1450 1777 1464 1761 1835 1501 2016 2943 SUMMARY OUTPUT Regression Statistics Multiple R 0.574214 R Square 0.329721 Adjusted R 0.306608 Standard E 510.0497 Observatio 31 ANOVA df Regression Residual Total SS 1 3711203 29 7544371 30 11255573 Coefficients Standard Error Intercept 1944.977 160.823 X Variable 1.535693 0.406593 SUMMARY OUTPUT Regression Statistics Multiple R 0.648877 R Square 0.421042 Adjusted R 0.401078 Standard E 474.0328 Observatio 31 SUMMARY OUTPUT ANOVA df Regression Statistics Multiple R 0.67113 R Square 0.450416 Adjusted R 0.411159 Standard E 470.0262 Observatio 31 Regression Residual Total SS 1 4739067 29 6516507 30 11255573 Coefficients Standard Error Intercept 433.7922 445.9779 X Variable 1.044154 0.227366 ANOVA df Regression Residual Total SS MS F Significance F 2 5069685 2534843 11.47379 0.000229 28 6185888 220924.6 30 11255573 Coefficients Standard Error t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% Intercept 739.8626 508.0808 1.456191 0.156463 -300.8938 1780.619 -300.8938 1780.619 X Variable 0.637519 0.521137 1.223324 0.231403 -0.429981 1.705019 -0.429981 1.705019 X Variable 0.777549 0.313562 2.479733 0.019433 0.135247 1.419851 0.135247 1.419851 MS F Significance F 3711203 14.26559 0.00073 260150.7 t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% 12.0939 7.50E-013 1616.057 2273.897 1616.057 2273.897 3.776981 0.00073 0.704117 2.367268 0.704117 2.367268 MS F Significance F 4739067 21.08997 7.86E-005 224707.1 t Stat P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% 0.972676 0.338758 -478.335 1345.919 -478.335 1345.919 4.592382 7.86E-005 0.579137 1.50917 0.579137 1.50917 Plantcity is a large nursery and retail store specializing in house and garden plants and supplies. Jean Raouth, the expenses for June 2010 through December 2012, and has estimated sales for 2013. Date June-15 May-15 April-15 March-15 February-15 January-15 December-14 November-14 October-14 September-14 August-14 July-14 June-14 May-14 April-14 March-14 February-14 January-14 December-13 November-13 October-13 September-13 August-13 July-13 June-13 May-13 April-13 March-13 February-13 January-13 December-12 July-15 August-15 September-15 October-15 November-15 December-15 January-16 February-16 March-16 April-16 May-16 Supplies Expense $2,143 $2,288 $3,059 $2,113 $1,977 $3,340 $2,648 $1,938 $2,254 $2,273 $2,012 $3,120 $2,274 $1,990 $3,073 $1,938 $2,563 $2,761 $4,316 $1,816 $1,760 $3,776 $2,405 $1,830 $2,748 $1,738 $2,072 $2,597 $2,068 $2,116 $2,765 June-16 1. Develop the regression that Jean should use based on the above data and using the regression procedure REG Model: R Square Supplies Expense = F(Sales Units and Sales Dollars) 0.4504155276 Coefficients Intercept Sales Units Sales Dollars Standard Error t Stat 739.862600165 508.080815579 0.6375189868 0.5211367574 0.7775489721 0.3135615224 1.456190782 1.2233237777 2.4797333749 2. What are the predicted monthly figures for supplies expense for 2013? Model to use: Supplies = F (Sales Units; Sales Dollars) Date July-15 August-15 September-15 October-15 November-15 December-15 January-16 February-16 March-16 April-16 May-16 June-16 Sales Units 154 212 168 382 291 276 339 506 256 240 352 242 Sales Dollars $1,297 $1,675 $1,623 $1,871 $2,182 $1,949 $2,350 $2,367 $1,909 $2,204 $2,683 $3,262 n plants and supplies. Jean Raouth, the assistant manager, is in the process of budgeting monthly supplies expense for 2013 for 2013. Sales Units 314 361 489 113 195 419 1,062 166 241 187 212 434 402 269 411 159 207 403 837 81 134 258 354 152 274 211 347 113 201 195 877 154 212 168 382 291 276 339 506 256 240 352 Sales Dollars $1,773 $1,942 $2,683 $1,439 $1,692 $1,878 $1,967 $1,515 $1,861 $1,607 $1,805 $2,281 $2,399 $2,101 $1,844 $1,632 $1,988 $2,292 $2,726 $1,616 $1,723 $2,123 $2,054 $1,450 $1,777 $1,464 $1,761 $1,835 $1,501 $2,016 $2,943 $1,297 $1,675 $1,623 $1,871 $2,182 $1,949 $2,350 $2,367 $1,909 $2,204 $2,683 242 $3,262 nd using the regression procedure REGRESSION in Excel Analysis Toolpak - enter the following Regression results. Supplies Expense = F(Sales Units) R Square P-value 0.3297213285 Coefficients 0.1564625194 Intercept 0.2314033995 Sales Units 0.0194327646 Predicted Supplies Expense 1846.521540932 2177.411153615 2108.927771649 2438.188979894 2621.992482421 2431.260787121 2783.221621097 2902.90562441 2387.408448503 2606.585091483 3050.433175632 3430.506941933 Standard Error 1944.977125315 160.8229545024 1.5356925099 0.406592578 t Stat 12.0939024615 3.7769811676 Supplies Expense Supplies Expense = F(Sales Units and Sales Dollars) = F(Sales Units) 1846.521540932 2177.411153615 2108.927771649 2438.188979894 2621.992482421 2431.260787121 2783.221621097 2902.90562441 2387.408448503 2606.585091483 3050.433175632 3430.506941933 2181.473771847 2270.543937424 2202.973466986 2531.611664114 2391.863645709 2368.82825806 2465.576886187 2722.037535347 2338.114407861 2313.543327702 2485.540888816 2316.614712722 onthly supplies expense for 2013. She assumes that in some way supplies expense is related to sales, either in units or in dol ng Regression results. Supplies Expense = F(Sales Dollars) R Square P-value 7.4958268E-013 Intercept 0.000730434 Sales Dollars Supplies Expense = F(Sales Dollars) 1788.059454519 2182.749527511 2128.453538633 2387.403639432 2712.135419062 2468.847622748 2887.553229281 2905.303841029 2427.081477458 2735.106798972 3235.256388821 3839.821341894 0.4210417869 Coefficients Standard Error t Stat P-value 433.7921934886 445.9778754276 0.972676 0.338758 1.0441536323 0.2273664674 4.592382 7.9E-005 ither in units or in dollars. She has collected these data for sales and supplies Excel Instructions No additional Excel instructions No pivot tables, no tables/lists, no range names, no charts, no Solver models, no Analysis Toolpak procedures s, no Analysis Toolpak proceduresStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started