need asap
Output
Regression | ||||||||||||||||||
R² | 0.7 | 3 | ||||||||||||||||
Adjusted R² | 0.725 | n | 66 | |||||||||||||||
R | 0.859 | k | 3 | |||||||||||||||
Std. Error | 483.063 | Dep. Var. | Salaries and employee benefits | |||||||||||||||
ANOVA | ||||||||||||||||||
Source | SS | df | MS | F | p-value | |||||||||||||
40,757,899.8382 | 3 | 13,585,966. | 61 | 58.22 | 5.08E-18 | |||||||||||||
Residual | 14,467,687.9194 | 62 | 233,349.8052 | |||||||||||||||
Total | 55,225,587.7576 | 65 | ||||||||||||||||
Regression output | confidence interval | |||||||||||||||||
variables | coefficients | std. error | t (df=62) | 95% lower | 95% upper | |||||||||||||
Intercept | 7,144.8143 | 261.6878 | 27.303 | 0.00 | 6,621.7079 | 7,667.9208 | ||||||||||||
Real Estate Loans | 0.0044 | 0.0008 | 5.573 | 0.0028 | 0.0059 | |||||||||||||
Individual Loans | 0.0211 | 0.0056 | 3.756 | 0.0099 | 0.0323 | |||||||||||||
Small Bus Loans | 0.0208 | 0.0021 | 9.776 | 0.0165 | 0.0250 |
Sheet2
SUMMARY OUTPUT | ||||||||||||||||||||
Regression Statistics | ||||||||||||||||||||
Multiple R | 0.8590841877 | |||||||||||||||||||
R Square | 0.7380256416 | |||||||||||||||||||
Adjusted R Square | 0.725349463 | |||||||||||||||||||
Standard Error | 483.06294119 | |||||||||||||||||||
Observations | ||||||||||||||||||||
Significance F | ||||||||||||||||||||
40757899.8382078 | 13585966.6127359 | 58.2214611404 | 5.07903416960344E-18 | |||||||||||||||||
14467687.91 | 9368 | 233349.805151096 | ||||||||||||||||||
55225587.7575757 | ||||||||||||||||||||
Coefficients | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||||||||||||
7144 | 261.6877912527 | 27.3028187801 | 6621.707905517 | 7667.9207775721 | ||||||||||||||||
0.0043748584 | 0.0007849581 | 5.5733655759 | 0.0028057495 | 0.005 | 9439 | |||||||||||||||
0.0210590357 | 0.0056061 | 419 | 3.7564221932 | 0.0098525179 | 0.03 | 22655 | ||||||||||||||
0.0 | 2075 | 0.0021236064 | 9.7756568838 | 0.0165146189 | 0.0250046763 |
Sheet3
0. | 2182 | |||||||
0.0476334248 | ||||||||
0.0015511711 | ||||||||
590.9733443568 | ||||||||
1083016.66083366 | 361005.55361122 | 1.0336609217 | 0.3839700839 | |||||
21653468.6118936 | 3492 | |||||||
22736485.2727273 | ||||||||
2355.6747153756 | 320.145670444 | 7.3581339148 | 0.0000000005 | 1715.7126450247 | 2995.6 | 3678 | ||
-0.000810673 | 0.0009603082 | -0.8441800728 | 0. | 401 | -0.0027303018 | 0.0011089557 | ||
-0.0036974726 | 0.006858486 | -0.5391091618 | 0.5917422534 | -0.0174073906 | 0.0100124454 | |||
0.0043389758 | 0.0025979943 | 1.6701252037 | 0.0999361967 | -0.0008543408 | 0.0095322924 |
Sheet4
0. | 4060 | |||||||||||
0.1648847878 | ||||||||||||
0.1244759872 | ||||||||||||
2078.2 | 17104 | |||||||||||
52869805.7004447 | 17623268.5668149 | 4.0804177663 | 0.0104016139 | |||||||||
267777152.663192 | 4318986.33327729 | |||||||||||
320646958.363636 | ||||||||||||
15081.44 | 1821 | 1125.8 | 2439 | 13.3 | 9590 | 5.78891 | 2950 | 12830.9509432152 | 17331.9326998716 | |||
0.0046337335 | 0.0033770202 | 1.3721367283 | 0.1749651736 | -0.0021168337 | 0.0113843007 | |||||||
0.0276978947 | 0.02411 | 8554 | 1.1484060777 | 0.2552125184 | -0.0205144085 | 0.0759101978 | ||||||
0.0237638541 | 0.0091361079 | 2.6010916603 | 0.011605023 | 0.0055010348 | 0.0420266735 |
NonInterest Exp
Bank ID | Tot Noninterest Expense | Fixed asset and premise expense | ||||||
1051465 | 219838 | 38838 | 71632 | 2168 | 11488 | 2618 | ||
1051979 | 243990 | 4099 | 48070 | 18010 | 9528 | 1840 | ||
1064915 | 170149 | 15864 | 55760 | 16489 | 1996 | |||
1071726 | 267980 | 18149 | 28991 | 16398 | 8922 | 2203 | ||
1076123 | 353210 | 20833 | 19120 | 2551 | ||||
1076619 | 388757 | 5315 | 2256 | 18183 | 9702 | 2258 | ||
1081613 | 261131 | 18380 | 12703 | 16530 | 9563 | 1863 | ||
1082393 | 271068 | 25006 | 35210 | 17826 | 10420 | 2613 | ||
1086122 | 277475 | 7873 | 23970 | 15681 | 8833 | 1718 | ||
1099672 | 230078 | 20129 | 41753 | 15538 | 9749 | 1564 | ||
1099908 | 288455 | 15017 | 49928 | 17256 | 9850 | 1995 | ||
1119383 | 377872 | 29399 | 34839 | 16539 | 9453 | 1822 | ||
1127146 | 312853 | 13311 | 95584 | 21982 | 10955 | |||
1134014 | 217259 | 12906 | 47055 | 16759 | 9289 | 3009 | ||
1 | 1359 | 1 | 23294 | 26658 | 13916 | 14559 | 8099 | 1498 |
1136009 | 248642 | 4702 | 40364 | 16512 | 1250 | |||
1140127 | 341148 | 14653 | 114934 | 17611 | 10782 | 2317 | ||
1206733 | 218600 | 1115 | 35084 | 19610 | 8896 | 3305 | ||
1208513 | 307943 | 16133 | 32778 | 15742 | 9828 | 1769 | ||
1246476 | 319400 | 28907 | 64032 | 17728 | 10246 | 2005 | ||
1247455 | 436927 | 14837 | 73021 | 19203 | 10959 | 2027 | ||
1247576 | 240472 | 15879 | 64117 | 17515 | 10372 | 1707 | ||
1830361 | 274984 | 5400 | 34619 | 15193 | 8490 | |||
1838590 | 148482 | 5227 | 13247 | 15678 | 7721 | 1719 | ||
1944204 | 398473 | 2222 | 62896 | 19693 | 10518 | |||
1 | 9470 | 250884 | 21554 | 45234 | 18467 | 8969 | 2024 | |
2227182 | 291100 | 8999 | 75762 | 17002 | 10319 | 2646 | ||
2237118 | 358759 | 347 | 69329 | 20119 | 10783 | 3064 | ||
2290560 | 407867 | 624 | 31254 | 19689 | 2938 | |||
2312837 | 229065 | 1926 | 25467 | 15706 | 8260 | 2021 | ||
2314327 | 445285 | 4105 | 92514 | 19702 | 10840 | 1597 | ||
2321419 | 390635 | 4445 | 74130 | 17610 | 9842 | 1611 | ||
2324997 | 287417 | 5050 | 51977 | 15143 | 10199 | |||
2327541 | 304608 | 17629 | 16563 | 8071 | 2381 | |||
2391270 | 333091 | 31927 | 18645 | 9303 | 1942 | |||
2467474 | 264585 | 12263 | 88566 | 17943 | 9895 | 3158 | ||
2492 | 438764 | 15624 | 66881 | 22257 | 10297 | 3186 | ||
2497462 | 280389 | 8697 | 24438 | 19348 | 9328 | 2434 | ||
2603928 | 317943 | 44240 | 21404 | 10131 | 3938 | |||
2641694 | 467703 | 39984 | 68828 | 22568 | 11988 | 2824 | ||
2693273 | 348271 | 37149 | 19429 | 10060 | 2309 | |||
2745604 | 316288 | 15117 | 116195 | 21234 | 11343 | 2118 | ||
2809560 | 211831 | 1376 | 13591 | 17207 | 8816 | 2138 | ||
2838159 | 314256 | 3659 | 60259 | 17635 | 10290 | 1819 | ||
3030307 | 2 | 2197 | 4501 | 73981 | 20417 | 9788 | 2879 | |
3104570 | 286850 | 50136 | 10044 | 3237 | ||||
3121344 | 330504 | 8781 | 110730 | 19239 | 11751 | 2545 | ||
3145966 | 286665 | 9462 | 41879 | 19496 | 9238 | 2674 | ||
3228702 | 359689 | 35086 | 40604 | 17176 | 9969 | 1768 | ||
3367094 | 252486 | 4343 | 28198 | 16196 | 9091 | 2318 | ||
3434633 | 382798 | 84267 | 18305 | 10429 | 2274 | |||
3454220 | 326673 | 1235 | 65454 | 17073 | 9178 | |||
3461358 | 333317 | 8598 | 115588 | 11276 | ||||
3595084 | 385166 | 29857 | 90197 | 18329 | 10751 | 2398 | ||
3596009 | 229344 | 68960 | 15213 | 8946 | ||||
3696093 | 173885 | 27761 | 27514 | 18242 | 8751 | 3308 | ||
3726440 | 341478 | 1229 | 36396 | 18148 | 10008 | 2875 | ||
3802250 | 397737 | 7794 | 112933 | 16810 | 10978 | 2347 | ||
3851230 | 347140 | 6580 | 22135 | 22927 | 9826 | 2408 | ||
3854642 | 245271 | 9351 | 67866 | 20863 | 10624 | |||
3908929 | 348607 | 11886 | 28738 | 17986 | 9811 | 1862 | ||
3948439 | 488842 | 1409 | 14602 | 9393 | 1195 | |||
4183536 | 439118 | 6581 | 10825 | 14302 | 9366 | 1297 | ||
4299147 | 312874 | 27923 | 13534 | 8508 | ||||
4305477 | 172258 | 45054 | 35114 | 19193 | 2349 | |||
4360113 | 321071 | 9009 | 17642 | 16399 | 8808 | 2484 |
Managerial Accounting Homework
Cost Estimation in the Banking Industry
Cost management is particularly important in the banking industry where pricing is competitive and interest rates are set by a combination of market forces and regulatory policies. Fictitious Bank Corp, is a midsized privately owned bank operating in multiple states. The company offers a range of traditional banking services including checking and savings accounts, IRAs, home and personal loans, and small business loans.
In an effort to stay competitive in the marketplace, Fictitious’ top management has hired your team to perform an industry benchmark analysis of their different expense categories to assess their competitiveness in the market.
Data Collection*
As an industry expert in banking, you have access to data on Fictitious’ competitors. You have compiled data on the non-interest expenses of 66 competitors with total assets between 300 and 700 million dollars. This data is provided in the bankdata.xlsx file in BlackBoard and includes non-interest cost and loan data, in thousands of dollars. Data on three types of expenses were collected, with definitions of these expense provided below.
Selected Data Definitions
Total Non-Interest expense: all expenses incurred in the operation of the bank, which are not related to the interest paid on deposits and other liabilities.
Wage and Salary expense: total wages and salaries of bank employees excluding top executives. This expense includes insurance and other benefits.
Fixed Asset expense: expenses related to servicing physical assets such as rent, maintenance, and janitorial services. This number also includes depreciation on building and equipment owned.
*all data a presented in thousands of dollars
Assignment
Instructions: Please embed your answers inside the document after each question. If you are unsure about something, feel free to email me for guidance.
1. Using the data provided, estimate the following OLS regression models for each of the three different types of expenses: wage expense, fixed asset expenses, and total non-interest expenses (i.e. run three different regressions).
Expense = α + β1*R.E. Loans + β2*Pers. Loans + β3*Small Bus. Loans + ε
Answer:
(i) Wage expense
The dependent variable:
· Wage expense
The independent variable:
· R.E Loans
· Individual loan
· Small business loan
The regression output is given below
.
Regression Statistics
Multiple R
0.859084
R Square
0.738026
Adjusted R Square
0.725349
Standard Error
483.0629
Observations
66
ANOVA
df
SS
MS
F
Significance F
Regression
3
40757900
13585967
58.22146
0.000
Residual
62
14467688
233349.8
Total
65
55225588
Coefficients
Standard Error
t Stat
P-value
Intercept
7144.814
261.6878
27.30282
0.00
Real Estate Loans
0.004375
0.000785
5.573366
0.00
Individual Loans
0.021059
0.005606
3.756422
0.00
Small Bus Loans
0.02076
0.002124
9.775657
0.00
The OLS regression model is
Wage expense = 7144.814 + 0.004375* RE loans + 0.021059*Ind loans +0.02076*Small bus loans
(ii) fixed asset expenses
The dependent variable:
· fixed asset expenses
The independent variable:
· R.E Loans
· Individual loan
· Small business loan
The regression output is given below.
Regression Statistics
Multiple R
0.218251
R Square
0.047633
Adjusted R Square
0.001551
Standard Error
590.9733
Observations
66
ANOVA
df
SS
MS
F
Significance F
Regression
3
1083017
361005.6
1.033661
0.38397
Residual
62
21653469
349249.5
Total
65
22736485
Coefficients
Standard Error
t Stat
P-value
Intercept
2355.675
320.1457
7.358134
5.15E-10
Real Estate Loans
-0.00081
0.00096
-0.84418
0.401814
Individual Loans
-0.0037
0.006858
-0.53911
0.591742
Small Bus Loans
0.004339
0.002598
1.670125
0.099936
The OLS regression model is
Fixed asset expense = 2355.675-0.00081* RE loans -0.0037*Ind loans +0.004339*Small bus loans
(iii) Total non interest expense
The dependent variable:
· Total non interest expense
The independent variable:
· R.E Loans
· Individual loan
· Small business loan
The regression output is given below
Regression Statistics
Multiple R
0.40606
R Square
0.164885
Adjusted R Square
0.124476
Standard Error
2078.217
Observations
66
ANOVA
df
SS
MS
F
Significance F
Regression
3
52869806
17623269
4.080418
0.010402
Residual
62
2.68E+08
4318986
Total
65
3.21E+08
Coefficients
Standard Error
t Stat
P-value
Intercept
15081.44
1125.824
13.39591
5.79E-20
Real Estate Loans
0.004634
0.003377
1.372137
0.174965
Individual Loans
0.027698
0.024119
1.148406
0.255213
Small Bus Loans
0.023764
0.009136
2.601092
0.011605
The OLS regression model is
Total non interset expense = 15081.44 +0.004634* RE loans +0.027698*Ind loans +0.023764*Small bus loans
2. Interpret the coefficients and explanatory power of the models in part1. Which costs are fixed and variable? Does the size and statistical significance of these coefficients seem to match what you would think about the three different types of costs?
3. Estimate the wage expense for a bank with $70 million in small business loans, $285 million in real estate loans, and $10 million in personal loans.
4. A member of Fictitious’ board believes that Fictious’ labor costs are too high relative to the amount of loans they make. He cites a study conducted by a large accounting firm, which included data from banks such as Bank of America and Wells Fargo. Conceptually (without comparing models, etc.), discuss the validity of the board members claim.
Hint: relevant range