# [Solution]Simulation and Risk Analysis

Instructions: Please submit your work in one single Excel file with one tab/worksheet for each problem. 1. (50 points) In class, we developed a simple…

Instructions: Please submit your work in one single Excel file with one tab/worksheet for each problem.
1. (50 points) In class, we developed a simple spreadsheet model for computing profit in Excel. Use this profit model to implement a financial simulation model for a new product proposal and determine a distribution of profits using the discrete distribution below for the demand, unit cost, and fixed cost. Price is fixed at \$1,000. Demand is unknown and follow the distribution:

Demand
Probability

120
0.25

140
0.50

160
0.25

Unit costs are also variable and follow the following distribution:

Unit Cost
Probability

\$400
0.20

\$600
0.40

\$700
0.25

\$800
0.15

Fixed costs are estimated to follow the distribution:

Fixed Costs
Probability

\$45,000
0.20

\$50,000
0.50

\$55,000
0.30

Simulate this model for 50 trials and a production quantity of 140. What is the average profit?

2. (50 points) 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 each month. Approximately 85% pay off their balances in full, and the remaining incur finance charges. The average finance charge has recently been ranged 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 totally 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 are between 4.6% and 5.4% of total charges. Use Monte Carlo simulation with 500 trials to analyze the profitability of the credit card product.

Assignment status: Solved by our experts

>>>Click here to get this paper written at the best price. 100% Custom, 0% plagiarism.<<<