Answered step by step
Verified Expert Solution
Question
1 Approved Answer
THIS HAS TWO PARTS Your company is thinking of expanding its ice - cream sales into a new region. You were tasked with figuring out
THIS HAS TWO PARTS
Your company is thinking of expanding its icecream 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 icecream within a matter of a month or so Sales are expected to generate revenues. The company is most likely to sell a unit of icecream for $ The annual demand for icecream equals units. Besides revenues, there are also costs involved. There is a fixed cost of $ 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 icecream is around $ Lastly, once the firm decides to start selling in region x it would have to remain in the market for years. After 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
Read OUTPUT description below BEFORE you start working on Part A
Before making a recommendation to the firm, you feel the need to carry out some additional analysis in order to develop greater confidence in your recommendation. Specifically, you recognize that changes in the inputs and assumptions could impact the project outcomes as well as your recommendation. Therefore, you decide to explore the range of possible profit levels you may observe in practice by conducting sensitivity analysis. The inputs and assumptions of concern are:
The fixed cost is expected to be $ but it could actually vary about up or down.
The discount rate is expected to be but it could actually be between and
The most likely unit price is $ but the firm has some flexibility to set the price anywhere between $ and $
If the unit price is set higher or lower than $ managers estimate crudely the following number of units sold ie demand for five possible unit prices:
Unit Price No Units Sold
$
$
$
$
$
Part A
NOTE: Incorporate VLOOKUP function to make demand sensitive to price.
What is the profit under each of the following scenarios:
Scenario best case: discount rate is fixed cost $ and unit cost is $
Scenario worst case: discount rate fixed cost is $ and unit cost is $
Scenario most likely: discount rate is fixed cost $ and unit cost is $
Write your answers for the THREE scenarios. For all other variables, use expected values for example, price $
Before you do below, name the cells so their names appear in the summary report.
Use Excels SCENARIO MANAGER to set the three above scenarios and generate a summary report showing the scenario assumptions and the profit.
Assuming scenario use a onevariable DATA TABLE to show how profit varies as the discount rate varies between and in increments of
Assuming scenario use a twovariable DATA TABLE to show how profit varies as the discount rate varies from to in increments of and the unit price varies from $ to $ in increments of cents.
Create a Spin interactive control button that allows you to change the discount rate between and in increments of ; remember the trick used to allow this interactive button to vary in increments smaller than
Create a Scroll interactive control button that allows you to change the unit price between $ and $ in increments of cent; remember the trick used to allow this interactive button to vary in increments smaller than
Assuming a discount rate of use the data generated for question above to create a D graph showing how profit changes as a function of unit price.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started