# Excel 2010 Financials: Identifying the Profitability of an Investment

#### Excel 2010 Financials Cookbook

\$23.99

Exploit the flexibility of Excel to the max by using it to manage and analyze your financial data. These hands-on recipes will give you the techniques you need to go from casual user to power user.

## Excel 2010 Financials Cookbook

 Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

(For more resources on this subject, see here.)

# Calculating the depreciation of assets

Assets within a business are extremely important for a number of reasons. Assets can become investments for growth or investments in another line of business. Assets can also take on many forms such as computer equipment, vehicles, furniture, buildings, land, and so on. Assets are not only important within the business for which they are used, but they are also used as a method of reducing the tax burden on a business.

As a financial manager, you are tasked with calculating the depreciation expense for a laptop computer with a useful life of five years.

In this recipe, you will learn to calculate the depreciation of an asset over the life of the asset.

## Getting ready

There are several different methods of depreciation. A business may use straight-line depreciation, declining depreciation, double-declining depreciation, or a variation of these methods. Excel has the functionality to calculate each of the methods with a slight variation to the function; however, in this recipe, we will use a straight-line depreciation. Straight-line depreciation provides equal reduction of an asset over its life.

## Getting ready

We will first need to set up the Excel worksheet to hold the depreciation values for the laptop computer:

1. In cell A5 list Year and in cell B5 list 1.

This will account for the depreciation for the year that the asset was purchased.

2. Continue this list until all five years are listed:

3. In cell B2, list the purchase price of the Laptop computer; the purchase price is \$2500:

In cell B4, we will need to enter the salvage value of the asset. The salvage value will be the estimated resale value of the asset when it is useful life, as determined by generally accepted accounting principles, has elapsed.

4. Enter \$500 in cell B3:

5. In cell B5 enter the formula =SLN(\$B\$2,\$B\$3,5) and press Enter:

6. Copy the formula from cell C5 and paste it through cell C9:

Excel now has listed the straight-line depreciation expense for each of the five years. As you can see in this schedule, the depreciation expense remains consistent through each year of the asset's useful life.

## How it works...

Straight-line depreciation calculates the value of the purchase price minus the salvage price, and divides the remainder across the useful life.

The function accepts inputs as follows =SLN(purchase price, salvage price, useful life).

## There's more...

Other depreciation methods are as follows:

# Calculating the future versus current value of your money

When working within finance, accounting, or general business it is important to know how much money you have. However, knowing how much money you have now is only a portion of the whole financial picture. You must also know how much your money will be worth in the future. Knowing future value allows you to know truly how much your money is worth, and with this knowledge, you can decide what you need to do with it.

As a financial manager, you must provide feedback on whether to introduce a new product line. As with any new venture, there will be several related costs including start-up costs, operational costs, and more. Initially, you must spend \$20,000 to account for most start-up costs and you will potentially, for the sake of the example, earn a profit of \$5500 for five years. You also know due to expenditures, you expect your cost of capital to be 10%.

In this recipe, you will learn to use Excel functions to calculate the future value of the venture and whether this proves to be profitable.

## How to do it...

We will first need to enter all known values and variables into the worksheet:

1. In cell B2, enter the initial cost of the business venture:

2. In cell B3, enter the discount rate, or the cost of capital of 10%:

3. In cells B4 through B8, enter the five years' worth of expected net profit from the business venture:

In cell B10, we will calculate the net present value:

1. Enter the formula =NPV(B3,B4:B8) and press Enter

We now see that accounting for future inflows, the net present value of the business venture is \$20,849.33. Our last step is to account for the initial start-up costs and determine the overall profitability.

2. In cell B11, enter the formula =B10/B2 and press Enter:

As a financial manager, we now see that for every \$1 invested in this venture, you will receive \$1.04 in present value inflows.

## How it works...

NPV or net present value is calculated in Excel using all of the inflow information that was entered across the estimated period. For the five years used in this recipe, the venture shows a profit of \$5500 for each year. This number cannot be used directly, because there is a cost of making money. The cost in this instance pertains to taxes and other expenditures.

In the NPV formula, we did not include the initial cost of start-up because this cost is exempt from the cost of capital; however, it must be used at the end of the formula to account for the outflow compared to the inflows.

## There's more...

The \$1.04 value calculated at the end of this recipe is also known as the profitability index. When this index is greater than one, the venture is said to be a positive investment.

(For more resources on this subject, see here.)

# Identifying the profitability of an investment

There are many methods for gauging the effectiveness and profitability of an investment. The more analysis tools that you can use, the more prepared you will be to either move forward or decline an investment. There are numerous external factors that have been discussed so far such as the net present value that measures magnitude of return; however, equally important are internal factors regarding the return on investment for measuring efficiency or quantity.

As a financial manager, you must provide feedback on whether to introduce a new product line. As with any new venture, there will be several related costs including start-up costs, operational costs, and more. Initially, you must spend \$20,000 to account for most start-up costs and you will earn a profit of \$5500 for 5 years.

In this recipe, you will learn to use internal rate of return functions to make a decision of profitability.

## How to do it...

We will begin by entering basic information regarding the business venture we are testing for profitability:

1. In cell B2, enter the initial cost of venture start-up:

2. In cells B3 through B7 enter the yearly inflows from the venture.
For this analysis, we will not utilize the cost of capital because the inflows listed represent the net income:

3. In cell B8, enter the formula =IRR(B2:B7) and press Enter:

The internal rate of return is calculated at 12%. By utilizing this information, we are now able to determine that the rate of return of 12% provides a profitable return for the internal capital that is used within this venture.

## How it works...

Internal rate of return utilizes capital expenditures and inflows to calculate an internal rate of return based on the estimated growth. The higher the percentage of return, the higher the possible profitability; comparing two ventures, the venture with the higher IRR will likely produce greater growth.

The IRR function in Excel requires the inclusion of a negative number to represent the capital expenditure of the business.

## There's more...

Combining internal rate or return with the analysis function of net present value will provide a more complete picture to gauge the future return as well as internal profitability.

# Calculating and planning for inventory requirements

Inventory is often a necessary component to business, especially retail. Retail businesses often maintain an inventory of products to allow for sales and customer demand; however, inventory does not have to be limited to product inventory. Staffing can also be considered inventory with respect to forecasting, as a business must maintain a certain level of staffing in order to fulfil the business requirements.

As a financial manager, you are tasked with ensuring proper stock levels of widget A are available for purchase to fulfil customer demand.

In this recipe, you will learn to use forecasting functions to ensure proper product levels.

## Getting ready

For this recipe, we will utilize a toolset in Excel known as the Data Analysis Toolpak. For most installations of Excel, this toolpak is not installed automatically. You will need to install this functionality:

1. Click on the File tab within the Excel ribbon and choose Excel Options:

2. From the Excel Options, choose the Add-Ins option from the left panel:

3. Ensure that Excel Add-ins is listed in the Manage dropdown box near the bottom of the window, and select Go.
The Add-ins manager will open.
4. Select the Analysis Toolpak checkbox and select OK:

Excel will now install the Analysis Toolpak. Depending on the options chosen when Office was first installed, you may be prompted to insert your Office installation discs.

Upon completion of the installation, you will have a new group called Analysis located under the Data tab on the Excel ribbon.

## How to do it...

We will begin by entering the product sales levels for Widget A onto the worksheet:

1. Beginning in Cell B2, enter the product information as well as the labels for each month of product sales.

2. From the Data menu, select Data Analysis:

3. In the Data Analysis window, select Moving Averages, and click on OK:

Excel will now prompt for information regarding the location of data and the size of data points.
4. In the Moving Averages window, change the Input Range to \$B\$2:\$B\$13, set the interval to 3, and set the Output Range to \$D\$2, and check the box to Chart Output; select OK:

Excel now calculates the moving average and enters the forecasted values into the cells in column D vertically to correspond with the original month labels:

From the data presented and the chart of information, we now have forecasted sales information for Widget A, and can utilize the forecasted average data to ensure proper inventory levels.

## How it works...

Moving average is a function in Excel that will take the average of a number of cells and provide this information. When entering the parameters for the analysis, we chose an interval of 3; this means that Excel will take a cell and the 2 cells preceding, totally 3 cells, and provide an average to the data points.

By allowing Excel to graph this information automatically, we are able to see the visual forecast and also how this correlates to the actual data provided. As needed, we may now adjust inventory levels up or down to ensure we meet customer demand.

## There's more...

Excel defaults the interval for the moving average to three; however, you can modify the fluctuation of the forecast by altering this number. A larger interval number will encompass more data values hence resulting in a smoother less fluctuating forecast. A smaller interval will produce a forecast with greater fluctuation due to the smaller sample size for averages.

# Summary

This article showed us how to identify the profitability of an investment with the focus on depreciation, and the value of a dollar, using Excel 2010 Financials.

Further resources on this subject:

### Books to Consider

Excel 2013 Dashboard Design [Video]
\$ 72.25
Amazon Web Services: Migrating your .NET Enterprise Application
\$ 26.99
comments powered by Disqus