About Financial and Investment tips

Archive for February 28th, 2010

financial model construction is an art. The only way to improve your business, is a variety of funding models on a wide range of industries to build. Let an investment model that is not out of reach of most people -. As an investment property

You can download

with a model of investment goods.

Before we embark on building a financial model, we must ask what motivates the company, we are exploring. The answer will have major consequences on how we construct the model.

For example, a financial institution like a bank is driven by its assets (investments, loans, mortgages, etc.), then we’d probably superior predict the growth of the company’s equilibrise sheet. A retail store is partly by income of its products. Therefore, we should focus on income forecasts and profit and loss account.

Who uses it?

are other problems that come with this model and use what they are? A firm might have a new product for which we compute an optimal price. Or investor, the assignment of a project to see what kind of investments he or she can expect.

According to can be very different from these scenarios, the final result, which compute the model. If you know what the decision to model users must be off several times until a concept that the right inputs used to find the corresponding products.

Properties

In our scenario we want to know what kind of financial performance, we owned a specific investment amount of information on investment. This information includes variables such as the buy price, appreciation, and at what price we can rent, arrangements for financing of goods acquirable foreground, etc.

our rental income and assessing the value of p return on this investment will be determined by two main factors. Therefore, we should begin from forecast rental income and property value considerations.

As soon as we pointed out that under the model we might use information that we calculated to find ways to encourage the buy of property and financial costs, we anticipate might result from the use of funds built.

Next, we will forecast the costs of property management can be overcome. We need the value of the property we had anticipated being healthy to compute property taxes, it is important that we build the model in a particular order.

Once we have established these projections, we can begin the assembly of the profit and loss statement and equilibrise sheet. Since we place this into effect, if we can refer the concepts that we have not calculated and we can go back and add them to the appropriate places.

Finally, we can be financial stocks on the project cash flow for investors and compute the return on investment.

Add

, Template

Since the construction is a evenhandedly complicated model, we must think about how we wish to interpret, so we keep our work area clean. Excel is one of the ideal ways to organize financial models to separate certain sections of the model to different worksheets.

By the calculations and projections that are closely linked in the same worksheet and the separation of other calculations that are more closely related to other sections of the model on separate tabs (sheets ), we believe our model is organized.

We

describes apiece journalism to a name, the information contained therein. This grants other users of the model to superior comprehend where the data is calculated in the model and how it flows.

In our model, such as capital investment, we use the four tabs: real estate, finance, money and finance. Real estate, financing and cost are the tabs that we input hypothesis and the predictions of our model. The Financials journalism will be our results page where we have the output of our model in a way that is easy to comprehend display (in the form of financial statements).

Sales Forecasting

First Things First, with the property tab, rename the journalism “ownership” and adding the title in cell A1 the worksheet. Taking care of some of the question place on the front, we have easier to keep clean model.

Then, if we can make our assumptions. A few lines below the title, type “assumptions” and a vertical list of the following entries:

price

Sat first month’s rent

p> <Annual Report of appreciation

Annual rent

Broker Fee

Investment Period

p / in the cells right from the adjudge of apiece entry, we have up an input field with a realistic placeholder for apiece value. We format apiece of these values ​​are blue. This is a common modeling convention indicates that these input values. This format is easier to comprehend for ourselves and others how the model works. Here are some appropriate values ​​to begin with:

0,000.00

550.00

95.00%

3.50%

1.00%

6.00%

4 years

The buy price is the price we anticipate that to condemn a particular property. The initial monthly rent is the price for which we anticipate to rent the property. The load bourgeois determines how we keep the leased property (95% utilization means that it is only about 18 days will not rented to tenants per year).

Annual Report thinks about determining the rate, which increases the value of our homes (or decreases) per year. Annual rent increase will be to determine how much we increase the rent apiece year. Measures what percentage fee broker the understanding price of the property, we have a broker, if we sell to pay for goods.

The investment period is as long as we owned before selling it to hold. Now that we have a good set of ownership assumptions, we can begin to make calculations based on these assumptions.

A note on the periods of time

There are several ways to begin the predicted values ​​over time. One could project finances monthly, quarterly, annually or a combination of all three. For most models, review of financial projections by month for years.

you can use the model, some of the cyclicality of the business (if any) to see. You also have some problems with the business model that does not appear in the annual surveys (such as cash equilibrise defects) could be granted on the site. After the first year, then you can predict the financial statements on an annual basis.

For our needs, will reduce annual estimate of model complexity. A side effect of this election is when we begin amortization mortgage later, we filed incurred interest costs more than we do when we prefabricated monthly principal payments (what happens in reality) .

more modeling choices, you might think about is whether to use the headings effective date of your columns projection (31/12/2010, 31/12/2011, …) . This can help to implement complex functionality later, but again, for our purposes, we are simply 1, 2, use 3, etc. to measure our years. In Excel, we can read and play with the formatting of these numbers a bit:

Year 1 Year 2 Year 3 Year 4 …

These figures should be in a box under our assumptions the first year in column B. We have at least these values ​​are adequate to take years to be enrolled in ten. Forecasts for over ten years and have no credibility so that most financial models, not more than ten years.

On the projections

Now we use our time labels on the “Property” worksheet, we are ready to begin our forecasts. Here are the original values ​​we want for the next ten years in our proposed model:

Property Value

Annual rent

Property for understanding

Fees Bureau

Mortgage Bal.

Equity Line Bal.

Net income

valuable possessions Menu

Add this article in column A below and on the left side, where we have labels per year.

The value of the property line is simply the value of the proposed property over time. The value of the first year will be equal to our assumption of the buy price and the formula because it is precisely this assumption. The formula for apiece year on the right side of the first year are:

= B14 * (1 + $ B)

When the cell B14 is directly related to year in which we are currently calculating the value of the property and left $ B is an absolute reference in our “annual appreciation” of delivery. This formula can be applied on the line to compute the remaining years of the property value.

The line of the annual rent calculated annual rental income of property apiece year. The formula for the first year is as follows:

= IF (> = B12 $ B, 0, B5 * 12 * $ B)

B12, “1″ labels will be created. $ B should have an absolute reference (to save the data in our cell hypothesis must be an integer, even if it is formatted as follows: “annual”, otherwise the formula does not work) on our investment period to adopt. B5, a reference must be an absolute reference to Use our assumption of a monthly subscription and $ B.

What does this feature is that if our investment is less than the year of calculation of the value, then the result must be zero (we will no longer own the building after it is sold, we might collect “rent-t). Otherwise, the formula for calculating the annual rent, the monthly rent is multiplied by twelve and then multiplied by the rate of occupation.

For subsequent years, the formula looks like this: p>

/ = IF (> C12 = $ B, 0, B16 * (1 + $ B ))

/ p> Even if the investment is less than the year to compute the value, then the result will be zero. Otherwise, you simply take the value of current years, revenues rental and increase our hypothesis of increased rents in the cell $ B

Out Time

Now that we predicted property values ​​and rental income, we can now predict the proceeds from the subsequent understanding of the property. to get the net proceeds from the understanding of our office property, we need values ​​higher than expected. understanding price of property, brokerage, mortgage and the equilibrise equity equilibrise

The formula for predicting the understanding price is:

= IF (B12 = $ B, B14, 0)

This formula states that if the current year (B12) is equal to the period of our investment ($ billion), while our price is equal to the value of our nominated this year (B14) . Otherwise, if the year is the year we plan to sell the property, then it is not a understanding and the understanding price is equal to zero

The formula for fees brokerage following a similar approach.

= IF (B18 = 0.0, B18 * $ B)

This formula states that if the understanding price for a year data (B18) is zero, then the brokerage fees are zero. If there is no sale, no brokerage fees. If there is a understanding and brokerage fees are equal to the buy price ( B18) by our assumption of brokerage fees ($ B) times.

mortgage equilibrise and our equity equilibrise we will send you the following worksheet calculated, so for now we want to leave two blank lines as placeholders for these values. Our net proceeds from the understanding of real estate is simply the selling price less the equilibrise of the mortgage broker less expenses, less the equilibrise of having to home.

Adding a line, it also shows that the value of the property that we own, so that it reflects a value of zero when we sold, the formula will be easy “Property Value of property ..

= IF (> = B12 $ B, 0, B14)

B12 for the current year in our series of labels per year. $ B refers to our adoption period of investment and B14 refers to the current annual value of the property value we calculated line. All this line does not represent our line of property value, but there is ground zero for the value of the property after we sell the property. P> </p on <Finance / strong <

/ p> shapes us, we love the real estate financing. That the study of a journalism “financing” again and add the title of “financing” at the top of the table. The first thing we need to know is how much we need to fund.

First, we sort of “purchase price” a few lines below the title. On the right side of this cell, a reference to our assumption of a buy price of the “property” journalism (= object! B4). We will format the text of that cell to be green because we are on information linking one another worksheet. Formatting text in green is a common convention of financial modeling to help keep track of where information flows from.

Below

this line, leave a tiny working capital. “On the right side of the cell type we going acceptance of, 000.00 (formatted in blue indicate on an input). Our working capital is assumed to additional capital, we believe that we must save the regular management of the cover an investment property. We are certain costs that can not be fully covered by our rents and our working capital to ensure that we do not have cash flow problems. Below

line of working capital, we will sort of “Total capital required and the right of this sum cell values ​​of our buy price and assumption of working capital. This amount is the total amount of capital that we raise.

Capital Sources .

few lines as part of our “total capital required,” we create receive capital sources, this area will have six columns with the following topics: source, amount, buy price% , speed, duration and annual payment. Two typical sources of capital for the buy of a property for a mortgage and a line of credit (or credit). Our eventual source of capital (for this model anyway) is our own money or equity.

In the Source column, we add “first mortgage”, “credit line” and “equity” in the three cells in our media column. For a typical mortgage, a bank loan to a rule 80% of property value on a first mortgage, so we will give 80% in line with the first mortgage under the price category (re- formatted in blue indicate an input value)%.

We

now doable to compute the amount of the first mortgage in the amount column with the following formula:

= B5 * C11

B5 is one. reference to our buy price and the C11 is a reference to our hypothesis of buy price%

In the current market situation, banks are reluctant to offer lines of credit mortgage if it invests capital under 25% in the property, But we state they are willing to borrow something. Suppose they give us another 5% of the value of the property as equity. Specify 5% (blue) in the line of credit under the heading%. Buy Price

We can

a similar formula to compute the amount of equity column:

= B5 * C12

Now we have the level of bank financing for our purchases, we can compute how much capital we will need under the amount on line for capital, enter the following formula.

B7 is our total funding needs. B11 funding is acquirable from the first mortgage and B12 is funding acquirable to the equity line of credit. Again, we can adopt that we have to cough money for something we should not be financed by the Bank.

Cost of Capital p> </p Now we know that this funding is going to cost us. For interest rates, we adopt that 5% on the first mortgage and 7% on the equity line. Give these two values ​​in blue Our Rate column. For conditions, a mortgage is typically 30 years and a contribution of the line could be 10 years. Give these values ​​in blue, the term position.

The annual payment will be a column calculating the annual payment, we have to pay in full apiece loan until the end of their terms, including interest, we use an Excel function for that.

=- PMT (D11, E11, B11, 0)

The VPM us the value of fixed premium, it will be given to a certain level (D11) A number of periods (E11), a current value (B11) and a future value (what we want to be neutral to fully repay the loan). We can then work with the same formula in the cell to compute the payment for the equity.

Now we are ready, our map projections. Let’s begin by duplicating the headers displayed in the property registry (year 1, year 2, etc.) and add them to the financing of the journalism under our capital sources. Let us also think about that the land value of online registration of property (identification of values ​​in green to indicate they come from another story).

We have balances, you must first join our Mortgage Let’s adjudge this section of the worksheet “first mortgage” and include the following topics in the first column.

Beginning equilibrise

Mon interest PMT

Main PMT

Closing equilibrise

Post Sales equilibrise

For the year of our opening equilibrise We are only using our first mortgage amount (= B11). For two years later, we’re just using the previous closing stock (= B25).

To compute the interest payment for apiece year, simply multiply the opening equilibrise of our interest rate (= B22 * $ D). B22 equilibrise this year would top $ D and our discount rate.

is

around apiece year, the principal payment to be calculated, simply subtract the current year interest payment of our annual payment ($ = F-B23). $ F is the annual payment, we calculated before, and B23 is

for interest payments this year.

closing equilibrise our equilibrise is just the beginning at least our capital payment (= B22-B24).

Finally, our contribution simply selling our closing equilibrise for apiece year, or zero if we have already sold the property (= IF (B19 = 0.0, B25)). This line is it easy for us to represent our debt if we build our equilibrise sheet later.

We repeat the same lines and calculations for the projection of our equity funds. Once we are done with these two sources, we finished our spreadsheet.

back a bit

We can now file in our line of mortgage and equity balances in the Properties journalism to compute the net proceeds to the mortgage balance, we use the formula. p>

/ = IF (B18 = 0.0, financing: B22)

B18 refers to the understanding value of goods this year if the value is zero, then we be the mortgage equilibrise to zero because we do not sell. Are the property during the year and do not need to show a mortgage balance. If the value is not zero, then we want the mortgage equilibrise for a given year, the journalism on Financing (Financing! B22) can be shown to find.

We use the same formula for calculating the equity balance.

On the expenditure

Let’s expense journalism of our “costs” and add the same title at the top of the table, this spreadsheet is easy and easy at first, we’ll create a plateau of assumptions with labels next command.

Wed Annual Report home fixes

Report annual rental fee broker

Others <Expenditures / p / / p> Beside apiece of these cells, gives us the following values ​​in the adoption blue: p>

< 0.00

0, 00

< / p> Each of these assumptions is an ongoing cost of managing a property. The following are our assumptions box, add our headlines once again one of our other worksheets (in year 1, year 2, etc..)

Let’s drop in a row that our property value calculated above and we

format displays these values ​​in green. We need these values ​​to compute the tax burden so that it will be easier to have it on the same worksheet.

Below this line, we add a few positions that we are the predictions:

Home Fixes

broker rental fees

Other Expenses

taxes

the first year of home fixes is simply equal to our annual Adoption (= B5). For subsequent years, but we must think about whether we still owned and otherwise our costs will be zero if it does, we want to develop our home repair costs by the inflation rate, here that function for the next year should look like this: …

/ p Sat = IF (B15 = 0.0 * C, (1 + $ B))

/ p> In this case, C the value of property in the current year the B15 last year at home fixes and $ B refers to the rate of inflation. For rental brokerage fees and other costs, we can predict the same methodology for expenses.

taxes, we need to use a different calculation .. property taxes on property value, so we used a percentage that is the tax shown hinge Our adoption fee calculation formula are:

= B13 * $ B

Since our taxes will be zero if this property is zero, we can simply multiply the value of our property (B13) accepted by our tax rate ($ B). And now we have our expenses predicted.

Putting it all together

Now comes the fun part. We must implement all of our projections in the equilibrise sheet presented. Since making this part of the model that went on around us, it is very clean and well formatted.

Let’s adjudge on the “Financials” and type the same title at the top of the worksheet. few lines later, we, our equilibrise sheet, beginning with a “budget” adjudge in the first column. Just below this line, we start into our standard categories of one year, but this time we do one year before the year 0 1 column contains

On the left side of the sheet just below the title year, we place the equilibrise sheet as follows:.

Cash

Properties

Total assets

first mortgage

Line of Credit

Total liabilities </<

p>

PUC

Retained earnings

Total equity

Total liabilities and capital own

/ p Sat Check

/ value> p present in year zero is equal to the amount of capital we intend to invest our equity value reference of the financial spreadsheet (Funding =: B13) and format of the value in green.

Real Estate, first mortgages, equity and retained earnings are zero in the year zero, because we have not invested anything yet. We can go further and add formulas to total assets (cash and accommodation), total debt (first mortgage and equity line), equity (paid-up capital more retained earnings) and total liabilities and equity (total debt plus shareholders). These formulas are the same for all years of assessment.

For the year the equilibrise is zero for the capital contributions, we have the same formula as cash for the year zero (= funding! B13) are used.

back

money, we’ll use this line as our equilibrise sheet for cash items is the most liquid equilibrise sheet. To take a case, we cash the amount of total liabilities and shareholders’ equity less. This is to ensure that the equilibrise sheet is always balanced. We need to see if our money is always negative, which could be a problem.

On the equilibrise sheet is the property of the rule of its historical value (represented Our buy price), so we use the formula for the value of our property and it shows the format Green:

= IF (C5> = $ goal B, 0, Real Estate $ ! B)

C5 is the current year. Immobilien! $ B is a reference to our investment period of adoption and $ B is a reference to the buy price. The property value is either zero (after we sold it), or equal our buy price.

Our first mortgage and equity balances online, you can easily switch the equilibrise after understanding on the financing of the tab. We show format of apiece line in green, he pulled beings from another worksheet.

PUC is either equal to our initial investment (since we will not make additional investments) or zero, after we sold the property. The formula is as follows:

= IF

C5 is the current year property $ (C5 object> = $ B, 0, $ B!). ” B is a reference to our investment period of adoption and $ B is a reference to the year zero of our paid-up capital.

We have retained earnings should match that we have planned our profit and loss because it hinges on the net profit jump.

line is a swift check to see if your statement is balanced. It is simply equal to total assets minus total liabilities and equity. value not equal to zero, then you know there’s a problem. As a bell and whistle extra, you can use conditional formatting to highlight all the problems.

Calculation

Bottom Line </strong

/ Below p> check online, we sent our tax return in the same way we use our equilibrise sheet -. with a “profit and loss” adjudge of our year, followed by column headers, we will do our layout profit and loss statement as follows:

rental income

Product Sales

Total revenue

repair

home

broker rental fees

Other expenses

Total operating expenses

Operating income

Interest expense

taxes

Net income

revenue rental income from the sale, studio, bureau fees, other expenses and taxes just from other worksheets where we have calculated (and formatted in green of course) are established.

More investment products

The good news is that, despite the tight credit environment, there are many substitute financing and non-bank financing options acquirable to companies that need a cash infusion, whether it’s to beef up working capital or help assist growth.

However, the bad news is that business owners often shy away from non-bank financing because they don’t comprehend it. Most owners simply rely on their banker for financial information and many bankers (not surprisingly) have only limited experience with options beyond those offered by the bank.

To help assist some of the fear that owners often have of substitute financing, here is a description of the most common types of non-bank financing. There are many struggling businesses out there this day that could benefit from one of these substitute financing options:

Full-Service Factoring: If a business has financial challenges, full-service factoring is a good solution. The business sells its outstanding accounts receivable on an ongoing basis to a commercial finance company (also referred to as a factoring company) at a discount—typically between 2-4 percent—and then the factoring company manages the receivable until it is paid. It is a great substitute when a traditional line of credit is simply not available. There are a number of variables to a program, including full recourse, non-recourse, notification and non-notification.

Spot Factoring: Here, a business can sell just one of its invoices to a factoring company without any commitment to minimum volumes or terms. It sounds like a good solution but it should be used sparingly. Spot factoring is typically more costly than full-service factoring (in the 5-8 percent discount range) and usually requires extensive controls. In most cases, it does not solve the underlying demand of working capital issue.

Accounts Receivable (A/R) Financing: A/R financing is an saint solution for companies that are not yet bankable but have good financial statements and need more money than a traditional lender will provide. The business must submit all of its invoices through to the A/R finance company and pay a collateral management fee of about 1-2 percent to have them professionally managed. A borrowing base is calculated regular and when funds are requested an interest rate of Prime plus 1 to 5 points is applied. If and when the company becomes bankable, it is a evenhandedly easytransition to a traditional bank line of credit.

Asset-Based Lending (ABL): This is a artefact secured by all the assets of a company, including A/R, equipment, real estate and inventory. It’s a good substitute for companies with the right mix of assets and a need for at least million. The business continues to manage and collect its own receivables but submits an aging report apiece month to the ABL company, which will review and periodically audit the reports. Fees and interest make this product more costly than traditional bank financing, but in many cases it provides access to more capital. In the right situation, this can be a very clean trade-off.

Purchase Order (PO) Financing: Best for a business that has a buy order(s) but lacks the supplier credit needed to fill it. The business must be healthy to demonstrate a history of completing orders, and the statement debtor placing the order must be financially strong. In most cases, a PO finance company requires the involvement of a bourgeois or asset-based lender in the transaction. PO financing is a high-risk kind of financing, so the costs are usually very high and the due diligence required is quite intense.

The message I am trying to convey is simply that financially challenged business owners should not be afraid to think about substitute or non-bank financing options. It’s a evenhandedly easy matter to learn what they are, how much they cost and how they work. Substitute financing is a much superior option than covering the challenges of growth or turnaround alone. It is a known fact that the vast majority of business failures are due to a demand of working capital—but it doesn’t have to be that way.

With a superior understanding of these different types of non-bank financing, you’ll be in a superior position to decide if they might be the answer to your financing challenges.