ACCT 3050 Individual Project - Part 5 Cost Estimation (10 Points)

The PZB Bag Company operates a chain of handbag stores that sell 10 different styles of inexpensive womens bags. PZB wants to better understand their operations and develop an equation to estimate some of their operating costs for the operating budget in 2021. The following cost and other data were gathered for 2020:

REQUIRED: Using Excel complete the following requirements:

1. Create a new Excel Workbook with Seven (7)) worksheets for this assignment and save the workbook in the following format LastName_FirstName_Exercise_2

2. Worksheet #1: Create a Data Dictionary sheet in the workbook. Be sure to include all the important items that a user of the workbook could refer to. For example, your data dictionary could include the definition of variable costs, mixed costs, fixed costs, cost driver, independent variable, dependent variable, etc.

3. Worksheet # 2: Then, create a master data worksheet. Specifically, this worksheet should have all the given information in this problem as shown above.

4. Worksheet # 3: in this worksheet populate the cells to determine which of the costs, maintenance, health insurance or shipping is variable, fixed or mixed. Keep in mind that I will be looking for formulae in the excel worksheet that is consistent with the characteristics of these cost behaviors. From your analysis, choose one month and its related VC, FC, and MC. For each calculate the per unit cost. Show whether the unit costs stays the same, increases, or decreases as volume increases and provide an explanation. Create a scatterplot for each of the costs and its related cost drivers.

5. Worksheet # 4: Using the high-low method, determine the cost function for each cost.

6. Worksheet # 5: Use the cost functions in the previous information to develop a combined or overall monthly operating cost function for the PZB bag company.

7. Worksheet #6: Next month, PZB expects to use 505 machine hours, have 89 employees, and ship 7,800 units. Estimate the total operating cost for the month.

8. Worksheet #7: Conduct a multiple regression to estimate the regression equation for operating costs using all the data and the appropriate independent variables you have identified in your master data worksheet#2.

9. Evaluate the multiple regression output using the criteria of economic plausibility, goodness of fit, and significance of independent variables.