Accounting, Financial, Taxation

In real estate properties that are intended for rent or sale after development is completed, leasing and selling activities generally occur throughout the acquisition, development, and construction phases of a project. Successful pre-leasing and pre-selling efforts are evidence of a project ’ s viability, and funds received from buyers are often used to finance a project ’ s development. Commissions; legal fees; closing costs; advertising costs; and costs for grand openings are examples of costs to sell or rent; however, based on the type of real estate property, leasing and sales activities — and related costs incurred — may vary.

Costs Incurred To Sell Of A Real Estate Project

Costs incurred to sell a real estate project are generally substantial. Depending on the type of selling costs incurred, they are accounted for in one of three ways:

1. Included in project costs
2. Deferred
3. Expensed

What selling cost to be included in a project? which selling cost should be deffered? what to be expensed?  read on…….

[1]. Selling Costs To Be Included In Project Costs

Selling costs are included in project costs if all of the following criteria are met:

1. They are reasonably expected to be recovered from the sale of the project or from incidental operations.
2. They are incurred for tangible assets that are used directly throughout the selling period to aid in the sale of the project, or services that have been performed to obtain regulatory approval of sales.

Examples of costs that generally qualify as project costs are:

1. Costs of model units and their furnishings
2. Costs of sales facilities
3. Legal fees for the preparation of prospectuses
4. Costs of semi-permanent signs

[2]. Selling Costs To Be Deferred

FASB Statement No. 67 provides for the deferral of certain selling costs. It is important to note that deferred selling costs are not part of project costs. If the percentage-of-completion method were applied, the incurrence of selling costs would not increase a project’s percentage of completion. Additionally, deferred selling costs are not part of qualifying expenditures for interest capitalization.

Selling costs are accounted for as prepaid costs; that is, they are deferred if they meet the following criteria: They must be directly associated with successful sales efforts, and their recovery must be reasonably expected from sales. FASB Statement No. 67 provides for the deferral of such selling costs until the related profit is recognized.

If profit is recorded under the accrual method of accounting, a deferral of selling costs is generally not necessary, as the selling costs are incurred in the period of sale. For example: a seller may incur brokerage commissions at the time of closing.

If profit from a real estate sale is recognized under a method of accounting other than the accrual method, such as the deposit or installment method. Paragraph 18 of Statement 67 provides for cost deferral until the related profit is recognized.

If a sales contract is canceled or if the receivable from a real estate sale is written off as uncollectible, any related unrecoverable deferred selling costs are charged to expense.

[3]. Selling Costs To Be Expensed

Costs that do not meet the criteria for capitalization as project costs or for cost deferral are expensed as incurred.

Costs Incurred To Rent A Real Estate Project

Costs to rent a real estate project under operating leases fall in one of two categories:

1. Initial direct costs; and
2. Other than initial direct costs.
3. Costs to rent projects under direct financing or sales - type leases are treated like costs to sell.

FASB Statement No. 67 does not apply to initial direct costs. Initial direct costs are incremental direct costs incurred by the lessor in negotiating and consummating leasing transactions, and certain costs directly related to specified activities performed by the lessor. The accounting for such costs is provided in FASB Statement No. 13, Accounting for Leases.

Costs other than initial direct costs to rent real estate projects under operating leases that are related to and are expected to be recovered from future rental operations are deferred (capitalized). Examples of such costs are costs of:

1. Model units and their furnishings
2. Rental facilities
3. Semi-permanent signs
4. Grand openings
5. Unused rental brochures

Deferred rental costs that are directly related to a specific operating lease are amortized over the lease term. Deferred rental costs not directly related to revenue from a specifi c operating lease are amortized over the period of expected benefit. The amortization period of capitalized rental costs begins when the project is substantially complete and held available for occupancy. Any amounts of unamortized capitalized rental costs associated with a lease or group of leases that are estimated not to be recoverable are charged to expense when it becomes probable that the lease or group of leases will be terminated.

The Advertising Cost

Costs of advertising, which include the costs of producing advertisements (such as the costs of idea development, artwork, printing, and audio and video production) and communicating advertisements that have been produced (such as the costs of magazine space, television airtime, billboard space, and distribution costs) are accounted for based on the provisions of SOP 93 - 7, Reporting on Advertising Costs.

Costs of advertising are expensed, either as incurred or the first time the advertising takes place (e.g., the first public showing of a television commercial or the first appearance of a magazine advertisement) with the following two exceptions provided for in paragraphs 26 and 27 of that SOP:

Direct-response advertising whose primary purpose is to elicit sales to customers who could be shown to have responded specifically to the advertising and that results in probable future economic benefits. Costs of direct response advertising that are capitalized should be amortized over the period during which the future benefits are expected to be received.

Expenditures for advertising costs that are made subsequent to recognizing revenues related to those costs. For example: a company may assume an obligation to reimburse its customers for some or all of the customers’ advertising costs (cooperative advertising). In that scenario, revenues related to the transactions creating those obligations are earned and recognized before the expenditures are made.

For purposes of applying SOP 93-7, those obligations should be accrued and the advertising costs should be expensed when the related revenues are recognized.

Example - Selling Cost

Developer X sells developed lots. The buyers of the lots have made only nominal down payments, and X has determined that the application of the deposit method of accounting is appropriate. X intends to defer the following five types of costs incurred in connection with X’s efforts to sell the lots:

1. Wages and commissions paid to sales personnel, and related insurance, taxes, and benefits for sales personnel

2. Costs for the corporate sales department

3. Radio and newspaper advertising expenses

4. Telephone, hospitality, meals, and travel costs for customers and prospective customers

5. Title insurance and professional fees incurred in the sale

X intends to defer these costs, as they are incurred in connection with D’s efforts to sell the lots. The question is: “Is a deferral of these costs appropriate?” Here is the answer set:

1. To the extent the costs for wages and commissions to sales personnel relate directly to successful sales efforts, their deferral (together with the deferral of any related insurance, taxes and benefits) is appropriate.
2. Costs of the corporate sales department are not directly associated with successful sales and should not be deferred.
3. For advertising costs, the guidance in SOP 93-7 should be followed.
4. To the extent that telephone, hospitality, meals, and travel costs for customers and prospective customers are incurred directly for successful sales efforts, their deferral is appropriate.
5. Title insurance and professional fees are incurred directly in connection with the sales; their deferral is appropriate.

The AICPA has issued SOP 04-2, Accounting for Real Estate Time-Sharing Transactions, which includes guidance relating to the deferral of costs for the sale of time-sharing intervals. That guidance may provide additional insights when considering what types of selling costs to defer.

One is sometimes called upon to make sales forecasts or verify those made by the sales and marketing departments. One of the better approaches for doing this is to extend the past history of sales volume forward into the periods being projected. Though this method of prediction is like trying to drive a car by looking in the rear view mirror, it is still one of the best tools available, as long as it is supplemented by detailed conversations with the sales staff to see what is really happening in the marketplace.

There are two formulas provided by Excel that result in forecast information:

The first, and simplest, is the TREND command. This one superimposes a trend line on an existing set of time-sequenced data points to arrive at an expected sales level for a specified future period.

To illustrate the command, we return once again to the income statement shown earlier in my previous post [Financial Statement Proportional and Ratio Analysis with Excel Spreadsheet]. We will use a new worksheet within the same spreadsheet, called Trend, and reference in it all of the monthly sales figures from the previous income statement. This is shown in below figure, along with a graph that shows the added trend line.

In the above worksheet, we already know all sales data points from January through August, and want to calculate a trend line that extends an additional month to give us a prediction for September sales. Accordingly, in the table of months and historical sales figures noted in the Trend worksheet, there is an additional cell next to the “August” sales period. In that cell we enter the following formula:

TREND(B4:B11,A4:A11,A12)

Though it looks complicated, this is a relatively simple command. The trend line is based on the data points contained in cells B4 through B11 for the date ranges contained in cells A4 through A11. The date for the period to be forecast is noted in cell A12. The formula generates a number that is the extension of the trend shown by the previous data elements and will deposit this number in the B12 cell.

Another way to state the formula is to ignore the dates and just ask for the next number in sequence. The formula, based on the previous example, looks like this:

TREND(B4:B11, ,{9})

Under this variation, we are using the same set of data points, but ignoring the dates (hence the two commas in the formula with no data in between), and a number in brackets which represents the trend for the ninth number in the sequence of data elements. Since the original set of data only included eight data elements, this will be the next revenue figure after the last month of actual data. If the requested trend were for the month of December, the number in brackets would change to {12}, since this would represent the twelfth data point in the series.  However, to add a trend line overlay to the presented data, click on the completed graph, move the cursor to the revenue line on the chart, and press the right mouse button. Then click on the Add Trend line option, and pick from six available types of trend lines that can be added to the graph.

The second type of forecasting tool provided by Excel is REGRESSION ANALYSIS. This is a powerful tool for determining the trend line that best fits a disparate set of data, and is most useful when dealing with a set of numbers that are widely scattered, and show no apparent pattern.

In essence, the method determines the trend line that minimizes the sum of all squared errors between all data points and the line. Rather than delve into the formula for this method, it is easiest to plot the data elements and proceed immediately to a graph, on which Excel will superimpose a regression trend line.

In the above figure, the second half of the presentation includes the regression analysis. In it, we have plotted twenty data items for twenty periods that are wildly different from each other, and have no apparent pattern. The first step in the analysis is to create a graph. Then use the same steps just described for the TREND analysis to add a trend line to the chart. The result is shown in the bottom half of the above figure, where we find that there is a slight upward trend line to the data used to compile the regression trend line.

Of the two methods presented, the TREND formula is of the most use, for one will find that most data being analyzed in the financial arena has a lengthy and steady trend line of data. Only for the most unusual analyses, involving wildly disparate data items, will the regression analysis be necessary.

The following list shows some of the trend lines that you may consider and try when measuring a company’s operations using the TREND formula in excel spreadsheet:

1. Trend of amount of utilized storage space. This trend can spotlight many contributing problems, such as obsolete inventory, returned goods, scrapped parts, and excess finished goods.
2. Trend of cost of distribution channels. This analysis should include the net margins earned on each sales channel.
3. Trend of cost of freight. A company may extend into new geographical regions without considering the cost of shipping product into those areas. This analysis should be conducted by region to spot such problems.
4. Trend of cost of sales calls. In particular, one should compare the relationship between the sales received from high-volume customers and the cost of making sales calls to them. It is common to find a few high-maintenance customers who are not worth the sales effort from a cost-benefit perspective.
5. Trend of design cycle iterations required. Increases in the number of design iterations indicate serious problems in the design process, while a drop in the trend indicates good design management (though it can also indicate that an insufficient number of product reviews are being made).
6. Trend of direct labor rates. This can be used in comparison to a market survey to see if company pay rates are varying from those offered by other firms. After a layoff, it can change suddenly if people whose pay varies substantially from the mean have been laid off.
7. Trend of engineering change notices issued. A jump in the number of change orders can signal the presence of quality problems, as well as a likely increase in inventory, as some components are rendered obsolete.
8. Trend of gross margins. Investigate margins by both product and volume.
9. Trend of inventory, bill of material, and labor routing accuracy. These three items require very high levels of accuracy in order to operate a production planning system. Any drop in these trends will likely result in production snafus.
10. Trend of number of product options per product family. Changes in this trend can refer to a deliberate attempt at product proliferation as a marketing strategy (if it increases) or a rationalization of engineering tasks (if it declines).
11. Trend of overhead capitalization. If the amount of overhead shifted into a capital account, such as inventory, is changing, this may signal a deliberate attempt by management to alter the reported level of earnings.
12. Trend of pricing. Track the sensitivity of sales volume to changes in pricing.
13. Trend of product returns. A sudden change in this trend can signal a quality problem in the product, or that the distribution pipeline is full.
14. Trend of ratio of overhead to production labor. Overhead can balloon rapidly, and this is a prime early indicator of the problem.
15. Trend of return on shareholders’ equity and return on assets. A significant decline in these measures signals reduced cash flow, more difficult borrowing covenants, and a host of other problems, such as reduced margins and increased expenses.
16. Trend of sales quotas. This trend can be compared to actual sales per salesperson to see if the quotas are unrealistically high or low.
17. Trend of sales volume. Examine sales volume by both territory and product.
18. Trend of utilized plant capacity. This is a good indicator of the need for additional production shifts, increased maintenance, or more facilities.

There are several tools used to conduct financial analysis. One is a database of accounting information, in which an analyst can roam for days, tracking down the details regarding when specific transactions have taken place, why they occurred, and the likelihood of their happening again. However, analysts rarely descend straight into the depths of the accounting database without first using some more simple means for determining what problem has arisen, which yields clues regarding where in the database to search. This higher-level information is obtained by using ratio and trend analysis to pinpoint the issue. To get this information, a calculator, pencil, and paper are sufficient, but also very time consuming and prone to error. Instead, an electronic spreadsheet is the best method.

For you who are well financed, you may purchase such instant Excel spreadsheet analysis tools. But for you who are willing to invest a lil time and learn, you may save some bucks. In this post, we review how to use such a spreadsheet—in this case, the Microsoft Excel spreadsheet.

The formulas presented in this post are by no means difficult. I will do my best to confin to the simplest and most understandable spreadsheet commands, and avoids the use of complicated macros. The discussion focuses on using spreadsheets for: financial statements. In each case, it is noted how Excel can be used to solve a problem, and then a sample situation is provided.

A key issue that is noted throughout this post is the difference between a spreadsheet and a worksheet. In Excel, a spreadsheet can have a number of interlinked layers known as worksheets. When an entry is made in one worksheet, it can be referenced by other worksheets in the same spreadsheet. This is a preferable approach to using Excel for financial analysis, since one can separate the data being analyzed in one worksheet, ratios in another, and graphics in yet another worksheet – but with formulas linking all of them together. In the examples used, nearly all of the analysis is done on one spreadsheet that contains a half-dozen worksheets.

Financial Statement Proportional Analysis

Proportional analysis is simply converting all of the numbers in an income statement and balance sheet into percentages, so that they can be compared over time to see what differences arise. By conducting this analysis, one can see if there are trends in revenues, costs, assets, or liabilities that may require further analysis or investigation.

When using Excel to conduct a proportional analysis of a financial statement, one must first input the income statement for each period into the worksheet, so that the proportional analysis calculation will appear below it or on a separate worksheet. Below figure, a simplified income statement has been entered in the cells at the top of the worksheet.

For each line item in this top section, there is a formula entered in the replicated income statement at the bottom of the screen that divides each expense line item by the revenue figure, resulting in a percentage of sales for each item. For example: the materials cost proportion for the month of January is calculated with the following formula, which is entered in cell B15:

B5 / B$4

Since the spreadsheet contains the income statement for multiple months, the resulting proportional analysis becomes very useful for finding any trends in the expenses being incurred over the course of the year.

The income statement proportional analysis used in the preceding example would be of great use to management in determining why its profits are not increasing along with its evident sales growth. In the example, sales increase from $1,200 in January to $1,400 in August, but profits drop by $9. Why? By perusing the proportional analysis, it is an easy matter to see that the cost of materials has dropped as a percentage of sales, which may reflect excellent purchasing, design, or production work to lower these costs. For the answer to why profits have dropped, we must look lower in the spreadsheet. The direct labor cost as a proportion of sales has risen, so this is an obvious target area for further analysis. However, the overall gross margin percentage has only dropped by one percent over the time period being analyzed, so there must be more trouble further down in the income statement.

Sure enough, the administrative expenses line item reveals a three percent jump in costs. Accordingly, anyone using this analysis would conclude that the trouble has arisen in the direct labor and administrative areas, and that the materials expense requires no further analysis.

Though this type of analysis is an excellent way to hone in on key areas, it is rarely the final analysis conducted, since it does not reveal enough information. Also, it is not sufficient if there are many operating divisions rolled into the income statement. In these cases, it is best to create a number of separate spreadsheets, one for each division, and conduct the analysis on each one, thereby yielding a greater level of detail regarding problem areas.

The same proportional analysis can be applied to the balance sheet. In the next figure, one can manually enter a simplified version of the balance sheet at the top of the spreadsheet, which produces a set of percentages at the bottom. The asset percentages sum to the grand total of all assets, while the percentages for liabilities and equity sum to the total for those two categories.

As was the case for the proportional analysis of the income statement, the cell formula is extremely simple. In the above figure, the percentage for accounts payable in April is calculated by dividing the total accounts payable dollars, located in cell E10, by the total of all liabilities and equity for that month, which is located in cell E14.

What does the proportional analysis of the balance sheet tell us? To use the example, there is a clear increase in the fixed asset investment, which requires the use of all cash, as well as an increased debt load, which reaches its height in May, after which cash flow from operations is used to gradually draw down the level of debt. The only other trend of note is that inventory levels are declining, which indicates either excellent logistics practices or a decline in sales that no longer requires such a large supporting base of inventory. Consequently, a great deal can be discerned by reviewing a proportional balance sheet analysis.

Financial Statement Ratio Analysis

Perhaps the most common use of an electronic spreadsheet is to conduct a ratio analysis of the income statement and balance sheets. Typically, a summary form of the income statement and balance sheet are located at the top of the worksheet, with ratios located at the bottom that are derived from these two reports. By using this approach, one can quickly enter the summary-level financial information for the current reporting period and then see the related ratios appear at the bottom of the worksheet. In a few moments, he or she has access to a rough analysis of company operations. If there are entries for the financial results of previous months, then one can also see trend lines in ratio results that extend through to the current reporting period.

As an example of the types of ratio analysis one can use in a worksheet, we will use the “income statement and balance sheet” shown earlier, in the first and second screenshot above. A series of ratios are noted in below figure that are derived from those statements.

In the above figure, there are several tabs itemized at the bottom of the worksheet. Each one represents another spreadsheet that is clustered into the same workbook. The first tab, entitled “IS“, contains a spreadsheet version of the income statement.

The second tab, entitled BS, contains a spreadsheet version of the balance sheet. The ratios shown in the Figure are compiled by referencing the cell locations in these two spreadsheets and listing the result on the current Ratios spreadsheet.

The formulas behind the ratios in the above figure are not shown, so the same spreadsheet is laid out differently in below figure to provide this information. In this example, we have eliminated the formulas for all but the month of January, and then listed each formula in full. For example, the first ratio is the Quick Ratio, which compares easily liquidated assets to current liabilities. To obtain this information, the cell entry goes to the “BS” spreadsheet and adds together cells B4 and B5, which contain the cash and accounts receivable figures for the month of January. The formula then divides the sum by the accounts payable and accrued liabilities amounts, which are located on the same spreadsheet in cells B10 and B11.

Further down in the list of ratios are ones that are built upon the income statement. For example: the Return on Sales percentage is derived by referencing the profit figure for January, which is located in cell B11 in the IS spreadsheet and dividing by total sales, which is located in cell B4 in the same spreadsheet. Finally, we can mix references to both the IS and BS spreadsheets in the same ratio formula.

For example: mto arrive at the return on equity, the formula takes the profit for January, which is located in cell B11 in the IS spreadsheet, annualizes it by multiplying by 12, and divides it by the equity figure, which is located in cell B13 in the BS spreadsheet. Thus, we can mix cell references from a variety of spreadsheets in order to arrive at a centralized set of ratios that can be stored in a single spreadsheet location.

Automated Ratio Result Analysis

If there are a great many ratios linked to a set of financial statements, one may want to save time in reviewing them by having the spreadsheet issue a warning message for those ratios that fall outside a preset parameter. Another reason for using this approach is when a lending institution places constraints on a company by requiring minimum levels for certain ratios, such as a current ratio of at least 2:1, or a debt/equity ratio of no higher than 30%. In either case, a formula that presents a YES/NO or GOOD/BAD result can save some time.

A simple IF formula will create an automated ratio result. To continue with the example used previously in the above figure, we will add three rows to the analysis. Under the Balance Sheet Ratios section, add a row entitled “Meets Quick Ratio Covenant” This is a YES/NO determination based on the quick ratio being greater than 0.9, and will appear in row 8. The formula for the month of January will be:

IF(B5>.9,”Yes”,”No”)

Under the Income Statement Ratios section, add a row entitled Meets Gross Margin Covenant. This is a YES/NO determination based on the gross margin being greater than 43% and will appear in row 14. The formula for the month of January will be:

IF(B18>21,“Yes”,“No”)

All of these new formulations are shown in the ratios Figure on the next screenshot. In the Figure, one can quickly skim through the various months of results to determine the occasions when covenants have been violated. Setting up the IF statements that drive these automated ratio results are quite simple, and can help to some extent in the task of sorting through large quantities of ratios.