Reference no: EM133654584
Homework
Your company is thinking of expanding its ice-cream sales into a new region. You were tasked with figuring out whether this would be a profitable step based on the following information.
Once a decision is taken to enter region "x", the company would be able to start selling ice-cream within a matter of a month or so. Sales are expected to generate revenues. The company is mostlikely to sell a unit of ice-cream for $3.50. The annual demand for ice-cream equals 65,000 units. Besides revenues, there are also costs involved. Thereis a fixed cost of $20,000per year for running the sales and production sites in region "x". In addition, there is a variable cost - the cost of producing one unit of ice-cream is around $3.00. Lastly, once the firm decides to start selling in region "x", it would have to remain in the market for 3 years. After 3 years, it could decide whether to stop selling in region "x". To calculate profitability, it is necessary to compute the cash flows in each year and discount them by an annual discount rate of 8%.
Read OUTPUT description below BEFORE you start working on Part I.
Part I
A. Draw an influence diagram for the decision problem described above.
B. Develop a variable definition table for the decision problem.
Part II
By now you have developed a better conceptual understanding of the core issue underlying the business problem you are trying to address, namely: compute the profit the company should expect from starting to sell ice-cream in region "x". You have also defined the variables involved, identified the inputs to the problem, and may have made assumptions about issues not covered in the initial problem description. You should be ready to start building an Excel model for the problem.
A. Build a spreadsheet model that computes the cash flows and profit of the initiative. In the worksheet, show the calculated profit and the values of intermediate variables based on which profit is calculated.
B. Based on the profit you computed, would you recommend starting to sell in region "x"? Offer a brief but precise answer.
Part III
A. COPY ORIGINAL DATA FROM Part II ABOVE IN a new worksheet called Part III
Find what discount rate would yield a grand (total) discounted profit of $15,555.
B. COPY ORIGINAL DATA FROM Part II ABOVE IN THE SPACE BELOW (#1)
Find what unit price would yield a grand (total) discounted profit of $555,000.