Can I have the exacl formula as soon as possible?Thank you

Refer to Table 1. Write the Excel formula for each cell marked with w?" in columns B. Cand D such that formula could be copied and pasted into columns D, E, F and G using Microsoft Excel without further editing. There is no need to explicitly write the Excel formula for sells marked with "Copy & paste". Labelcach formula clearly with cell reference position, In 2020, BS Bank employs 800 employees in its call centres in the United States (U.S.), and the call centre operations involve answering queries from bank customers over the phone on credit cards, bank balances, deposits and other investments. The call centre operations are important but expensive in the U.S., and BS Bank's management is considering laying off these U.S. employees and "off-shoring" its call centre operations to India for the next five years (2021-2025) where employment costs are significantly lower, and where a high quality of service could be delivered from Indian college graduates. An Indian firm, Bangalore InfoSystems (BI), would be hired as a contractor to run the call centre operations, arrange for a site, hire and train call centre employees in India. Cash would be freed up by foregoing telecommunications, computing and other real estate costs in the U.S. However, there are many hidden costs in transferring operations to off- shore" countries. You are required to use Microsoft Excel (see table in which figures for costs and benefits are at the end of each year) to forecast if the financial benefits of "off-shoring" operations to India would outweigh the costs based on the Net Present Value (NPV) approach. NPV is a standard economic analysis for using "time value of money to appraise multi-period, long-term investments. BS Bank's appropriate discount rate for NPV analyses has been 25% (row 18). which is the minimum acceptable rate of earnings on potential long- term investments. Thus, if the "off-shoring investment shows a positive NPV at 25% discount rate, the Chief Information Officer (CIO) will consider the "off-shoring" operations to India financially justified. Soms hidden costs (cash outflows of "off-shoring opstations to India would include: Training a call centre employee will cost more in India than in the U.S. because Indian employees must be trained to speak like Americans as well as being trained on technical aspects of banking, When a customer from the U.S. calls about a credit card error, he/she does not want to be distracted by someone with a foreign accent. Training cost per Indian employee (row 12) is expected to be US$1.600 per year in 2021, increasing cach year in future. Legal and technical consulting in India (row 13): Costs would involve hiring an Indian law firm for legal advice and hiring an Indian technical firm for technical advice in evaluating the best contractor (BI is chosen). In 2020, BS Bank spent US$15 million for legal & technical consulting fees during the initial setup of "off-shore operation. BS Bank expects to spend US$6 million on maintaining the relationship in 2021, with the amount increasing slightly cach year in future. . Companies that have outsourced labour in India have noticed that day-to-day communication with their contractor is more successful if some representatives (called liaison employees) are stationed with their contractor. Thus BS Bank would send two of its employees to India to work with BI, and BI would send two of its employees to work with BS Bank in the U.S. (row 14). The cost of a liaison employee is expected to be US$200,000 in 2021, increasing cach year (row 15). This expense covers the cost of a liaison employee's salary and housing in Background security checks for new call centre employees: In the U.S., background checks are inexpensive, because so much information about residents is readily available in digital format. In India, however, background information is difficult to obtain and thus, background checks are expensive. A background check would cost US$1,000 per Indian employee in 2021, increasing each year thereafter (row 16). A background check would cost US$20 per U.S. employee in 2021 increasing each vear thereafter (row 17). Some financial benefits for cash inflows) of "off-shoring operations to India would include: . At the U.S. call centres, networking and telecommunication costs would be US$95 per employee in 2021. decreasing cach year thereafter (row 4). If the centre were off-shored, BS Bank would not have to pay for telecommunications directly. At the U.S. call centres, computing support costs would be US$390 per employee in 2021. decreasing each year thereafter (row 5). If the centre were off-shored, BS Bank would not have to pay for computing support directly. At the U.S. call centres, real estate costs would be USS1,100 per employee in 2021, increasing each year thereafter (row 6). Real estate costs include upkeep on the call centre building, insurance etc. If the centre is off-shored, BS Bank could use the site for other purposes. . For every 20 U.S. call centre employees, BS Bank employs 1 supervisor (row 7). If the centre is off-shored, supervisors could be used in other areas of the bank, thus a benefit for BS Bank. The average salary cost of a U.S. call centre employce will be US$30,000 in 2021, increasing each year thereafter (row 8). The average salary cost of a U.S. call centre supervisor will be US$52,000 in 2021, increasing each year thereafter row 9). If the centre were off-shored, these salary costs would be forgone. . At the U.S. call centres, 11% of call centre employees are expected to leave and be replaced in 2021, and this turnover rate is expected to increase in future years (row 10). It is expected to cost US$1,100 to train a new employee in 2021, increasing in future years (row 11). If the centre were off-shored, these training costs would be forgone. Two important risk factors affecting the costs and benefits of "off- shoring operations to India are: ._Indian labour market risk (cell B20) has two values. F for "favourable". N for "not favourable". As the U.S. demand for offshore operations increases, so does the demand for qualified foreign labour, and thus wages and employce turnover rate are expected to escalate in 2021 - 2025 (N for this case in cell B20). However, some economic analysts think that as Indian technical schools are expected to turn out more and more qualified people, and thus wages and employee tumover rate will not go up greatly in 2021 - 2025 (F for this case in cell B20). . The currency exchange rate between Indian rupees and American dollars (cells C21 to G21) has three values: U for "Up". S for "Steady". D for "Down". The 5-year contract between BS Bank and BI is made in 2020 and the agreed currency exchange rate is fixed at USSI dollar to INR$45.85 rupees (cell B30), even though there will be currency fluctuations during the life of the contract. It is agreed that Bl contractor will be paid in rupees once per year but BS Bank operates in U.S. dollars. BS Bank may benefit financially by taking advantage of currency fluctuations. BS Bank calculates currency conversion in the following steps: - 1 computes the contract fee to Bl in U.S. dollars, . 24 converts the contract fee to rupees at the contract's rate of 45.85, 3 BS Bank acquires the needed rupees at the current exchange rate. If more than INRS45.85 rupees are expected to be exchanged for USSI dollar, U is entered in tow 21. If less than INRS45.85 rupees are expected to be exchanged for USS dollar, D is entered in row 21. If the currency exchange rate is expected to be steady, S is entered in row 21. An entry is made for each year of the 5-year contract. The pattern of SSSSS in cells C21 to G21 means steady fluctuation is estimated for all 5 years. The patter of SSDDD in cells C21 to G21 means steady fluctuation is estimated for the first 2 years, and down for the last 3 years Calculations (rows 24 to 55): Calculate intermediate results of benefits (cash inflows) and costs (cash outflows) for NPV analysis. Number of U.S. employees (row 24): If the off-shoring agreement were not taken, this is the number of non-supervisory U.S. staff needed. There were 800 U.S. call centre employees in 2020, and it is expected to increase 2% per year that is 2% more than previous year) due to expected banking business growth. Note that the number of employees must be an integer Number of Indian employees (row 25): If the off-shoring agreement were taken, this is the number of non-supervisory Indian staff needed. Initially, assume there is a 1-to-1 replacement of U.S. employees in 2020, that is 800 Indian employees (B25 800). India's college graduates are expected to be more productive than US employees, thus the number would increase only 1% per year (that is 1% more than previous year) due to expected banking business growth. Note that the number of employees must be an integer Number of new U.S. employees (row 26: If the off-shoring agreement were not taken, this is the number of new U.S. employees. New US employees are hired for two reasons: expanding business growth (see row 24) and replacing employees who left in prior year (use the US employee turnover rate in row 10). Note that the number of employees must be an integer Indian employee turnover rate (row 27). If the Indian labour market is expected to be favourable during the life of the 5-year contract. employee turnover rate will be 109. per year . If it is expected to be not favourable, employee turnover rate will be 29% per year. ._ Number of new Indian employees (row 28): If the off-shoring agreement were taken, this is the number of new Indian employees New Indian employees are hired for the same reasons as new U.S. employees - see row 26, thus the logic of calculation is the same as for new U.S. employees, except that in 2021, all Indian employees are new employees Average Indian employment cost per employee (row 29: In 2020, the average Indian employment cost per employee is US$16,000. If the Indian labour market is expected to be favourable during the life of the 5-year contract, salary levels will increase 8% per year (that is 8% more than previous year). If it is expected to be not favourable. the increase will be 15% per year (that is 15% more than previous year) . Number of rupees per U.S. dollar (sow 30: The agreed currency exchange rate is fixed at USS1 dollar So INR$45.85 rupees (cell 030) when the 5-year contract is made in 2020, but currency exchange rate fluctuates each day. If the exchange rate outlook is steady for that year, the number of rupees per U.S. dollar is the same as prior year. If the outlook is up, the number of rupees per U.S. dollar will be 2% more than prior year. If the outlook is down, the number of rupees per U.S. dollar will be 2% less than prior year Indian employment cost in U.S. dollar (row 32): This is based on the number of Indian employees and the average Indian employment cost (in USS) per Cup cost (in USS) per employec. - Indian employment cost in rupees (row 33): This is the payment of Indian employment cost to BI contractor in rupees, using the agreed exchange rate of 45.85 rupees to US$1. Actual USS needed for Indian employment cost (row 34). This is the amount of dollars actually required to pay BI contractor as the Indian employment cost for that financial year Cost (in USS) of liaison employees living in the U.S. (row 35%. This is based on the number of liaison employees living in the US and the cost (in USS) of a liaison employee Cost (in USS) of liaison employees living in India (row 37): This is based on the number of linison employees living in India and the cost (in USS) of a liaison employee. Payment of Indian liaison employees in rupees (row 38). The liaison employees living in India will be paid by BS Bank in rupees calculated at the agreed exchange rate of 45.85 rupees to USSI. . Actual USS needed for Indian liaison payments (row 39): This is the number of dollars actually required to pay for the liaison employees living in India for that financial year. Cost of background checks in India (row 41): This is the cost (in USS) of background security checks performed by BI for new Indian employees in a year Payment of Indian background checks in rupees (row 42% This is the payment of Indian background checks to Bl contractor in rupees, using the agreed exchange rate of 45.85 rupees to USSI. Actual USS needed for Indian background checks (row 43). This is the number of dollars actually required to pay for the Indian background checks for that financial year. Cost of training new Indian employees in US dollar (row 45): This is based on the number of new Indian employees and the training cost (in USS) per Indian employee Payment of Indian training cost in rupees (row 46): This is the payment of Indian training cost to Bl contractor in rupees, at the agreed exchange rate of 45.85 rupees to USSI. . Actual USS needed for Indian training cost (row 47). This is the number of dollars actually required to pay for the Indian training cost for that financial year. Cost of background checks in the U.S. (row 48): This is the cost (in USS) of background security checks performed for new U.S. employees in a year .U.S. telecommunication costs (row 49): This is based on the number of U.S. call centre employees and the cost of U.S. telecommunication per call centre employee. .U.S.computer costs (row 50): This is based on the number of U.S. call centre employees and the cost of U.S. computer per call centre employee US real estate costs (row 51): This is based on the number of US. call centre employees and the cost of U.S. real estate per call centre employee .U.S. training costs (row 52): This is based on the number of new U.S. call centre employees and the cost to train a U.S. call centre employee ..Number of U.S. supervisors (row 53): This is based on the number of U.S. call centre employees and the ratio of U.S. call centre employees to supervisor. Note that the number of supervisors must be an integer Cost of U.S. supervisors (row 54): This is based on the number of U.S. supervisors and the average employment cost for a U.S. call centre supervisor Cost of U.S. employees (row 55): This is based on the number of U.S. call centre employees and the average employment cost for a U.S. employee. Net Present Value (NPV) calculation (rows 58 - 61): This is based on the net cash flows in the 5-year investments. The net present value of an investment is calculated by using a discount rate and a time series of payments /costs (negative values) and income/benefits (positive values). The net cash flow in a year is the difference between cash inflows (or investment benefits) and cash outflows (or costs). Formulae for rows 58 to 61 are given, their descriptions are given below; Cash inflows or benefits (row 58): These values are the total amounts that could be saved each year if the call centre is off-shored to India. Cash outflows or costs (row 59): These values are the total amounts that will be paid by BS Bank to Bl contractor each year if the call centre is off-shored to India. .Net cash flow (row 60): These values are the difference between cash inflows and cash outflows (that is, cash inflows less cash outflows) each year. Net present value (cell B61): Use Microsoft Excel's function - NPVO, to calculate the NPV at 25% discount rate (row 18) for the 5-year contract with BI if the call centre is off-shored to India. cell. S-year contract with Bl if the call centre is off-shored to India Table 1: "NA" - Not Applicable, meaning no entry is required in the 900 $ CONSTANTS &US TELECOM COSTS. PER EMPLOYEE USS N 3 V.S.OONPUTING COSTS. PER EMPLOYEES N FEX.ESTATE COST PERUS BIP.DYEUSS 2030 SE EMPLOYEE SUPER/SOR RATONUS 22 AVERAGE U.S. EMPLOYMENT COST PER EMPLOYEE USB HO 300 200 330 AVERAGE PONENT COST PER SUPERVISOR 50 500 5 US EMPLOYEE TURNOVER RATE M TRAINING COST PERUS EMPLOYEE USS 118 23 300 5 TRAINING COST PER INDIAN DIPLOYEE USS LETA AND TECHNICAL CONSULTING NDLUS 1920 x 700 TOTA, NUMBER OF LASON BPLOYEES 4 SOOST OF ONE SAISON BVP.OYEES COST OF BAYGROUND CHEXPEREMPLOYEE NDAN OOST OF BOXGROUND EXPEREMPLOSES 88 M2 2 5 DISCOUNT BATE FOR NPV 028 098 0303512 RISK FACTORS 2011 222 223 224 225 NDALABOR MARKET RSK FAVORABLE 23 N= NOT FAVORABLE ALL YEARS 21 EXCHANGE RATE U=UP S=57540Y, DEBO . BOLCULATIONS NUGER OF US BALDEES Cres Crise Day It Online 3 NUMBER OF ICH BIPLOEB Seas On Tree Onyiza lim ole BENUS EMPLOYEES A Linal Up Izz Online in intele NDAN BROETROBRATE MA Energy Confest Online Cialis EN NOANDIPLOYEES Alimentele Copy Ime Online BABAE NA BIP_MET COST PER DIPLOYEE 158 nare Opbretony oldal NUGER OFRPEES PER US DOLLAR 6 Cap Szere Op Sate ale NDAN BPOMENT DIST NOAK EMPLOYMENT COSTUS 4 lines Cap Sze Ceylan Ciale IOANEIPOMENT COST RUPEES lines Cepilzen Doyle Cimale SAATUALUS ESTED FOR ICH EIPLANET OST USO MA The Cp zee Online Caima lima * COST OF LASO DIPLOYEES US USA Aflmar Cop Szene Day I fee On iz bola * LOANLASEXBIPAREOST 3 COST CFLASO BP.016831 10488 The Copy Lines Day Ban Cliniz PAMENT FOR NOAVLASONBILDES RUPES Copy Lase Online olabiz SATA USI CESED FOR ACHA LABOMPANIES Ainars Cp lee Online Online NASAKOJO DEXOOST # COST OF BAOXGROUND CEDISH CHUSS Alinei Copilme online disable UPAMENTOS NOMBRESCRONO CHEOXS RUPEES Ma l'intera copil sarde Cayl met de la lola GADUAIS E PANCHAN BOXGROUOO EXS mare Oples Online izinle NATANGAS 2POS Olasz ABACE NOMBIPAMET COST PER DIPLOYEE USD 50 O marts Capellani 3 NUMBER OF RUPEES PERUS DOLAR & pe Cap Base Oplonial SA BPOMIENTOS NOMBIPONE COSTU WA Online On ima bible #KOMBIPONENT DIST RUPEES NA I Opis Opinizinle KETAUS EDED FOR ICH BIPLOMOTOOST 89 M Erate Opere deplase loialis *CS OF USO BIPOVEST USUS MA The Complex Colt talalla * ROANLIKSONBPOVES OOSI 900STOFUASO BPDVBS 110488 MA in Copy free Onlar bizle PANENT FOR NOAUASONBILDES RUFE mert Opi pare Op base de la la ARA USTEDET FOR ICHKLASOWANIES NA Intel Coplen Online tolila 4 NABOKONG DEX OOST limmern Cape reste plane Cose dala PAMENT OF IOAN BACKGROUND CHESS RUPEES A filmer Day Spas Online iniziale ATALUSI TEELEO PORNCANBOXCRODODENS A Press Complete ale tielle 1981 1983-O ONOLIKO SEK 1800 11 -BEN . 4 NDAV TRANG OOST NA & COST OF TRANING INOAN WORKERS LS timat Canaras Gange past Caritas Sayira & PAYMENT OF NOAN TRANING RUPEES Na filmat Operater Dany pest Omica iz O ACTUAL USI KETED FOR NDAN TANING IM Brais Cplate Cry 3 pase Opiz bola COST OF BAONGROINOO-EXS INUS 18 Almaty Oupy & paste Copy Lee Onize lolz GUS TELECOM COSTS Ma Ninang Top 8 mese Dry Spese Opla lale 3 US COMPUTBOOSTS M final Copy Aras Cany dras Cenise Caries SIUS REAL ESTATE COSTS NA Mimary Cap I see any page Opinie 2 US. TRANING COSTS MA in Copy I see Ceny best op iz le 5 NUVEER OF US SUPERVISORS NA Nments Op zate Copy Lene Opiz Dale SLUS.S.PER/SORY COSTS NA fy many Copy & pese Copy Spase Opleie 5 COST OF US BIPLOSES Na pomaly Couple paste Copy Lee Online Oylz sc la toitlarnis EN ALS 22 9 TUK BERTS CONS 100STE CASO 80 Besos BPN OF OF SONG NESTEN SPIEG 1 55 Dla la la la BS BANK'S CALL CENTER OFF-SHORING DECISION 2021 CONSTANTS 2020 U.S. TELECOMM COSTS, PER EMPLOYEE (US$) NA U.S. COMPUTING COSTS, PER EMPLOYEE (USS) NA REAL ESTATE COST, PER US EMPLOYEE NA EMPLOYEE to SUPERVISOR RATIO in U.S. NA AVERAGE US EMPLOYMENT COST PER NA AVERAGE EMPLOYMENT COST PER SUPI NA U.S. EMPLOYEE TURNOVER RATE NA TRAINING COST PER US EMPLOYEE (USS) NA TRAJNING COST PER INDIAN EMPLOYEE (US$) NA LEGAL AND TECHNICAL CONSULTING in INDIA (US$) 15000000 TOTAL NUMBER OF LIAISON EMPLOYEES NA COST OF ONE LIAISON EMPLOYEE (USS) NA COST OF BACKGROUND CHECK PER EMI NA COST OF BACKGROUND CHECK PER EM NA DISCOUNT RATE FOR NPV 0.25 RISK FACTORS 2020 INDIA LABOR MARKET RISK: F- FAVORABLE N NNOT FAVORABLE (ALL 5 YEARS) EXCHANGE RATE: U=UP S STEADY, D NA = DOWN 95 390 1100 20 30000 52000 0.11 1100 1600 6000000 4 200000 1000 20 0.25 2022 90 380 1200 20 31000 53000 0.12 1200 1700 6500000 4 210000 1100 25 2023 88 370 1300 20 32000 54000 0.13 1300 1800 7000000 4 220000 1200 30 0.25 2023 2024 80 360 1400 20 33000 55000 0.14 1400 1900 7500000 4 230000 1300 35 0.25 2024 2025 75 350 1500 20 34000 56000 0.15 1500 2000 8000000 4 240000 1400 40 0.25 2025 0.25 2021 2022 NA NA NA NA NA U U U U U 2021 2022 2023 2024 2025 Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste 12 marks] Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste -- CALCULATIONS 2020 NUMBER OF U.S. EMPLOYEES 800 1.5 marks] NUMBER OF INDIAN EMPLOYEES 800 11.5 marks NEW U.S. EMPLOYEES NA [2.5 marks) INDIAN EMPLOYEE TURNOVER RATE NA [2 marks) NEW INDIAN EMPLOYEES NA [1 mark] AVERAGE INDIAN EMPLOYMENT COST PER EMPLOYEE (US84000 3 marks] NUMBER OF RUPEES PER US DOLLAR 45.650 [3 marks] INDIAN EMPLOYMENT COST NA INDIAN EMPLOYMENT COST (USS) NA [1 mark] INDIAN EMPLOYMENT COST (RUPEES) NA [1 mark] ACTUAL USS NEEDED FOR INDIAN EMPLC NA [2 marks] COST OF LIAISON EMPLOYEES in U.S. (USS) NA [1 mark] INDIAN LIAISON EMPLOYEE COST: NA COST OF LIAISON EMPLOYEES in INDIA (US$) NA 11 mark] PAYMENT FOR INDIAN LIAISON EMPLOYE NA [1 mark] AC US$ NEEDED FOR INDIAN LIAISC NA [2 marks] INDIAN BACKGROUND CHECK COST NA COST OF BACKGROUND CHECKS in INDIA (US$) NA [1 mark] PAYMENT OF INDIAN BACKGROUND CHE NA 11 mark] ACTUAL USS NEEDED FOR INDIAN BACK NA [2 marks] INDIAN TRAINING COST NA COST OF TRAINING INDIAN WORKERS (USS) NA 11 mark) PAYMENT OF INDIAN TRAINING (RUPEES) NA [1 mark] ACTUAL USS NEEDED FOR INDIAN TRAINING NA [2 marks] COST OF BACKGROUND CHECKS IN U.S. (USS) NA 11 mark] U.S. TELECOMM COSTS NA [1 mark] U.S. COMPUTER COSTS NA [1 mark) U.S. REAL ESTATE COSTS NA (1 mark] U.S. TRAINING COSTS NA [1 mark] NUMBER OF U.S. SUPERVISORS NA [1.5 marks! U.S. SUPERVISORY COSTS NA [1 mark) COST OF U.S. EMPLOYEES NA [1 mark] Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & Paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste 2021 NET CASH FLOW ANALYSIS TOTAL BENEFITS (CASH INFLOWS TOTAL COSTS (CASH OUTFLOWS): Net cash flow (BENEFITS - COSTS) NPV OF OFF-SHORING INVESTMENT 2020 0 15000000 - 15000000 2022 2023 2024 2025 Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste NA NA NA NA NA Refer to Table 1. Write the Excel formula for each cell marked with w?" in columns B. Cand D such that formula could be copied and pasted into columns D, E, F and G using Microsoft Excel without further editing. There is no need to explicitly write the Excel formula for sells marked with "Copy & paste". Labelcach formula clearly with cell reference position, In 2020, BS Bank employs 800 employees in its call centres in the United States (U.S.), and the call centre operations involve answering queries from bank customers over the phone on credit cards, bank balances, deposits and other investments. The call centre operations are important but expensive in the U.S., and BS Bank's management is considering laying off these U.S. employees and "off-shoring" its call centre operations to India for the next five years (2021-2025) where employment costs are significantly lower, and where a high quality of service could be delivered from Indian college graduates. An Indian firm, Bangalore InfoSystems (BI), would be hired as a contractor to run the call centre operations, arrange for a site, hire and train call centre employees in India. Cash would be freed up by foregoing telecommunications, computing and other real estate costs in the U.S. However, there are many hidden costs in transferring operations to off- shore" countries. You are required to use Microsoft Excel (see table in which figures for costs and benefits are at the end of each year) to forecast if the financial benefits of "off-shoring" operations to India would outweigh the costs based on the Net Present Value (NPV) approach. NPV is a standard economic analysis for using "time value of money to appraise multi-period, long-term investments. BS Bank's appropriate discount rate for NPV analyses has been 25% (row 18). which is the minimum acceptable rate of earnings on potential long- term investments. Thus, if the "off-shoring investment shows a positive NPV at 25% discount rate, the Chief Information Officer (CIO) will consider the "off-shoring" operations to India financially justified. Soms hidden costs (cash outflows of "off-shoring opstations to India would include: Training a call centre employee will cost more in India than in the U.S. because Indian employees must be trained to speak like Americans as well as being trained on technical aspects of banking, When a customer from the U.S. calls about a credit card error, he/she does not want to be distracted by someone with a foreign accent. Training cost per Indian employee (row 12) is expected to be US$1.600 per year in 2021, increasing cach year in future. Legal and technical consulting in India (row 13): Costs would involve hiring an Indian law firm for legal advice and hiring an Indian technical firm for technical advice in evaluating the best contractor (BI is chosen). In 2020, BS Bank spent US$15 million for legal & technical consulting fees during the initial setup of "off-shore operation. BS Bank expects to spend US$6 million on maintaining the relationship in 2021, with the amount increasing slightly cach year in future. . Companies that have outsourced labour in India have noticed that day-to-day communication with their contractor is more successful if some representatives (called liaison employees) are stationed with their contractor. Thus BS Bank would send two of its employees to India to work with BI, and BI would send two of its employees to work with BS Bank in the U.S. (row 14). The cost of a liaison employee is expected to be US$200,000 in 2021, increasing cach year (row 15). This expense covers the cost of a liaison employee's salary and housing in Background security checks for new call centre employees: In the U.S., background checks are inexpensive, because so much information about residents is readily available in digital format. In India, however, background information is difficult to obtain and thus, background checks are expensive. A background check would cost US$1,000 per Indian employee in 2021, increasing each year thereafter (row 16). A background check would cost US$20 per U.S. employee in 2021 increasing each vear thereafter (row 17). Some financial benefits for cash inflows) of "off-shoring operations to India would include: . At the U.S. call centres, networking and telecommunication costs would be US$95 per employee in 2021. decreasing cach year thereafter (row 4). If the centre were off-shored, BS Bank would not have to pay for telecommunications directly. At the U.S. call centres, computing support costs would be US$390 per employee in 2021. decreasing each year thereafter (row 5). If the centre were off-shored, BS Bank would not have to pay for computing support directly. At the U.S. call centres, real estate costs would be USS1,100 per employee in 2021, increasing each year thereafter (row 6). Real estate costs include upkeep on the call centre building, insurance etc. If the centre is off-shored, BS Bank could use the site for other purposes. . For every 20 U.S. call centre employees, BS Bank employs 1 supervisor (row 7). If the centre is off-shored, supervisors could be used in other areas of the bank, thus a benefit for BS Bank. The average salary cost of a U.S. call centre employce will be US$30,000 in 2021, increasing each year thereafter (row 8). The average salary cost of a U.S. call centre supervisor will be US$52,000 in 2021, increasing each year thereafter row 9). If the centre were off-shored, these salary costs would be forgone. . At the U.S. call centres, 11% of call centre employees are expected to leave and be replaced in 2021, and this turnover rate is expected to increase in future years (row 10). It is expected to cost US$1,100 to train a new employee in 2021, increasing in future years (row 11). If the centre were off-shored, these training costs would be forgone. Two important risk factors affecting the costs and benefits of "off- shoring operations to India are: ._Indian labour market risk (cell B20) has two values. F for "favourable". N for "not favourable". As the U.S. demand for offshore operations increases, so does the demand for qualified foreign labour, and thus wages and employce turnover rate are expected to escalate in 2021 - 2025 (N for this case in cell B20). However, some economic analysts think that as Indian technical schools are expected to turn out more and more qualified people, and thus wages and employee tumover rate will not go up greatly in 2021 - 2025 (F for this case in cell B20). . The currency exchange rate between Indian rupees and American dollars (cells C21 to G21) has three values: U for "Up". S for "Steady". D for "Down". The 5-year contract between BS Bank and BI is made in 2020 and the agreed currency exchange rate is fixed at USSI dollar to INR$45.85 rupees (cell B30), even though there will be currency fluctuations during the life of the contract. It is agreed that Bl contractor will be paid in rupees once per year but BS Bank operates in U.S. dollars. BS Bank may benefit financially by taking advantage of currency fluctuations. BS Bank calculates currency conversion in the following steps: - 1 computes the contract fee to Bl in U.S. dollars, . 24 converts the contract fee to rupees at the contract's rate of 45.85, 3 BS Bank acquires the needed rupees at the current exchange rate. If more than INRS45.85 rupees are expected to be exchanged for USSI dollar, U is entered in tow 21. If less than INRS45.85 rupees are expected to be exchanged for USS dollar, D is entered in row 21. If the currency exchange rate is expected to be steady, S is entered in row 21. An entry is made for each year of the 5-year contract. The pattern of SSSSS in cells C21 to G21 means steady fluctuation is estimated for all 5 years. The patter of SSDDD in cells C21 to G21 means steady fluctuation is estimated for the first 2 years, and down for the last 3 years Calculations (rows 24 to 55): Calculate intermediate results of benefits (cash inflows) and costs (cash outflows) for NPV analysis. Number of U.S. employees (row 24): If the off-shoring agreement were not taken, this is the number of non-supervisory U.S. staff needed. There were 800 U.S. call centre employees in 2020, and it is expected to increase 2% per year that is 2% more than previous year) due to expected banking business growth. Note that the number of employees must be an integer Number of Indian employees (row 25): If the off-shoring agreement were taken, this is the number of non-supervisory Indian staff needed. Initially, assume there is a 1-to-1 replacement of U.S. employees in 2020, that is 800 Indian employees (B25 800). India's college graduates are expected to be more productive than US employees, thus the number would increase only 1% per year (that is 1% more than previous year) due to expected banking business growth. Note that the number of employees must be an integer Number of new U.S. employees (row 26: If the off-shoring agreement were not taken, this is the number of new U.S. employees. New US employees are hired for two reasons: expanding business growth (see row 24) and replacing employees who left in prior year (use the US employee turnover rate in row 10). Note that the number of employees must be an integer Indian employee turnover rate (row 27). If the Indian labour market is expected to be favourable during the life of the 5-year contract. employee turnover rate will be 109. per year . If it is expected to be not favourable, employee turnover rate will be 29% per year. ._ Number of new Indian employees (row 28): If the off-shoring agreement were taken, this is the number of new Indian employees New Indian employees are hired for the same reasons as new U.S. employees - see row 26, thus the logic of calculation is the same as for new U.S. employees, except that in 2021, all Indian employees are new employees Average Indian employment cost per employee (row 29: In 2020, the average Indian employment cost per employee is US$16,000. If the Indian labour market is expected to be favourable during the life of the 5-year contract, salary levels will increase 8% per year (that is 8% more than previous year). If it is expected to be not favourable. the increase will be 15% per year (that is 15% more than previous year) . Number of rupees per U.S. dollar (sow 30: The agreed currency exchange rate is fixed at USS1 dollar So INR$45.85 rupees (cell 030) when the 5-year contract is made in 2020, but currency exchange rate fluctuates each day. If the exchange rate outlook is steady for that year, the number of rupees per U.S. dollar is the same as prior year. If the outlook is up, the number of rupees per U.S. dollar will be 2% more than prior year. If the outlook is down, the number of rupees per U.S. dollar will be 2% less than prior year Indian employment cost in U.S. dollar (row 32): This is based on the number of Indian employees and the average Indian employment cost (in USS) per Cup cost (in USS) per employec. - Indian employment cost in rupees (row 33): This is the payment of Indian employment cost to BI contractor in rupees, using the agreed exchange rate of 45.85 rupees to US$1. Actual USS needed for Indian employment cost (row 34). This is the amount of dollars actually required to pay BI contractor as the Indian employment cost for that financial year Cost (in USS) of liaison employees living in the U.S. (row 35%. This is based on the number of liaison employees living in the US and the cost (in USS) of a liaison employee Cost (in USS) of liaison employees living in India (row 37): This is based on the number of linison employees living in India and the cost (in USS) of a liaison employee. Payment of Indian liaison employees in rupees (row 38). The liaison employees living in India will be paid by BS Bank in rupees calculated at the agreed exchange rate of 45.85 rupees to USSI. . Actual USS needed for Indian liaison payments (row 39): This is the number of dollars actually required to pay for the liaison employees living in India for that financial year. Cost of background checks in India (row 41): This is the cost (in USS) of background security checks performed by BI for new Indian employees in a year Payment of Indian background checks in rupees (row 42% This is the payment of Indian background checks to Bl contractor in rupees, using the agreed exchange rate of 45.85 rupees to USSI. Actual USS needed for Indian background checks (row 43). This is the number of dollars actually required to pay for the Indian background checks for that financial year. Cost of training new Indian employees in US dollar (row 45): This is based on the number of new Indian employees and the training cost (in USS) per Indian employee Payment of Indian training cost in rupees (row 46): This is the payment of Indian training cost to Bl contractor in rupees, at the agreed exchange rate of 45.85 rupees to USSI. . Actual USS needed for Indian training cost (row 47). This is the number of dollars actually required to pay for the Indian training cost for that financial year. Cost of background checks in the U.S. (row 48): This is the cost (in USS) of background security checks performed for new U.S. employees in a year .U.S. telecommunication costs (row 49): This is based on the number of U.S. call centre employees and the cost of U.S. telecommunication per call centre employee. .U.S.computer costs (row 50): This is based on the number of U.S. call centre employees and the cost of U.S. computer per call centre employee US real estate costs (row 51): This is based on the number of US. call centre employees and the cost of U.S. real estate per call centre employee .U.S. training costs (row 52): This is based on the number of new U.S. call centre employees and the cost to train a U.S. call centre employee ..Number of U.S. supervisors (row 53): This is based on the number of U.S. call centre employees and the ratio of U.S. call centre employees to supervisor. Note that the number of supervisors must be an integer Cost of U.S. supervisors (row 54): This is based on the number of U.S. supervisors and the average employment cost for a U.S. call centre supervisor Cost of U.S. employees (row 55): This is based on the number of U.S. call centre employees and the average employment cost for a U.S. employee. Net Present Value (NPV) calculation (rows 58 - 61): This is based on the net cash flows in the 5-year investments. The net present value of an investment is calculated by using a discount rate and a time series of payments /costs (negative values) and income/benefits (positive values). The net cash flow in a year is the difference between cash inflows (or investment benefits) and cash outflows (or costs). Formulae for rows 58 to 61 are given, their descriptions are given below; Cash inflows or benefits (row 58): These values are the total amounts that could be saved each year if the call centre is off-shored to India. Cash outflows or costs (row 59): These values are the total amounts that will be paid by BS Bank to Bl contractor each year if the call centre is off-shored to India. .Net cash flow (row 60): These values are the difference between cash inflows and cash outflows (that is, cash inflows less cash outflows) each year. Net present value (cell B61): Use Microsoft Excel's function - NPVO, to calculate the NPV at 25% discount rate (row 18) for the 5-year contract with BI if the call centre is off-shored to India. cell. S-year contract with Bl if the call centre is off-shored to India Table 1: "NA" - Not Applicable, meaning no entry is required in the 900 $ CONSTANTS &US TELECOM COSTS. PER EMPLOYEE USS N 3 V.S.OONPUTING COSTS. PER EMPLOYEES N FEX.ESTATE COST PERUS BIP.DYEUSS 2030 SE EMPLOYEE SUPER/SOR RATONUS 22 AVERAGE U.S. EMPLOYMENT COST PER EMPLOYEE USB HO 300 200 330 AVERAGE PONENT COST PER SUPERVISOR 50 500 5 US EMPLOYEE TURNOVER RATE M TRAINING COST PERUS EMPLOYEE USS 118 23 300 5 TRAINING COST PER INDIAN DIPLOYEE USS LETA AND TECHNICAL CONSULTING NDLUS 1920 x 700 TOTA, NUMBER OF LASON BPLOYEES 4 SOOST OF ONE SAISON BVP.OYEES COST OF BAYGROUND CHEXPEREMPLOYEE NDAN OOST OF BOXGROUND EXPEREMPLOSES 88 M2 2 5 DISCOUNT BATE FOR NPV 028 098 0303512 RISK FACTORS 2011 222 223 224 225 NDALABOR MARKET RSK FAVORABLE 23 N= NOT FAVORABLE ALL YEARS 21 EXCHANGE RATE U=UP S=57540Y, DEBO . BOLCULATIONS NUGER OF US BALDEES Cres Crise Day It Online 3 NUMBER OF ICH BIPLOEB Seas On Tree Onyiza lim ole BENUS EMPLOYEES A Linal Up Izz Online in intele NDAN BROETROBRATE MA Energy Confest Online Cialis EN NOANDIPLOYEES Alimentele Copy Ime Online BABAE NA BIP_MET COST PER DIPLOYEE 158 nare Opbretony oldal NUGER OFRPEES PER US DOLLAR 6 Cap Szere Op Sate ale NDAN BPOMENT DIST NOAK EMPLOYMENT COSTUS 4 lines Cap Sze Ceylan Ciale IOANEIPOMENT COST RUPEES lines Cepilzen Doyle Cimale SAATUALUS ESTED FOR ICH EIPLANET OST USO MA The Cp zee Online Caima lima * COST OF LASO DIPLOYEES US USA Aflmar Cop Szene Day I fee On iz bola * LOANLASEXBIPAREOST 3 COST CFLASO BP.016831 10488 The Copy Lines Day Ban Cliniz PAMENT FOR NOAVLASONBILDES RUPES Copy Lase Online olabiz SATA USI CESED FOR ACHA LABOMPANIES Ainars Cp lee Online Online NASAKOJO DEXOOST # COST OF BAOXGROUND CEDISH CHUSS Alinei Copilme online disable UPAMENTOS NOMBRESCRONO CHEOXS RUPEES Ma l'intera copil sarde Cayl met de la lola GADUAIS E PANCHAN BOXGROUOO EXS mare Oples Online izinle NATANGAS 2POS Olasz ABACE NOMBIPAMET COST PER DIPLOYEE USD 50 O marts Capellani 3 NUMBER OF RUPEES PERUS DOLAR & pe Cap Base Oplonial SA BPOMIENTOS NOMBIPONE COSTU WA Online On ima bible #KOMBIPONENT DIST RUPEES NA I Opis Opinizinle KETAUS EDED FOR ICH BIPLOMOTOOST 89 M Erate Opere deplase loialis *CS OF USO BIPOVEST USUS MA The Complex Colt talalla * ROANLIKSONBPOVES OOSI 900STOFUASO BPDVBS 110488 MA in Copy free Onlar bizle PANENT FOR NOAUASONBILDES RUFE mert Opi pare Op base de la la ARA USTEDET FOR ICHKLASOWANIES NA Intel Coplen Online tolila 4 NABOKONG DEX OOST limmern Cape reste plane Cose dala PAMENT OF IOAN BACKGROUND CHESS RUPEES A filmer Day Spas Online iniziale ATALUSI TEELEO PORNCANBOXCRODODENS A Press Complete ale tielle 1981 1983-O ONOLIKO SEK 1800 11 -BEN . 4 NDAV TRANG OOST NA & COST OF TRANING INOAN WORKERS LS timat Canaras Gange past Caritas Sayira & PAYMENT OF NOAN TRANING RUPEES Na filmat Operater Dany pest Omica iz O ACTUAL USI KETED FOR NDAN TANING IM Brais Cplate Cry 3 pase Opiz bola COST OF BAONGROINOO-EXS INUS 18 Almaty Oupy & paste Copy Lee Onize lolz GUS TELECOM COSTS Ma Ninang Top 8 mese Dry Spese Opla lale 3 US COMPUTBOOSTS M final Copy Aras Cany dras Cenise Caries SIUS REAL ESTATE COSTS NA Mimary Cap I see any page Opinie 2 US. TRANING COSTS MA in Copy I see Ceny best op iz le 5 NUVEER OF US SUPERVISORS NA Nments Op zate Copy Lene Opiz Dale SLUS.S.PER/SORY COSTS NA fy many Copy & pese Copy Spase Opleie 5 COST OF US BIPLOSES Na pomaly Couple paste Copy Lee Online Oylz sc la toitlarnis EN ALS 22 9 TUK BERTS CONS 100STE CASO 80 Besos BPN OF OF SONG NESTEN SPIEG 1 55 Dla la la la BS BANK'S CALL CENTER OFF-SHORING DECISION 2021 CONSTANTS 2020 U.S. TELECOMM COSTS, PER EMPLOYEE (US$) NA U.S. COMPUTING COSTS, PER EMPLOYEE (USS) NA REAL ESTATE COST, PER US EMPLOYEE NA EMPLOYEE to SUPERVISOR RATIO in U.S. NA AVERAGE US EMPLOYMENT COST PER NA AVERAGE EMPLOYMENT COST PER SUPI NA U.S. EMPLOYEE TURNOVER RATE NA TRAINING COST PER US EMPLOYEE (USS) NA TRAJNING COST PER INDIAN EMPLOYEE (US$) NA LEGAL AND TECHNICAL CONSULTING in INDIA (US$) 15000000 TOTAL NUMBER OF LIAISON EMPLOYEES NA COST OF ONE LIAISON EMPLOYEE (USS) NA COST OF BACKGROUND CHECK PER EMI NA COST OF BACKGROUND CHECK PER EM NA DISCOUNT RATE FOR NPV 0.25 RISK FACTORS 2020 INDIA LABOR MARKET RISK: F- FAVORABLE N NNOT FAVORABLE (ALL 5 YEARS) EXCHANGE RATE: U=UP S STEADY, D NA = DOWN 95 390 1100 20 30000 52000 0.11 1100 1600 6000000 4 200000 1000 20 0.25 2022 90 380 1200 20 31000 53000 0.12 1200 1700 6500000 4 210000 1100 25 2023 88 370 1300 20 32000 54000 0.13 1300 1800 7000000 4 220000 1200 30 0.25 2023 2024 80 360 1400 20 33000 55000 0.14 1400 1900 7500000 4 230000 1300 35 0.25 2024 2025 75 350 1500 20 34000 56000 0.15 1500 2000 8000000 4 240000 1400 40 0.25 2025 0.25 2021 2022 NA NA NA NA NA U U U U U 2021 2022 2023 2024 2025 Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste 12 marks] Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste -- CALCULATIONS 2020 NUMBER OF U.S. EMPLOYEES 800 1.5 marks] NUMBER OF INDIAN EMPLOYEES 800 11.5 marks NEW U.S. EMPLOYEES NA [2.5 marks) INDIAN EMPLOYEE TURNOVER RATE NA [2 marks) NEW INDIAN EMPLOYEES NA [1 mark] AVERAGE INDIAN EMPLOYMENT COST PER EMPLOYEE (US84000 3 marks] NUMBER OF RUPEES PER US DOLLAR 45.650 [3 marks] INDIAN EMPLOYMENT COST NA INDIAN EMPLOYMENT COST (USS) NA [1 mark] INDIAN EMPLOYMENT COST (RUPEES) NA [1 mark] ACTUAL USS NEEDED FOR INDIAN EMPLC NA [2 marks] COST OF LIAISON EMPLOYEES in U.S. (USS) NA [1 mark] INDIAN LIAISON EMPLOYEE COST: NA COST OF LIAISON EMPLOYEES in INDIA (US$) NA 11 mark] PAYMENT FOR INDIAN LIAISON EMPLOYE NA [1 mark] AC US$ NEEDED FOR INDIAN LIAISC NA [2 marks] INDIAN BACKGROUND CHECK COST NA COST OF BACKGROUND CHECKS in INDIA (US$) NA [1 mark] PAYMENT OF INDIAN BACKGROUND CHE NA 11 mark] ACTUAL USS NEEDED FOR INDIAN BACK NA [2 marks] INDIAN TRAINING COST NA COST OF TRAINING INDIAN WORKERS (USS) NA 11 mark) PAYMENT OF INDIAN TRAINING (RUPEES) NA [1 mark] ACTUAL USS NEEDED FOR INDIAN TRAINING NA [2 marks] COST OF BACKGROUND CHECKS IN U.S. (USS) NA 11 mark] U.S. TELECOMM COSTS NA [1 mark] U.S. COMPUTER COSTS NA [1 mark) U.S. REAL ESTATE COSTS NA (1 mark] U.S. TRAINING COSTS NA [1 mark] NUMBER OF U.S. SUPERVISORS NA [1.5 marks! U.S. SUPERVISORY COSTS NA [1 mark) COST OF U.S. EMPLOYEES NA [1 mark] Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & Paste Copy & paste Copy & paste Copy & pasto Copy & pasto Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & pasto Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste 2021 NET CASH FLOW ANALYSIS TOTAL BENEFITS (CASH INFLOWS TOTAL COSTS (CASH OUTFLOWS): Net cash flow (BENEFITS - COSTS) NPV OF OFF-SHORING INVESTMENT 2020 0 15000000 - 15000000 2022 2023 2024 2025 Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste Copy & paste NA NA NA NA NA