Need answer for #3. Show formulas in excel

Requested Allegheny Bank Topics/Areas Activity-Based Costing, Customer line Profitability, and Presenting Financial Information Case Study The Allegheny Bank began operations in the mid-1990s. The bank quickly grew by providing checking account services to many small businesses that preferred to do business with a "local" bank. Although the bank initially offered checking account services for individual accounts (retail customers), the bank primarily focused on serving its business customers. Unfortunately, during the economic slowdown of the early 2000s (following the 9/11 terrorist attacks) that weakened the local economy, growth in business customer accounts began to decline. In response, Allegheny's senior management adopted a new strategy, focusing on increasing the number of retail customer accounts. By aggressively marketing individual retail accounts, Allegheny continued to grow. Yet, dark clouds were on the horizon. Recent income statements (below, S000) reveal a decline in the bank profits. FY 2019 FY 2020 FY 2018 $3,349 $3,417 $3,486 475 484 Net Interest Income Provision for Credit Loss Net Interest Income after provision for credit losses 2,884 2,942 3,002 Noninterest Income* 1,190 1,199 1,207 Noninterest Expenses 3.362 3.539 3.805 712 602 404 Pre-tax Income 230 Income Tax Expense 194 130 482 408 274 Net Income To counter falling profits, the bank took two actions in FY 2019, both aimed at increasing the bank's retail customer base: 1. It established a customer call center to respond to customer inquiries about account balances, checks cleared, fees charged, etc., and 2. It authorized year-end bonuses to branch managers who met their branch's target increase in the number of customers. However, even though 80% of the branch managers met the targeted increase in customer accounts, Allegheny Bank's profits continued to decline. John Diamond, the CEO, didn't understand why profits were declining, even though the bank was serving more customers. The Beaver County branch manager, Rosie Perez, also noticed that while small retail customers flocked to the bank, the number of business customers is barely stable. The Allegheny Bank's costing system, developed back in 1999, is straightforward. The bank does not trace any costs directly to individual customers. It simply treats all (noninterest expense) operating costs identified in the Income Statements (above) as indirect with respect to the customer line. The bank allocates these indirect costs to either the retail customer line or the business customer line, based on the total dollar value of checks processed (which is readily available because each branch must provide the dollar values of daily transactions for internal control). For the current period, the bank processed a total of $95 million in checks, of which $9.5 million was written by retail customers, and $85.5 millon was written by business customers. This costing approach was fairly typical of banks and other financial institutions at the time Allegheny developed its cost system. Perez suspected that the Allegheny Bank's cost system might be part of the problem. Perez learned about activity based costing (ABC) in school, but the applications involved manufacturing firms. She wonders whether Allegheny Bank could develop an ABC system, with the customer-line (business customer accounts and retail customer accounts) as the primary cost object. Rosie approached John Diamond with this suggestion; but, John was skeptical, exclaiming, Our profits are going down the tubes and you want me to spend money developing a new accounting system?" However, Rosie persisted, and John eventually authorized a pilot ABC study using three local branches. The ABC implementation team included Perez, the managers of each of the three bank branches, a bank teller, a customer service representative from the customer call center, and YOU! (Yeah, somehow you got recruited for the team). The team began by identifying the activities the bank performed. To start a simple pilot study, the team identified the three most important activities: Paying checks Providing teller services Responding to customer account inquiries at the customer service call center The ABC team began by determining the costs that are associated with each of the three activities. The team quickly discovered that, as is typical in service industries, labor (personnel) costs dominate. The ABC team asked each employee to fill out a short questionnaire to find out how the employee spends his or her time. The team followed up with in-depth interviews with each employee. The ABC team used this combined information to estimate the percentage of time each employee spent on each of the three activities noted above: (1) paying checks, (2) providing teller services, and (3) responding to customer account inquiries. The team estimated the other (nonlabor) resources that each of the three activities consumed. For example, they traced to the "responding to customer account inquiry" activity: (1) the cost of toll-free telephone lines at the customer service call center, and (2) depreciation on the other equipment and facilities the call center personnel use. Similarly, the team estimated the percentage of time the bank's information system was used for check processing and providing teller services (vs. other uses such as compiling periodic financial statements), to determine how much of the equipment's depreciation to assign to the activities "paying checks and providing teller services." To complete the pilot study in a timely fashion, the ABC team based their estimated activity costs on last year's (FY 2020) actual data. If the pilot study succeeded, then the ABC team planned to develop budgeted indirect cost rates for each activity the following year. After examining the three branch banks' indirect costs (that is, the cost items making up the branch banks' non-interest operating expenses), the ABC team classified the bank's annual costs in each activity's cost pool (S000), as shown below. While the table that follows sums to $2,850, know that this amount is part of the $3,805 "noninterest expenses" in the bank's FY 2019 Income Statement. The difference in these two amounts pertain to other operating costs that are excluded from the ABC pilot study, such as the CEO's salary. Activity Cost Pool to Which Cost is Assigned Estimated Annual Costs (5000) Indirect Costs Paying checks $700 Salaries of check-processing personnel Depreciation of equipment and facilities used in check processing Paying checks 440 Providing teller services Teller salaries 1,000 Depreciation of equipment and facilities used in teller operations Providing teller services 200 Salaries of customer representatives at call center Responding to customer account inquiries 450 Toll-free phone lines plus depreciation of equipment and facilities in customer call Responding to customer account inquiries center 60 Total indirect costs $2.850 The team identified the following cost drivers for each activity cost pool: Activity Cost Pool Activity Cost Driver Paying checks Providing teller services Responding to customer account inquiries Number of checks processed Number of teller transactions Number of account inquiry calls to customer service call center The ABC team estimated that for the three pilot-test bank branches, the retail and business customer lines experienced the annual activity levels in thousands) shown below. For example, the table below reveals that retail customers had 160,000 teller transactions and made 95,000 account inquiry calls to the customer service call center. Annual Number of Units of Annual Number of Units Activity Cost Driver Used by of Activity Cost Driver Retail Customers in Used by Business thousands) Customers (in thousands) Activity Cost Driver Checks processed 570 2,280 Teller transactions 160 40 Account inquiry calls to customer service call center 95 5 The bank currently serves 150,000 retail customer checking accounts and 50,000 business customer checking accounts. The bank earns net interest revenue on the balances that customers keep in their checking accounts. (The bank earns net interest revenue by managing the interest rate spread." This spread is the difference between the interest rate the bank carns on customer deposits (say 8 percent), less the interest rate the bank pays the customer on the average checking account balance (say 4 percent)). On average, the bank earns the following annual revenue from each type of account: Average annual revenue per retail customer account, Average annual revenue per business customer account $10 $40 3. Using the new activity-based costing system: a. Compute the indirect cost allocation rates for each of the three activities: i. paying checks ii. providing teller services iii. responding to customer account inquiries b. Use the schedule below as a template to compute the total indirect cost allocated to each customer line. Total Indirect Cost Assigned to Retail Customer Line Total Indirect Cost Assigned to Business Customer Line Activity Paying checks Providing teller services Responding to customer account inquiries Total Indirect Costs c. What proportion (%) of each activity is attributable to (i) the retail customer line, and (ii) the business customer line? Page 6 of 8