Using Data Analytics to Analyze CVP Relationships
Manufacturing companies often use past data to predict future costs and profits. The reliability of past data comes into play, however, given the company’s mix of variable and fixed costs as well as the consistency of its selling price. If cost behavior is consistent, forecasts may be more reliable.
Dashco produces and sells children’s rocking chairs, distributing them to major retailers. There are minor fluctuations in the unit variable production costs due to efficiencies and pay rates of the labor force, and varying materials prices. The production department is considering the use of alternative demand-based data in its forecasts.
Data containing the units sold and unit variable costs for the first three months of the year, along with amounts for total fixed overhead costs and selling, general, and administrative (SG&A) costs for the first three months of the year are shown in the following file:
- Module 2 CTA Excel File TemplateDownload Module 2 CTA Excel File Template
Requirements
There are three requirements to this problem. Use the included Excel file to perform each of the following.
- Populate the total costs and the operating income columns of a new data table in the Student Work area for each day provided in the three-month period.
- Perform a forecast using Excel’s Forecast Sheet tool based on the new data table. Excel will place the data on a new separate worksheet for you on the provided Excel file.
- You talked to the manufacturing group and they told you that they use Google search data to create a demand forecast, which is very accurate. A scatter graph of the expected operating income using the search index is provided here.
