Answered step by step
Verified Expert Solution
Link Copied!

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 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.
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 $20,000, but it could actually vary about 10% up or down.
The discount rate is expected to be 8%, but it could actually be between 6% and 10%
The most likely unit price is $3.50, but the firm has some flexibility to set the price anywhere between $3.30 and $3.70
If the unit price is set higher or lower than $3.50, managers estimate crudely the following number of units sold (i.e., demand) for five possible unit prices:
Unit Price No. Units Sold
$3.3082,000
$3.4071,000
$3.5065,000
$3.6056,000
$3.7041,000
Part A
NOTE: Incorporate VLOOKUP function to make demand sensitive to price.
(1) What is the profit under each of the following scenarios:
Scenario 1(best case): discount rate is 6%, fixed cost $18,000, and unit cost is $2.80
Scenario 2(worst case): discount rate 10%, fixed cost is $22,000, and unit cost is $3.20
Scenario 3(most likely): discount rate is 8%, fixed cost $20,000, and unit cost is $3.00
Write your answers for the THREE scenarios. For all other variables, use expected values (for example, price = $3.50).
Before you do (2) below, name the cells so their names appear in the summary report.
(2) Use Excels SCENARIO MANAGER to set the three above scenarios and generate a summary report showing the scenario assumptions and the profit.
(3) Assuming scenario (3), use a one-variable DATA TABLE to show how profit varies as the discount rate varies between 6% and 10% in increments of 1%.
(4) Assuming scenario (3), use a two-variable DATA TABLE to show how profit varies as the discount rate varies from 6% to 10% in increments of 1% and the unit price varies from $3.30 to $3.70 in increments of 10 cents.
(5) Create a Spin interactive control button that allows you to change the discount rate between 5% and 14% in increments of 1%; remember the trick used to allow this interactive button to vary in increments smaller than 1.
(6) Create a Scroll interactive control button that allows you to change the unit price between $3.30 and $3.70 in increments of 1 cent; remember the trick used to allow this interactive button to vary in increments smaller than 1.
(7) Assuming a discount rate of 8%, use the data generated for question (4) above to create a 2-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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Startup CFO The Finance Handbook For Your Growing Business

Authors: Kyle Brennan

1st Edition

1790959403, 978-1790959402

More Books

Students also viewed these Finance questions

Question

Discuss the various types of policies ?

Answered: 1 week ago

Question

Briefly explain the various types of leadership ?

Answered: 1 week ago

Question

Explain the need for and importance of co-ordination?

Answered: 1 week ago

Question

Explain the contribution of Peter F. Drucker to Management .

Answered: 1 week ago