Capital Budgeting Case

Instructions

THIS IS A GROUP PROJECT. GROUPS UP TO THREE STUDENTS ARE ALLOWED.

Capital Budgeting

Capital budgeting is the process a business or investor undertakes to evaluate potential projects or new business ventures. Construction of a new plant, the remodeling of an old one, establishing a new line of production, starting a new business, or buying a franchise are examples of projects that would require capital budgeting techniques for their analysis before being implemented. Other typical project examples are:

1. Mia’s Soups & Green Stuff, a healthy food bistro in San Marcos, TX
2. Wasser Athletic Place, a water gym and training facility in New Braunfels, TX
3. La Maison de Couture La Etoile Solitaire, a fashion design house by Congress Avenue in

Austin, TX.

The Project

After graduating from Texas State, you have decided to “start your own business”. To start a new business requires, of course, a start-up capital, and you do not have any. Fortunately, dear old Uncle

Joe is ready to help but lending you sufficient capital to start your business venture. After a careful analysis of available investment opportunities and considering your educational background and experience, you have narrowed your selection down to one specific project like any of the three examples mentioned above. For analytical purposes, the project’s time frame is five years. After five years you will sell off your investment and go on to something else.

See: Capital budgeting cash flow projection

The Assignment: Capital Budgeting Case

This assignment consists of analyzing a capital budgeting project. It involves 4 parts:

1. Estimating the projects free cash flow (Chapter 12)
2. Selecting the Discount Rate (Capital Cost)
3. Calculating the Capital Budgeting Criteria (Chapter 13)
4. Deciding about the project’s financial viability.

Completing the EXCEL Capital Budgeting Template

The CBC F20 EXCEL template has 5 tabs:

• Tab 1: Rubric
• Tab 2: links
• Tab 3: FCF – Criteria
• Tab 4: Analysis and Decision
• Tab 5: Example

Rubric and Deadlines (Tab 1: Rubric)

Check for rubric and deadlines. Bonus points are offered for early submission.

Related Links (Tab 2: Links)

Check for links for further explanation of the capital budgeting criteria.

Free Cash Flow and Capital Budgeting Criteria (Tab 3: FCF – Criteria)

Data (Rows 7 to 16)

Select and enter the amounts and rates between the indicated ranges. As a whole, they must be different from the amounts and rates provided in the example. For instance, you can select $650,000 for sales; 6% for growth rate; 52% for fixed cost (% of sales); 25% for tax rate; and 11% for NWC as percentage of sales.

The ranges are:

Sales year 2021 $400,000 – $800,000

Sales Growth Rate 4% – 8%

Fixed cost (% of sales) 50% – 60%

Tax Rate 22% – 28% NWC as a percentage of Sales 10% – 15%

Example:

Sales year 2021 $ 520,000

Sales Growth Rate 6%

Fixed cost (% of sales) 55%

Tax Rate 25%

NWC as a percentage of Sales 12%

The template automatically estimates the following: Equipment Cost at t = 0 $ 208,000

Installation Costs at t = 0 $ 16,640

Resale Value at t = 5 $ 41,600

Ending Book Value $ 22,464

Sales Projection (Rows 18 to 20)

Estimate and enter yearly sales for the years 2021 to 2025.

Example:

Sales Projection 2021 2022 2023 2024 2025

0 1 2 3 4 5

Sales Projection $ 520,000 $ 551,200 $ 584,272 $ 619,328 $ 656,488

Sales increases at 6% starting at $520,00 in year 1.

Net Working Capital (NWC) Cash Flows (Rows 23 to 26)

Estimate and enter the required NWC at the beginning of each year (t=0 for year 2021, t = 1 for

year 2022, and so on).

Example:

Net Working Capital Cash Flows 2021 2022 2023 2024 2025

0 1 2 3 4 5

Required NWC $62,400 $66,144 $70,113 $74,319 $78,779

Change in NWC $3,744 $3,969 $4,207 $4,459 -$78,779

The required NWC is 12% of anticipated sales for the following year. Then, calculate the change in

NWC for years 2021 to 2025. At the end of the project (t=5), the full amount of the project is recuperated in full.

Important: Notice that the amounts $ 3,744, … $ 4,459 are outflows and are shown as positive.

That is fine since for the Free Cash Flow (FCF) estimation, the change in NWC will be subtracted.

So, the impact in the FCF is negative as it should be. Following the same line of reasoning, the amount $ 78,779 is an inflow and is shown as negative. Again, that is fine since for the Free Cash Flow (FCF) estimation, it will be subtracted. So, the impact in the FCF is positive as it should be.

Depreciation Table (Rows 29 to 32)

Estimate and enter depreciation expense for years 2021 to 2025. Depreciation expense is based on the cost of the equipment + installation costs, ending book value, and the life of the asset (5 years).

Apply formula 12-2). Then, estimate the book value. Book value at t =1 equals book value at t = 0 minus depreciation expense for the year 2021.

Example:

Depreciation Table 2021 2022 2023 2024 2025

0 1 2 3 4 5

Annual Depreciation $ 40,435 $ 40,435 $ 40,435 $ 40,435 $ 40,435

Book Value $ 224,640 $ 184,205 $ 143,770 $ 103,334 $ 62,899 $ 22,464

Fixed Asset Cash Flows (Rows 35 to 41)

All data is provided by the case. There is an initial cash outflow at t=0 to pay for the equipment and its installation. There is an inflow at t=5 when the equipment is sold at its resale value. The business will record a capital gain or a capital loss. So, there is a tax effect to be calculated. It will be negative in the case of a capital gain (the gain increases net income and the tax liability), it will be positive in the case of a capital loss (the loss will decrease net income and the tax liability). To estimate the tax effect, compare the resale value and the book value at t=5, take the difference, and apply the tax rate.

Example:

Fixed Assets Cash Flows 2021 2022 2023 2024 2025

0 1 2 3 4 5

Equipment $ 208,000 Installation Costs $ 16,640 Resale Value $ (41,600)

Tax effect $ 4,784

Change in Fixed Assets $ 224,640 $ (36,816)

Important: Notice that the amount of $ 224,640 is an outflow and is shown as positive. That is fine since for the Free Cash Flow (FCF) estimation, it will be subtracted. So, it will impact the FCF negatively as it should be. Following the same line of reasoning, the amount of $ 36,816 is an inflow and is shown as negative. Again, that is fine since for the Free Cash Flow (FCF) estimation, it will be subtracted. So, it will impact the FCF positively as it should be.

Free Cash Flow (Rows 44 to 56)

Complete the Free Cash Flow Table.

Sales: copy from above.

Fixed costs: Apply the selected % rate to sales

Depreciation: Copy from above

EBIT = Sales – Fixed Cost – Depreciation

Taxes: Apply the selected rate to EBIT.

Net Income = EBIT – Taxes

Depreciation: Copy from above

Operational Cash Flow: Net Income + Depreciation

Change in Net Working Capital: Copy from above

Change in Fixed assets: Copy from above.

Free Cash Flow: Operational Cash Flow – Change in Net Working Capital – Change in Fixed

Assets.

Example:

Free Cash Flow 2021 2022 2023 2024 2025

0 1 2 3 4 5

Sales $520,000 $551,200 $584,272 $619,328 $656,488

– Fixed Costs $442,000 $468,520 $496,631 $526,429 $558,015

– Depreciation $40,435 $40,435 $40,435 $40,435 $40,435

EBIT $37,565 $42,245 $47,206 $52,464 $58,038

– Taxes $9,391 $10,561 $11,801 $13,116 $14,510

Net Income $28,174 $31,684 $35,404 $39,348 $43,529

+ Depreciation $40,435 $40,435 $40,435 $40,435 $40,435

Operational Cash Flow $68,609 $72,119 $75,839 $79,783 $83,964

– Change Net Working Capital

$62,400 $3,744 $3,969 $4,207 $4,459 -$78,779

– Change Fixed Assets $224,640 -$36,816

FREE CASH FLOW -$287,040 $64,865 $68,150 $71,633 $75,324 $199,558

Capital Budgeting Criteria (Rows 65-70)

Select your own discount rate. Estimate the standard set of capital budgeting criteria: Pay-back (years), Discounted Pay-Back (years), Profitability Index (number), Net Present Value ($), Internal

Rate of Return (%)

Analysis and Decision (Tab 4)

Complete a short analytical review of your capital budgeting project, explaining the main takeaways of the exercise. Use the indicated space. You should answer questions such as what does each of the criteria indicate? Is there any discrepancy among the criteria? Would you undertake the project?

Why?

Example (Tab 5)

Check to review the relations and equations used to build the FCF and calculate the criteria.

Notes

Capital budgeting is the process a business or investor undertakes to evaluate potential projects or new business ventures. The following steps are involved:

1. Estimate the free cash flows of the new project.
2. Assess the riskiness of the cash flows.
3. Determine the appropriate discount rate (i), based on the riskiness of the cash flows and the general level of interest rates. This is called the project cost of capital in capital budgeting.

4. Evaluate the cash flows and decide on the project’s financial viability.

The net present value (NPV) is simply the sum of the present values of a project’s free cash flows.

The formula is

= −0 + 1

(1 + )1 +

2 (1 + )2

+ 3

(1 + )3 +

4 (1 + )4

+ 5

(1 + )5

The rationale behind the NPV method is straightforward: if a project has NPV = $0, then the project generates exactly enough cash flows to recover the cost of the investment and to enable investors to earn their required rates of return (the cost of capital). If NPV = $0, then in a financial sense, the project breaks even. If the NPV greater than zero, then more than enough cash flow is generated, i.e., the project is financially viable, and conversely, if NPV is less than zero, then the project does not generate enough cash flow, i.e., the project is not viable.

The internal rate of return (IRR) is that discount rate which forces the NPV of a project to equal zero. In terms of a formula,

= −0 + 1

(1 + )1 +

2 (1 + )2

+ 3

(1 + )3 +

4 (1 + )4

+ 5

(1 + )5

There is no close formula to estimate the IRR. Use a financial calculator or a spreadsheet to estimate it. The IRR measures a project’s profitability in the rate of return sense: if a project’s IRR equals its cost of capital, then its cash flows are just sufficient to provide investors with their required rates of return. An IRR greater than the cost of capital (discount rate) means that the project earns enough to cover its cost of capital, i.e., the investor earns the required rate of return.

The payback period (years) is the expected number of years required to recover a project’s initial cost (cash flow at t=0). Payback represents a type of “breakeven” analysis: the payback period indicates when the project will break even in a cash-flow sense. Discounted payback is similar to payback except that discounted cash flows are used instead of the simple cash flows.

The assignment in a nutshell

Wasser Gyms of America WGA, Inc. has spent $250,000 on research to design a new water gym and training facility. The firm is planning to spend $ cell B13 on equipment and furniture for the new gym to be located in New Braunfels, TX. Shipping and installation costs of the equipment will be capitalized and depreciated; they total $ cell B14. The equipment has an expected life of five years, a $ cell B15 resale value, and it is to be depreciated using the straight-line method with and bookending value of $ cell B16. Revenue from fees is expected to be $ cell G7 for the first year and to grow at cell G8 rate thereafter, with fixed costs estimated to be cell G9 of sales per year. The firm has a tax rate of cell G10 percent, an opportunity cost of capital of x percent, and it expects net working capital to be cell G11 of the following year anticipated sales. Estimate the Free Cash Flows.

Calculate the capital budgeting criteria measures (PB, DPB, PI, NPV, and IRR). Analyze the financial viability of the project.

Links

https://courses.lumenlearning.com/boundless-finance/chapter/net-present-value/

https://www.investopedia.com/terms/n/npv.asp

https://hbr.org/2014/11/a-refresher-on-net-present-value

https://www.investopedia.com/terms/i/irr.asp

https://hbr.org/2016/03/a-refresher-on-internal-rate-of-return

https://courses.lumenlearning.com/boundless-finance/chapter/internal-rate-of-return/ https://www.investopedia.com/terms/p/profitability.asp

https://www.investopedia.com/terms/d/discounted-payback-period.asp

FCF – Criteria

The discount rate of return is the expected or required rate of return for an investment in a project or a business venture. Also known as the cost of capital, it is used to estimate the current value of an investment based on its expected future cash flows. The required rate of return differs from investor to investor. Select your own discount rate.

Please fill in all cells highlighted in green

t=0 indicates the beginning of 2021, t=1 indicates the ending of year 2021 and the beginning of year 2022, …

Example

Analysis and Takeways:

Last Updated on November 23, 2020