Assignment 4
Due: Wednesday March 6
At 11pm (evening)
All assignments must be completed individually
THIS ASSIGNMENT HAS THREE PARTS
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 most likely 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. There is a fixed cost of $20,000 per 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 A.
Part A
(1) Draw an influence diagram for the decision problem described above.
(2) Develop a variable definition table for the decision problem.
Part B
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.
1. 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.
1. Based on the profit you computed, would you recommend starting to sell in region “x”? Offer a brief but precise answer.
Part C
(1) COPY ORIGINAL DATA FROM Part B ABOVE IN a new worksheet called Part C
Find what discount rate would yield a grand (total) discounted profit of $15,555.
(2) COPY ORIGINAL DATA FROM Part B ABOVE IN THE SPACE BELOW (#1)
Find what unit price would yield a grand (total) discounted profit of $555,000.
| OUTPUT: Excel file should have THREE sheets; one each for Part A, Part B, and Part C Part A: Write your answer on paper (with pencil/pen) or in the Excel file itself. If you do on paper, then take a picture with cell and PASTE it into “Part A” sheet of the Excel file. You should submit ONLY ONE Excel file (that has three worksheets). |
IMPORTANT
SUBMIT answers on Blackboard>Assignments>Assignment 4.
NOTE: For each 24 hours the assignment is late, there will be a 10 point deduction.
Prof. Padmal Vitharana, BUA 345