Spreadsheet Case 1: Accounting & Sales Analysis at Smoke Industries
Download Spreadsheet File for This Case: SMOKE.XLS (66 Kb)
Breakeven Analysis - Part A (Basic)
Smoke Industries is a large organization which manufactures and sells smoke detectors and other safety items for homes and businesses. This spring, the company is considering an introduction of a new smoke detector model. Its Research and Development department has developed two basic variations of this new model, currently code named SilverWhistle and GoldWhistle. The SilverWhistle smoke detector model is sold primarily to housing contractors for installation in new homes. The GoldWhistle model is a slightly more enhanced detector and is sold to wholesalers for retail distribution. These models require different production processes and command a slightly different selling price. Because of market and capacity considerations, Smoke Industries does not wish to produce both products. The level for demand for either product is uncertain but is expected to be between 50,000 and 75,000 units per year.
The following information relates to the two proposed products. Sales commission will be 10 percent of sales and administrative fixed expenses will be $200,000 for the SilverWhistle model and $400,000 for the GoldWhistle model. The Marketing department wants to determine breakeven points for SilverWhistle and GoldWhistle and also calculate the indifference point between the two models.
This is a classic business problem-the company must determine how many units of a potential product (in this case, a smoke detector) must be sold each year to earn a specific income or profit. The solution to this problem is known as breakeven analysis. Breakeven analysis determines the breakeven point-the number of units of a product that must be sold to yield no profit and incur no loss. In other words, it is the point at which total costs and total revenue are exactly equal. Breakeven analysis examines the interrelationship between sales, production costs, and revenue to determine the sales volume and the specific price per unit that the company must attain to make a profit on a product. Any units sold beyond the breakeven point represent profit, and a sales volume below the breakeven point result in a loss.
In order to establish the breakeven point, a company must examine its operating costs, both fixed costs and variable costs. Fixed costs remain the same in total no matter how many units of a product are made or sold. As the volume of production increases, the total fixed cost does not increase. Conversely, a variable cost depends upon the number of units produced, thus increasing as production expands, or decreasing as production subsides.
Once the product's costs have been determined, the contribution margin per unit must be calculated. This is the difference between the selling price per unit and the variable costs per unit. It is the amount that each unit sold contributes first toward covering fixed costs and then toward profit once the breakeven point is reached. Once both of these values are known, you can calculate the breakeven point. Redefined, the breakeven point is calculated as the number of times the contribution margin must be generated to cover fixed costs (e.g., breakeven point in units = total fixed costs/contribution margin).
The indifference point is the unit sales level at which the profits of two products are equal. It is computed as follows:
Figure 1 Comparison Spreadsheet for Smoke Detector Models
Cost-Volume-Profit Analysis - Part B (Intermediate)
Cost-volume-profit analysis is a decision tool that can be used as input for decisions about the profitability of individual products and for the decisions comparing profitability of alternative products or methods of production. The analysis focuses on cost behavior in relation to changes in activity level. Cost-volume-profit analysis is useful because, for products with different cost structures, relative profitability depends on the activity level. That is, a product with a high level of fixed costs will require a higher sales activity level to generate a profit than will a product primarily with variable costs. Cost-volume-profit analyses facilitate the comparison of alternatives with different fixed and variable cost structures. In addition, these cost-volume-profit analyses can be effectively summarized graphically.
Two useful computations that are elements of cost-volume-profit analysis are the breakeven point and indifference point. In part A of this case, you helped the Marketing management of Smoke Industries perform breakeven analysis by creating a worksheet that calculated the break-even point and indifference point of two different smoke detector models. Management would then use this information to determine which model variation the company should sell.
Breakeven analysis can also generate pro forma (or projected) income statements which assume different levels of sales to show revenues from sales, costs, and profits. This financial statement can then be used to generate a line graph of unit sales, fixed costs, total costs, and revenues. The breakeven point on the graph is the intersection of total costs and revenues.
In this part of the case, you will go one step further by adding income projections for both smoke detector models for different activity levels and use this information to create line graphs which emphasize the break-even and indifference points. Expand this worksheet by completing the following tasks:
Figure 2 Income Projections for Smoke Industries
Creating and Analyzing Financial Statements - Part C (Advanced)
Like many companies, Smoke Industries is struggling to compete in the marketplace. That is one of the reasons why they are introducing a new smoke detector-to keep pace with customer demand. It is now the end of the fiscal year for Smoke Industries and the Board of Directors is meeting to analyze and critique the company's performance during the last year. They are each holding a copy of the annual financial statements.
The chairman of the board is concerned about the company's performance. He wants to spot any changes that are taking place within the organization (such as sales growth and gross margin trends) and highlight the direction of the changes. He also wants to see the relationships and trends found in the financial statement data in order to draw his own conclusions and act accordingly. Since all of the board members are each major stockholders of the company, they may even need to analyze the company's financial statements and compare its earnings history with that of its competitors to decide whether or not to hold onto the stock or sell it. In summary, after scanning these documents, they want to analyze the financial results for this year to determine whether the company is on track.
In this part of the case study, you will practice creating and formatting worksheets by creating two financial statements: the Balance Sheet and the Statement of Income and Retained Earnings. You will also perform various types of analyses on the data contained in these sheets to help answer the executive's questions about the company's performance.
Figure 3 The Balance Sheet for Smoke Industries
Figure 4 The Statement of Income and Retained Earnings for Smoke Industries
Now that you have created the financial statements for Smoke Industries, you are ready to perform financial analysis on them. Trend percentages, sometimes referred to as index numbers, are used for comparison of financial information over time to a base year. They are calculated by selecting a base year, assigning a weight of 100 percent to the amounts appearing in the base year financial statements, and expressing the amounts shown on the other years' financial statements as a percentage of base year amounts. In this part of the case study, you will provide the Board with trend information so they can perform various analyses on the data and determine the company's current and future performance. You will create a worksheet which highlights some key financial trends over the last four years, calculate the trend percentages based on the 1993 base year, and then display this information visually in a graph.
Another type of analysis that the Directors will perform is Ratio Analysis. This means studying the logical relationships found in a company's financial statements. The dollar amounts of the related accounts are usually established in fraction form and generally called ratios. Each ratio is typically categorized as a liquidity, equity or solvency, profitability, or market ratio. You will perform ratio analysis in this case study by creating three ratios: the current ratio, the equity ratio, and the return on assets ratio. The current ratio indicates the ability of a company to pay its current liabilities from current assets and, thus shows the strength of the company's working capital position. It is usually stated in terms of the number of dollars of current assets to one dollar of current liabilities (e.g., 1.5:1 means that the company has $1.50 of current assets for each $1 of current liabilities). In general, the higher the current ratio, the better position the company is able to pay its current debts.
The owners' equity ratio is an equity or solvency ratio and indicates the financial structure of the company by showing the relationship of debt and equity financing. Thus, if a company's equity ratio is 69% (or .69:1) in one year, and 73% (or .73:1) the next year, this increase may be desirable for creditors since it indicates the existence of a protective buffer in the event the company suffers a loss. It may or may not be attractive to the stockholders.
The return on assets ratio is a profitability ratio which measures income performance without regard to sources of assets. It is a measure of managerial effectiveness. This ratio is designed to show the earning power of the company as a bundle of assets. By disregarding both nonoperating assets and nonoperating income, the ratio measures the profitability of the company in executing its primary business.
Complete steps 3-8 to perform analysis on the financial statements.
MHHE Home | About MHHE | Help Desk | Legal Policies and Info | Order Info | What's New | Get Involved