Monte Carlo Accounting Assingment help With Solution

Monte Carlo Accounting Assingment help With Solution

 
QUESTIONS – Monte Carlo Simulation: Sanjay’s Restaurant

After years of working in management consulting, Fuqua alum Sanjay wants to return to Durham and open a gourmet Indian restaurant. Cooking has always been Sanjay’s passion and he believes the growing Durham (and Research Triangle) market would truly appreciate fine Indian cuisine. The extensive travel associated with Sanjay’s consulting job has given him the opportunity to study gourmet Indian restaurants in many U.S. cities and, whenever possible, he talked to the managers to better understand the profitability of their operations.
Sanjay has developed a pro-­-forma monthly cash flow analysis.
 
Exhibit 1: Pro forma Cash Flow Analysis

Monthly Cash Flows

Unfortunately, just about every number in this analysis is uncertain. Sanjay wants to build a Monte Carlo simulation model to better understand the risks of this venture.
From his conversations with restaurant owners in other cities, Sanjay estimates probability distributions for the variables as follows:

• Number of customers per month: This is very hard to predict. Sanjay is optimistic about the demand but recognizes the uncertainty and assigns a lognormal distribution with mean 2,400 and standard deviation 800 (and “location” parameter equal to zero).
• Average sales per customer: Sanjay’s prices will be set according to his estimate of what customers will pay. At this point, he is uncertain about the average sales per customer and assigns a normal distribution with a mean of $25.00 per customer and a standard deviation of $4.00.
• Direct cost of sales: This includes the costs of food served to customers as well as other incidental expenses (e.g., laundry). Sanjay assigns a normal distribution with a mean of
  $7.00 per customer with a standard deviation of $1.00.
• Payroll costs. Sanjay assigns a triangular distribution with a most likely value of $24,000 per month and a minimum value of $19,000 and a maximum of $32,000.
• Other fixed costs: Sanjay has identified a location in Durham and estimates that rent, utilities, and insurance for the location will cost about $8,000 dollars per month. He is quite confident about this and does not feel the need to model it as uncertain.

Some of these uncertainties are correlated. Specifically:

• Food with more expensive ingredients will typically be sold at higher prices. Sanjay estimates that the average sales per customer and direct costs of sales per customer are positively correlated with a correlation of 0.6.
• The payroll costs will tend to vary with the number of customers: if you have more customers, you need more staff members to serve them. Sanjay estimates that the number of customers and payroll expenses are positively correlated with a correlation of 0.4.

Build a simulation model of this venture to answer the following questions. Please run at least 10,000 trials for your final reported results. (Recall that Crystal Ball sometimes runs extremely slowly when in Extreme Speed. If this happens, run your model in Normal Speed instead.)

Important: Provide appropriate supporting Crystal Ball charts/statistics next to your answer to each question.

1. Build a deterministic model (without probability distributions) using the values shown in the pro forma cash flow statement but separate the inputs from the calculations. Make a copy of your deterministic model and, for this question only, change the number of customers from 2400 to 2500 and the average sales from $25 to $28. What is the profit?

Proceed to add probabilistic details in the copy of your deterministic model with the original value of 2400 customers and $25 average sales.

2. Define Crystal Ball assumptions as described but do NOT define the correlations.
3. What is the average profit per month?
4. What are the 10th and 90th percentiles of profit per month?
5. Add the correlations to your Crystal Ball assumptions.
6. What is the average profit per month?
7. What are the 10th and 90th percentiles of profit per month?
8. What is the probability that Sanjay’s profit exceeds $10,000 per month with this venture?
9. Compare the statistics and risk profiles (i.e., probability distributions on profits) for the three locations. Prepare an overlay chart containing the profit distribution for each location.
10. What is the mean profit for each location?
11. Which outcome has the least profit variability?
12. Can you rule out any of the locations at this time? Explain by referring to the Cumulative Frequency overlay chart view.
13. The most important assumption in Sanjay’s model is the assumption about the mean number of potential customers: he has assumed that the number of potential customers is lognormally distributed with a mean of 2,400 and a standard deviation of 800. Assuming Sanjay is risk neutral, we want to understand how the choice of location for the restaurant (between locations A, B, and C above) changes if we vary the mean number of potential customers away from the assumed value of 2,400. You may assume that the standard deviation and correlations remain the same as this mean changes.
14. Construct a data table and corresponding chart showing how the expected value of profit for these three alternatives change as we vary the mean for the number of potential customers between 1,000 and 5,000 customers per month (with increments of 250 customers).
15. What is the preferred location and average profit for mean demand of 2,000 customers? 3,500 customers? 4,500 customers?
16. Identify the customer levels in your data table at which the preferred location changes (if it changes) between a mean number of potential customers of 1,000 and 5,000. (Note: You do not need to interpolate to find exact transition points. Report the number of customers in your data table for each transition to a new location with the highest average profit.)

QUESTIONS

1. Build a base model without Solver that contains the inputs provided, a decision grid about how many planes to assign from each city to each city, and a calculation section that computes total repositioning distance and total repositioning cost. Consider the following assignment plan:

Reposition 2  Amsterdam planes for 2 Dublin customers Reposition 1 Barcelona plane for 1 Helsinki customer Use 1 Copenhagen plane for 1 Copenhagen customer Reposition 1 Copenhagen plane for 1 Liverpool customer Reposition 1 Zurich plane for 1 Lisbon customer Reposition 1 Zurich plane for 1 Venice customer Reposition 1 Zurich plane for 1 Vienna customer
 

1. What is the total repositioning distance?
   b. What is the total repositioning cost?
   c. What planes are unused?
2. Make a copy of your base model and extend it to formulate and solve a linear optimization problem to determine the most efficient way to assign planes to customers: that is, find the plan that minimizes the total repositioning costs. Note: You do not need to include integer or binary constraints in your formulation of this problem. You can ignore any minor discrepancies (e.g., less than one millionth) from integer values.
3. What is the assignment plan (listed by quantity and location as in Question 1)?
   b. What is the total repositioning distance for this plan?
   c. What is the total repositioning cost for this plan?
4. Is the optimal solution you obtained in Question 2 unique? Please provide evidence for your answer from the Sensitivity Report created from your Question 2 solution model.
5. Make a copy of your Question 2 optimization model. In this copy, change the distance between Amsterdam and Liverpool from 423 km to 425 km. Make the same change to the distance between Liverpool and Amsterdam. Use the adjusted distance for all remaining questions. Solve the optimization model with the adjusted distance. What is the total repositioning cost for this plan?
6. Sensitivity analysis based on the Question 4 optimization model:
7. In the Sensitivity Report created from your Question 4 solution model, list the objective coefficient value for the number of planes assigned from Amsterdam to Barcelona and from Dublin to Venice. Please explain how each value relates to the problem statement inputs.
8. Is the optimal solution you obtained in Question 4 unique? Please provide evidence for your answer.
9. How would the total repositioning costs change if we added another customer in Vienna? Please explain in a sentence or two how your solution value relates to the problem statement inputs; that is, please interpret this change in repositioning costs in terms of the underlying problem (don’t just say “Solver says” or “the sensitivity report says”).
10. How would the total repositioning costs change if there were an additional plane available in Amsterdam? Please explain in a sentence or two how this number relates to the problem statement inputs; that is, please interpret this change in repositioning costs in terms of the underlying problem (don’t just say “Solver says” or “the sensitivity report says”).
11. Now suppose that in addition to using its own aircraft, DayJets can rent planes from other firms (charter firms or other fleet operators) to meet customer requests. This can reduce repositioning costs, but DayJets must pay the plane owner for use of the plane (and crew). The rented planes can then be repositioned to meet customer requests.

Planes are available for rental from the following four cities at the indicated fees:

Amsterdam: €3000
Copenhagen: €3000
London: €4000
Venice: €2500
 
These rental fees do not include the costs of repositioning the aircraft. You may assume the cost of repositioning the rented aircraft are the same as for DayJets’ planes. You should also assume that there is exactly one plane available for rent at each of these locations.
 
Formulate and solve a new optimization problem that takes into account the possibility of renting planes.

1. Does your solution rent any planes? Is so, which ones?
   b. What is the assignment plan (listed by quantity and location as in Question 1)?
   c. How does this assignment plan differ from your solution to Question 4?
   d. What is the total cost of this plan?

Product Code :Acc68

To get answer for this question, kindly click here (Note: Don’t forget to write the product code in comment section)

You can also email us at assignmentconsultancy.help@gmail.com but please mentioned product code in the mail body while sending emails.You can browse more questions to get answer in our Q&A sections here.