Week #	Production volume (units)	Total costs for the week
1	977	$37,886.59
2	1,135	$38,137.62
3	1,003	$37,053.76
4	922	$33,952.98
5	854	$34,055.61
6	824	$35,622.84
7	985	$37,803.80
8	849	$34,618.20
9	1,091	$37,018.40
10	1,084	$35,274.24
11	938	$34,610.79
12	1,183	$40,100.12
13	1,124	$36,823.79
14	1,044	$34,686.72
15	866	$30,256.05
16	830	$35,157.51
17	1,113	$37,982.99
18	1,059	$37,526.53
19	1,112	$38,686.43
20	1,001	$37,551.24
21	992	$37,023.53
22	907	$31,664.32
23	1,155	$37,873.68
24	800	$31,845.70
25	970	$36,351.37
26	1,131	$36,261.08
27	815	$31,158.10
28	1,149	$40,782.70
29	965	$36,409.60
30	978	$41,141.34
31	970	$38,245.22
32	1,125	$36,267.05
33	1,116	$37,015.97
34	1,026	$34,609.60
35	861	$37,828.37
36	998	$35,139.12
37	897	$32,836.43
38	1,146	$36,815.82
39	828	$33,315.02
40	940	$34,379.39
41	1,063	$36,083.59
42	918	$37,945.48
43	978	$36,628.48
44	996	$37,103.21
45	1,091	$36,106.65
46	1,165	$39,334.84
47	1,010	$37,762.04
48	1,010	$36,368.49
49	1,020	$34,829.63
50	906	$33,308.73
51	1,178	$41,596.14
52	903	$33,304.19

Use excel to do the questions

1. Cost structure estimation (management accounting) As a management accountant, you need to understand the cost structure (i.e., fixed and variable costs) for the facility. Regress Y = Total costs for the week on X = Production volume (reminder: go to Data->Data analysis->Regression). The regression intercept will capture total fixed costs, and the slope coefficient on X will capture variable costs per unit.

(a) What is your estimate of total fixed costs?

(b) What is your estimate of variable costs per unit?

(c) How well does the regression explain the behavior of total costs?

(d) Visualize the relation between production volume and total costs using a scatter plot.

2. Detecting abnormal behavior (both audit and management accounting) The regression estimates describe typical or "normal" behavior of costs. However, often we are more interested in "abnormal" behavior. For example, to an auditor, abnormally high costs could indicate possible fraud (BAD), and abnormally low costs could indicate an accounting error or deliberate misreporting (BAD). To a management accountant, abnormally high costs could indicate production disruptions or inefficiency (BAD), and abnormally low costs could indicate high production efficiency (GOOD). Costs vary a lot with production volume. Therefore, it is not surprising that costs will be high when production is high and vice versa. This is perfectly normal. To detect "abnormal" behavior, we need to focus on situations in which costs are either too high or too low relative to the concurrent production level. A regression can help us here. First, use the regression estimates from step 1 to predict the "normal" level of total costs for each week: "Normal" costs = Fixed costs + Variable costs per unit * Production Volume for the week Compute these "normal" values for each week in column D. After that, compute the "abnormal" costs for each week as: "Abnormal" costs = Actual total costs from column C minus the "normal" costs you computed in column D. Compute these abnormal costs in Column E.

(a) Which week had the most "abnormally high" costs? (hint: sort the data by column E)

(b) Which week has the most "abnormally low" costs?

(c) As an auditor, you will want to pay a lot of attention to these two "abnormal" weeks. How would you assess whether or not the "abnormally high" week represents fraud? What additional information would you need?

3. Financial statement analysis -- duPont analysis The duPont framework decomposes Return on equity (ROE) into Profit margin (PM), Asset turnover (AT), and Equity Multiplier (EM): ROE = PM*AT*EM where PM = Net income/Net sales, AT = Net sales/Total assets, and EM = Total assets/Total equity. This decomposition helps us understand the drivers of our ROE. For example, a firm could have high ROE because it has: high pricing power (PM), and/or high asset use efficiency (AT), and/or high financial leverage (EM). We will implement this analysis for Southwest Airlines.

(a) Find the financial statements of Southwest Airlines for year 2022 (you can use Yahoo Finance). Fill in the following numbers. Keep in mind that Southwest might use a slightly different terminology (e.g., "revenue" instead of "sales"; if you cannot find a perfect match for an item, use the most reasonable match). Net sales?

Net income?

Total assets?

Total equity?

(b) Using the numbers from part (a), compute the following ratios: Return on equity ROE = Net income / Total equity?

Profit margin PM = Net income/Net sales?

Asset turnover AT = Net sales/Total assets?

Equity multiplier?

(c) Interpret in plain English the numbers that you got in part (b). For example, does the ROE number indicate good or bad performance? What are the main drivers (among PM, AT, and EM) contributing to this good or bad performance?