Spreadsheet Case 1: Accounting & Sales Analysis at Smoke Industries

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:

1. Design and create a spreadsheet which computes breakeven points and the indifference point for the SilverWhistle model and the GoldWhistle model. Use Figure 1 to assist you.

2. Format the worksheet using either built-in spreadsheet formatting templates or your own ideas. Be sure to verify the results. Print the spreadsheet, then save it in a new workbook, SMOKE. Analyze and evaluate the results of your work. Which smoke detector should the company sell?

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:

1. Set up projected income for the SilverWhistle and GoldWhistle model, similar to Figure 2. Prepare the projected income statements for four activity levels - 25,000; 50,000; 75;000; and 100,000 units. All the computations should be based on the Sales and Cost data provided in Part A of this case. Be sure to format and test the worksheet.

2. Create two line graphs to illustrate the breakeven points for the two models. The two variables plotted are the Total Costs and Total Revenues.

3. Create another graph to illustrate the Indifference point between the two models. The two variables plotted are Total Profit for the SilverWhistle model and Total Profit for the GoldWhistle model.

4. Print the spreadsheet and the three graphs.

5. Write a one page memo to management interpreting the results of your analyses. This memo should include a summary of the results of your work thus far on the business problem at Smoke Industries and your recommendations to management based on those results. The recommendations should not merely restate the computer results, but instead be written in nontechnical language.

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.
1. Select a new worksheet in the Smoke Industries workbook and create the Balance Sheet, as shown in Figure 3. Be sure to create the formulas, as shown in the lower half of the figure.

2. Select another sheet in the same workbook and create the Statement of Income and Retained Earnings, as shown in Figure 4. Be sure to create the formulas, as shown in the lower half of the figure. Format the worksheets according to the figures with underlines, currency formatting, and fonts.

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.
1. Select a new worksheet in the Smoke Industries workbook and rename it Trend Analysis. Create the information, as shown in the upper part of Figure 2.5. For the 1996 column, copy the appropriate information from the Statement of Income and Retained Earnings and link it to this worksheet. Be sure to create the formulas, as shown below:

2. With 1993 as the base year, you can calculate Sales trend percentages for each year by dividing that year's sales by the 1993 Sales amount. You can do the same for the other trends of Cost of Goods Sold, Gross Margin, Operating Expenses, and Net Income before Taxes. Scroll down a few rows in the worksheet and create the table of resulting trends, as shown in the lower part of Figure 5. Create the trend for the Sales row, then copy the formulas to the other rows. Be sure to use absolute references before you copy the formulas.

3. Create a line graph of the Gross Margin trend over the four-year period. Be sure to format the graph and add a chart title, axes titles, and any other appropriate information.

4. Analyze the results of the worksheet data and graph. What does it tell the Board of Directors about the Gross Margin Rate? If it is declining, it might be a signal that trouble lies ahead in lower income or actual losses. If it is stable or increasing, it might be a clue that an increase in sales or income is probable.

5. Select another sheet in this workbook and rename it Ratio Analysis. According to Figure 6, create the following ratios to help the directors determine the financial status of the company. Be sure to link the numbers from the Balance Sheet and Statement of Income and Retained Earnings, as appropriate.

6. Analyze these ratios and compare them to the previous year. What do they say about the financial condition of Smoke Industries?

Figure 5 Performing Trend Analysis at Smoke Industries

Figure 6 Performing Ratio Analysis at Smoke Industries

Begin a search: Catalog | Site | Campus Rep