Hola-Kola HW Case Team: _____________________

Perform a capital budgeting analysis of Hola-Cola’s new project under each of the following
scenarios:

This model has been prepared for your review based on the text in the case. However, you
suspect that the preparer of the model was not well-trained. Thus you must check for errors in
their approach (there are several!). This case will test your understanding of incremental CF.
Which revenues, expenses and investments should be considered in the decision? Which are not
incremental and should be excluded? It will also review your understanding of calculating Free
Cash Flow correctly. Be sure to carefully read the case description and be able to discuss the
strategic considerations of the investment opportunity. Finally, make a go or no go decision.

Scenario 1:

Using the data provided in case (after your adjustments to incorporate your knowledge of
incremental CF), what is the NPV of the project?
NPV:

Scenario 2: (right click and copy your adjusted Scenario 1 to a new tab, alter the growth assumptions)

You are concerned that the energy costs, labor costs, and material costs will increase by 5
percent per year, starting in year 2 (included). You are unsure whether you will be able to
adjust the shelf price per unit correspondingly. What is the NPV under the assumption that costs
increase but you cannot increase prices?
NPV:
Scenario 3: (right click and copy your adjusted Scenario 1 to a new tab, alter the growth assumptions)

You are concerned that the energy costs, labor costs, and material costs will increase by 5
percent per year, starting in year 2 (included). Alternatively, if you believe that you will be able
to increase the price per unit by 5 percent per year, starting in year 2, with the consequence
being to decrease sales volumes by 2 percent a year, starting in year 2. What is the NPV under
these new assumptions?
NPV:

Deliverables:

Prepare the provided slides to discuss your recommendation (ie Go or No Go) to the Board of
1) Directors. You may include additional slides. Address the cash flow elements from the slides to
explain how you adjusted your model. Consider, how should the board incorporate the multiple
scenarios? Note: The company does NOT get to choose which scenario will happen!

2) Submit your adjusted Excel model.

Pro Forma Managerial Balance Sheet (thousands of pesos) MBS Assumptions

0 1 2 3 4 5 0 1 2 3 4 5
Cash Cash/Sales No Assumptions provided so no forecast given
Accounts receivable $4,438,356 $4,438,356 $4,438,356 $4,438,356 $0 AR/(Sales/365): Collect 45 45 45 45 - You may make the simplifying assumption that receivables will be collected quickly in year 6 (at maximum the accounts will be collected by mid February).
Inventory $1,080,000 $1,080,000 $1,080,000 $1,080,000 $1,080,000 $0 Months of Materials 1 1 1 1 1 -
-Accounts payable ($1,278,247) ($1,278,247) ($1,278,247) ($1,278,247) $0 AP/(Materials/365): Pay 36 36 36 36 -
Total WCR $1,080,000 $4,240,110 $4,240,110 $4,240,110 $4,240,110 $0
Gross PP&E $50,000,000 $50,000,000 $50,000,000 $50,000,000 $50,000,000 $50,000,000
Review how the modeler used the cap ex plan and
Accumulated depreciation -$10,000,000 -$20,000,000 -$30,000,000 -$40,000,000 -$50,000,000 annual depr to forecast.

Net PP&E $50,000,000 $40,000,000 $30,000,000 $20,000,000 $10,000,000 $0

Total Capital Invested $51,080,000 $44,240,110 $34,240,110 $24,240,110 $14,240,110 $0

Debt $10,216,000 $8,848,022 $6,848,022 $4,848,022 $2,848,022 $0 Debt 20% 20% 20% 20% 20% 20%
Equity $40,864,000 $35,392,088 $27,392,088 $19,392,088 $11,392,088 $0 Equity 80% 80% 80% 80% 80% 80%
Total Employed Capital $51,080,000 $44,240,110 $34,240,110 $24,240,110 $14,240,110 $0

Pro forma Income Statements (thousands of pesos) I/S Assumptions

0 1 2 3 4 5 0 1 2 3 4 5

Total Sales $36,000,000 $36,000,000 $36,000,000 $36,000,000 $36,000,000

Sales of Low Cal Product $36,000,000 $36,000,000 $36,000,000 $36,000,000 $36,000,000 0 1 2 3 4 5
Units Sales new units 7,200,000 7,200,000 7,200,000 7,200,000 7,200,000 Growth 0.0% 0.0% 0.0% 0.0%
Price Sales price (pesos) $5.00 $5.00 $5.00 $5.00 $5.00 Growth 0.0% 0.0% 0.0% 0.0%
Cost of goods sold $15,720,000 $15,720,000 $15,720,000 $15,720,000 $15,720,000
Materials Cost $12,960,000 $12,960,000 $12,960,000 $12,960,000 $12,960,000 Materials per unit $1.80 $1.80 $1.80 $1.80 $1.80 Growth 0.0% 0.0% 0.0% 0.0%
Labor Cost $2,160,000 $2,160,000 $2,160,000 $2,160,000 $2,160,000 Labor total -> per unit $2,160,000 $0.30 $0.30 $0.30 $0.30 $0.30 Growth 0.0% 0.0% 0.0% 0.0%
Energy Cost $600,000 $600,000 $600,000 $600,000 $600,000 Energy total -> per unit $600,000 $0.08 $0.08 $0.08 $0.08 $0.08 Growth 0.0% 0.0% 0.0% 0.0%
Gross profit on sales $20,280,000 $20,280,000 $20,280,000 $20,280,000 $20,280,000
SG&A $660,000 $660,000 $660,000 $660,000 $660,000
Sales cost $300,000 $300,000 $300,000 $300,000 $300,000 Annual Sales costs - > per $300,000 $0.04 $0.04 $0.04 $0.04 $0.04 Growth 0.0% 0.0% 0.0% 0.0% 0.0%
Overhead Cost $360,000 $360,000 $360,000 $360,000 $360,000 Overhead Expenses 1% 1% 1% 1% 1% 1%
Market Research $5,000,000 $0 $0 $0 $0 $0 Market Research $5,000,000
EBITDA ($5,000,000) $19,620,000 $19,620,000 $19,620,000 $19,620,000 $19,620,000
Depreciation $10,000,000 $10,000,000 $10,000,000 $10,000,000 $10,000,000 Depreciation Schedule 1/5 1/5 1/5 1/5 1/5
EBIT ($5,000,000) $9,620,000 $9,620,000 $9,620,000 $9,620,000 $9,620,000
Interest expense $1,634,560 $1,415,684 $1,095,684 $775,684 $455,684 Int Exp/(Debt) 16% 16% 16% 16% 16%
Net income before taxes (EBT) ($5,000,000) $7,985,440 $8,204,316 $8,524,316 $8,844,316 $9,164,316

Taxes ($1,500,000) $2,395,632 $2,461,295 $2,557,295 $2,653,295 $2,749,295 Tax Rate 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
Net income (Earnings) ($3,500,000) $5,589,808 $5,743,022 $5,967,022 $6,191,022 $6,415,022

Calculating Free Cash Flow 0 1 2 3 4 5

EBIT ($5,000,000) $9,620,000 $9,620,000 $9,620,000 $9,620,000 $9,620,000
(-) Taxes ($1,500,000) $2,395,632 $2,461,295 $2,557,295 $2,653,295 $2,749,295 Tax Rate 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
NOPLAT ($3,500,000) $7,224,368 $7,158,705 $7,062,705 $6,966,705 $6,870,705
(+) Depreciation $0 $10,000,000 $10,000,000 $10,000,000 $10,000,000 $10,000,000
(-) Investments in WCR ($1,080,000) ($3,160,110) $0 $0 $0 $4,240,110
(-) Net Cap Exp ($50,000,000) $0 $0 $0 $0 $2,800,000
(+) Expenditure $50,000,000
(-) Salvage $4,000,000
(+) Tax $1,200,000
(-) Opportunity Costs $0 ($60,000) ($60,000) ($60,000) ($60,000) ($60,000)
Lost Beverage Cash Flow
Lost Rental Cash Flow $0 $60,000 $60,000 $60,000 $60,000 $60,000

Free Cash Flow

WACC 18.2%
PV of Cash Flows
NPV of Cash Flows
IRR
Payback Period
Pro Forma Managerial Balance Sheet (thousands of pesos) MBS Assumptions

0 1 2 3 4 5 0 1 2 3 4 5
Cash Cash/Sales No Assumptions provided so no forecast given
Accounts receivable AR/(Sales/365): Collect 45 45 45 45 -
Inventory Months of Materials 1 1 1 1 1 -
-Accounts payable AP/(Materials/365): Pay 36 36 36 36 -
Total WCR
Gross PP&E
Review how the modeler used the cap ex plan and
Accumulated depreciation annual depr to forecast.

Net PP&E

Total Capital Invested

Debt Debt 20% 20% 20% 20% 20% 20%

Equity Equity 80% 80% 80% 80% 80% 80%
Total Employed Capital

Pro forma Income Statements (thousands of pesos) I/S Assumptions

0 1 2 3 4 5 0 1 2 3 4 5

Total Sales
Sales of Low Cal Product
Units Sales new units
Price Sales price (pesos)

Cost of goods sold

Materials Cost Materials per unit
Labor Cost Labor per unit
Energy Cost Energy per unit
Gross profit on sales
SG&A
Sales cost Annual Sales costs per unit
Overhead Cost Overhead Expenses
Market Research Market Research
EBITDA
Depreciation Depreciation Schedule
EBIT
Interest expense Int Exp/(Debt)
Net income before taxes (EBT)

Taxes Tax Rate

Net income (Earnings)

Calculating Free Cash Flow 0 1 2 3 4 5

EBIT
(-) Taxes Tax Rate
NOPLAT
(+) Depreciation
(-) Investments in WCR
(-) Net Cap Exp
(+) Expenditure
(-) Salvage
(+) Tax
(-) Opportunity Costs
Lost Beverage Cash Flow
Lost Rental Cash Flow

Free Cash Flow

WACC
PV of Cash Flows
NPV of Cash Flows
IRR
Payback Period
Project X
Pro Forma "Operating Side" Managerial Balance Sheet MBS Assumptions

0 1 2 0 1 2
Cash $0 $0 $0 Cash/Sales
Accounts receivable AR/(Sales/365)
Inventory Months of Materials
-Accounts payable AP/(Materials/365):
Total WCR
Gross PP&E
Use your cap ex plan (lines 51-54) and
Accumulated depreciation depreciation schedule to forecast these
items!!
Net PP&E

Total Capital Invested $0 $0 $0

Pro forma Income Statements I/S Assumptions

0 1 2 0 1 2

Sales new units

Total Sales Sales price (pesos)
Cost of goods sold
Materials Cost Materials
Labor Cost Labor
Energy Cost Energy
Gross profit on sales $0 $0
SG&A
Sales cost Sales
Overhead Cost Overhead
Other costs… Other assumptions…
EBITDA $0 $0
Depreciation Depreciation Schedule
EBIT $0 $0

Calculating Free Cash Flow 0 1 2

EBIT
(-) Adj Taxes Tax Rate
NOPLAT
(+) Depreciation
(-) Investments in WCR
(-) Net Cap Exp
Expenditure
Salvage
Tax
(-) Opportunity Costs

Free Cash Flow

Factor=1/(1+WACC)^t WACC
PV of Cash Flows
Profitability Index
IRR
Payback Period