60.1K

Verified Solution

Link Copied!

Background: The attached data set shows actual accounting data from January 2022 through July 2022 for an internet merchandiser in columns B through H. The sales department has given you their sales forecast by month in columns J through N. We will review the cost categories and relationships in class. The group is to complete the income statement expenses from cost of goods sold through net income by month for August through December. Each expense line may exhibit fixed, variable or mixed cost behaviors. Use data analytics techniques to determine cost behavior and a forecast for each cost line.

Finish excel sheet using regression to forecast August, September, October, November, December.

What are you Answering: 1.) The group will complete the income statement expenses from cost of goods sold through net income by month for August through December. Each expense line may exhibit fixed, variable or mixed cost behaviors. Use data analytics techniques to determine cost behavior and a forecast for each cost line. Describe the methodology you used to forecast each line; 2.) Back test your model against January (Low sales), February (Medium Sales) and May (High Sales) showing variances for each line in the model. Does the back testing help validate your model; 3.) What is the breakeven point for the merchandising firm and how did you find your answer. Is this breakeven point a single number or a range; 4.) Are there any lines/costs that vary significantly that you cant predict their cost behavior? What additional information would you need to predict their cost behavior; 5.) What lines, if any, in the data set might be irrelevant or could be combined or eliminated to make the analysis easier or clearer; 6.) Does the sales mix (which website sales occur) have an impact on the variance comparing actual vs. forecast. How would you adjust for this?

Answer & Explanation Solved by verified expert

See Answer