Excel 2010 Financials: Identifying the Profitability of an Investment

Exclusive offer: get 50% off this eBook here
Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook — Save 50%

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook

$23.99    $12.00
by Andre Odnoha | July 2011 | Cookbooks Enterprise Articles Microsoft

Building financial function into Excel, as shown in the previous article, will augment the Excel toolset with user defined functions broadening the abilities of Excel. In this article wee will focus on depreciation, and the value of a dollar.

In this article by Andre Odnoha, author of Excel 2010 Financials Cookbook, we will learn:

  • Calculating the depreciation of assets
  • Calculating the future versus current value of your money
  • Identifying the profitability of an investment
  • Calculating and planning for inventory requirements

 

Excel 2010 Financials Cookbook

Excel 2010 Financials Cookbook

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

        Read more about this book      

(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:

    Excel 2010 Financials tutorial

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

    Excel 2010 Financials tutorial

    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:

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

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:

Excel 2010 Financials tutorial

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:

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

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

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

    Excel 2010 Financials tutorial

    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:

    Excel 2010 Financials tutorial

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.

Excel 2010 Financials Cookbook Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:
        Read more about this book      

(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:

    Excel 2010 Financials tutorial

  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:

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

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:

    Excel 2010 Financials tutorial

  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 2010 Financials tutorial

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.

Excel 2010 Financials tutorial

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.

    Excel 2010 Financials tutorial

  2. From the Data menu, select Data Analysis:

    Excel 2010 Financials tutorial

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

    Excel 2010 Financials tutorial

    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 2010 Financials tutorial

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:

Excel 2010 Financials tutorial

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:


Excel 2010 Financials Cookbook Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook
Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more
Select your format and quantity:

About the Author :


Andre Odnoha

Andre Odnoha has been working within the business and technology field for many years combining his knowledge of business and information technology. With his knowledge in both software and hardware related to computer systems and a Bachelor's degree in Business Administration, Andre works to combine and utilize technology from a business perspective.

Andre has over 11 years of experience in computer software and hardware and has programmed numerous software applications designed for medical billing, e-commerce, and content management.

Books From Packt


E-Business Suite 12.1 Financials Cookbook
E-Business Suite 12.1 Financials Cookbook

Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation
Oracle PeopleSoft Enterprise Financial Management 9.1 Implementation

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Pentaho Data Integration 4 Cookbook
Pentaho Data Integration 4 Cookbook

Statistical Analysis with R
Statistical Analysis with R

iReport 3.7
iReport 3.7

Sage Beginner's Guide
Sage Beginner's Guide

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting
Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
y
L
m
B
G
7
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software