Question
Arbbot Food, Inc., is considering an investment in a new organic juice in response to the growing demand for healthy drink. Initial investment is $450K
Arbbot Food, Inc., is considering an investment in a new organic juice in response to the growing demand for healthy drink.
Initial investment is $450K PPE with a life of 5 years. Straight-line depreciation with zero salvage value.
21% tax rate.
WACC for the project is 13.25%.
The initial investment should be invested at the end of 2019 and the first year of revenues will be realized at the end of 2020.
Arbbot Food, Inc. is financed from internally generated cash flows; project is all-equity-financed.
The expected price for the juice is $5 per bottle and the company expects to sell 200,000 bottles in 2020. The number of bottles sold is expected to grow 10% annually throughout the 5-year planning period.
COGS margin 67.4% (COGS margin=Cost of goods sold/sales).
Operating expenses before depreciation are 10% of sales plus an annual fixed cost of $115K.
1. Please use the above information and replace X in the Excel file with a correct accounting value. (Hint: Please try to compute all numbers by using Excel formulas).
2. Please use Excel functions to compute NPV and IRR for this project. Based on your results, is this a good project?
3. Please use Excel functions to do the sensitivity analysis of
a. expected sales quantity in base year (2020) on NPV. Please use 80%, 90%, 100%, 110% and 120% of the expected 200,000 bottles. Please also provide a chart as in slide 11.16.
b. expected sales quantity growth rate on NPV. Please use 80%, 90%, 100%, 110% and 120% of the expected 10% growth rate. Please also provide a chart as in slide 11.16.
c. (Hint: You can use Data Table tool)
4. Please use Excel functions to find the financial, cash and accounting breakeven salesquantities in base year (2020).
a. (Hint: Please use Goal Seek tool)
AA. TU x $. % G21 x 674 5 Projecte de preparatie (5) 6 posted b y a gumity (000) 7 Hiperted Growth Rete in sale unity COGS (COGS Sales) 9 Variable Operating fee de 10 Fixed Opening Coats (5000) 1. Initial investment in plante med a t / 12 Depreciation Med 13 Depreciable life of plant and equipment Sales 3 15 Project WAOC 13.15% FCF Calaton (5000 22 Las Cost of goods sold 23 Equals: Cross profit Less Operating espois before depreciation 25 Equals: BITDA 26 Less Depreciation and more 27 Equals: HIT LUXXXXXXX XXXXXX BREXHEMERE 29 Equals: NI Model Sensitivity analysis Cash Bay 47 AuHaving HTC qua v * Th e quantity MacBook Pro Called T . . A A t P er S . % B . Due . . ) 623 X 6 MacBook Pro AA. TU x $. % G21 x 674 5 Projecte de preparatie (5) 6 posted b y a gumity (000) 7 Hiperted Growth Rete in sale unity COGS (COGS Sales) 9 Variable Operating fee de 10 Fixed Opening Coats (5000) 1. Initial investment in plante med a t / 12 Depreciation Med 13 Depreciable life of plant and equipment Sales 3 15 Project WAOC 13.15% FCF Calaton (5000 22 Las Cost of goods sold 23 Equals: Cross profit Less Operating espois before depreciation 25 Equals: BITDA 26 Less Depreciation and more 27 Equals: HIT LUXXXXXXX XXXXXX BREXHEMERE 29 Equals: NI Model Sensitivity analysis Cash Bay 47 AuHaving HTC qua v * Th e quantity MacBook Pro Called T . . A A t P er S . % B . Due . . ) 623 X 6 MacBook Pro
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