Question

J&G Bank receives a large number of credit-card applications each month, an average of 30,000 with a standard deviation of 4,000, normally distributed. Approximately 60% of them are approved, but this typically varies between 50% and 70%. Each customer charges a total of $2,000, normally distributed, with a standard deviation of $250, to his or her credit card eaclh month. Approximately 85% pay off their balances in full, and the remaining incur finance charges The average finance charge has recently varied from 3% to 4% per month. The bank also receives income from fees charged for late payments and annual fees associated with the credit cards. This is a percentage of total monthly charges and has varied between 6.8% and 7.2%. It costs the bank $20 per application, whether it is approved or not. The monthly maintenance cost for credit-card customers is normally distributed with a mean of $10 and standard deviation of $1.50. Finally, losses due to charge-offs of customers' accounts range between 4.6% and 5.4% of total charges a. Using average values for all uncertain inputs, develop a spreadsheet model to calculate the bank's total monthly profit b. Use Monte Carlo simulation to analyze the profitability of the credit card product. Use any of the Analytic Solver Platform tools as appropriate to fully analyze your results and provide a complete and useful report to the manager of the credit card division

Answer 1

Book Microsoft Exce HomeInsertPage LayoutFormulas Data Review View s Cut Σ AutoSum, Copy Format Painter в 1 u 기 o»-Δ. $ . % ,Miog C Conditional Format Cel Insert Delete Format Formatting as Table Styles2 ClearFilter Select E Ξミ泹.론 섭 Merge & Center, Sort & Find & Number Cells Editing D23 J & G Bank Model Data No. of credit card applications Percentage of a Customer cha Cost per application to bank 30000 Use following Commands for this 2000 Model 12 13 No.of a Percentage of full balance Number of full balance Average finance charge factor Average finance charge Number of customersi Other Fees Monthly maintenace cost Losses due to c Monthly char Other fee charge full balance 18000 85% 15300 0.035 application С4-С5 С11-С12 15 16 17 C6 C14 C11-C13 2700 0.07 e offs factor r full balance 0.05 2010 140.7 21 C18-C6 C20 C17 23

Total amount charged to full balance Amount earned from full balance Monthly cha Other fees charge non full balance Total amount charged to non-full balance Amount earned from non-full balance Total earnings of bank Total a Losses due to charge offs Total expense of bank Revenue of bank 2150.7 32905710 2080 145.6 2225.6 6009120 38914830 C20+C21 C22 C13 C6+C15+C18 C24 C17 C24+C25 C26 C16 C23+C27 C7-C4 C28 C19 C29+C30 C28-C31 25 26 27 r non full balance 29 31 32 lication cost to bank 1945741.5 545741.5 36369088.5 34 35 36 The profit the bank makes from this credit card product is $36369088.5

b)

Make a similar model on another worksheet however at this point enter the conveyances of different unsure factors.

Snap on gem ball in the menu bar and select the "No. of uses" cell. Snap on characterize presumptions and pick ordinary circulation and enter the mean and standard deviation esteems.

Correspondingly enter different suspicions. For those qualities which have been given as a range, pick uniform conveyance while characterizing them as suppositions.

Select the "Income of bank" cell and snap on characterize estimate leave the default name as is and click Ok.

To check which dubious factors have any effect on the income, use Tornado outline choice.

Snap on more apparatuses and select Tornado investigation. Pick the objective estimate as the income of the bank cell click straightaway.

In the next window, click on add assumptions. All assumptions are then listed. Click next.

In the last window click Run. The Tornado chart and spider chart is as shown below.

Revenue of bank $25,000,000.0830,000,000.0835,000,000.0840,000,000.0845,000,000.0850,000,000.00 24,873.79 $1,679.61 35,126.21 $2,320.39 52% 68% 5.32% 4.68% 6.84% i 7.16% 3.10% í 3.90% 3.50% 7.00% 5.00% 30,000.00 60% $2,000.00 Upside Downside

Revenue of bank $45,000,000.00 $40,000,000.00 $35,000,000.00 30,000,000.00 10.00% 30.00% 50.00% 70.00% 90.00%

From the tornado examination it very well may be seen that the uniform circulation questionable parameters that is the "Other expense rate", "Client charge", "No. of Visa applications" and "Normal fund charge factor" have no impact on the income of the bank. To crosscheck this, run the recreation and after that remove the affectability diagram.

Press begin in the run area and the accompanying circulation is gotten in the wake of running 10000 preliminaries.

0.000 Ts Revenue of bank 0.04 3,5580,000.00 $3.63 꾜.319 92 3.58 67 806 02 tenderd Devieion 003 $1.41 꾜161 27 220 Mean Sd Error 0.02 001 2,0000,000.00 53,00,00 000 00 $4 00,000.00 55,0000.000.00 Certairty: 100.00