The Current Profit and Loss Statement for Corgan Ltd - Example

Profit and Loss Analysis and Recommendations for Corgan Ltd. 1. Introduction This report presents an analysis of four options presented to improve the bottom line at Corgan Ltd., as well as the results of a sensitivity analysis which takes into account the possibility of changes in costs or profit objectives. As a baseline, the current profit and loss statement for Corgan Ltd. is shown in Table 1: Current P/L Statement Unit Price £ 55.00 Demand 12000 Unit Cost -£ 35.46 Monthly Fixed Costs -£ 240,000.00 Revenue £ 660,000.00 Variable Costs -£ 425,520.00 Profit -£ 5,520.00 Table 1: Current P/L Statement Four options have been presented to solve the problem of a monthly loss: 1. An increase of £30,000 per month in advertising (a fixed cost), resulting in 2,000 additional units sold per month. 2. A reduction of £5.00 in the unit price plus an increase of £50,000 per month in advertising, resulting in 12,000 additional units sold per month. 3. Package redesign resulting in a one-time additional fixed cost of £12,000 and an increase of £0.85 in unit costs, increasing the number of units sold per month by 2%, or 240 units. 4. Reducing unit costs by £5.00 per unit through increased automation, which will increase monthly fixed costs by £50,000 and result in 1,000 additional units sold per month. 2. Analysis There is one very significant limitation to the analysis of the four options that must be clarified at the outset. The forecast increases in the number of units sold under different conditions are unavoidably assumptions; they may be sound assumptions, but there nevertheless is a degree of uncertainty whether they are valid. For the purposes of the first part of this analysis it is assumed they are correct, but this cannot be definitely assured. Option 1: Increasing advertising expenditures by £30,000 with 2,000 additional units sold per month increases both fixed and variable costs, and results in a monthly loss of £6,440, as shown in Table 2: Item Sales per Unit Total Digital Memory Card 14000 £ 55.00 £ 770,000.00 Variable Costs 14000 -£ 35.46 -£ 496,440.00 Contribution Margin 14000 £ 19.54 £ 273,560.00 Fixed Costs 14000 -£ 20.00 -£ 310,000.00 Net Profit/Loss 14000 -£ 0.46 -£ 36,440.00 Table 2: Option 1 Option 1 is therefore not recommended. Option 2: Reducing the unit price to £50.00 and increasing advertising expenditures by £50,000 per month will, even if the assumption of a doubling of the number of units sold is correct, result in a very high loss and cannot be recommended, as shown in Table 3: Item Sales per Unit Total Digital Memory Card 24000 £ 50.00 £ 1,200,000.00 Variable Costs 24000 -£ 35.46 -£ 851,040.00 Contribution Margin 24000 £ 14.54 £ 348,960.00 Fixed Costs 24000 -£ 20.00 -£ 530,000.00 Net Profit/Loss 24000 -£ 5.46 -£ 181,040.00 Table 3: Option 2 Option 3: Option 3 requires a package redesign which will result in £12,000 of additional fixed costs for the first month, and £0.85 in additional unit costs. Option 3: Package Redesign (1st month) Item Sales per Unit Total Digital Memory Card 12240 £ 55.00 £ 673,200.00 Variable Costs 12240 -£ 36.31 -£ 444,434.40 Contribution Margin 12240 £ 18.69 £ 228,765.60 Fixed Costs 12240 -£ 20.00 -£ 256,800.00 Net Profit/Loss 12240 -£ 1.31 -£ 28,034.40 Option 3: Package Redesign (2nd month onward) Item Sales per Unit Total Digital Memory Card 12240 £ 55.00 £ 673,200.00 Variable Costs 12240 -£ 36.31 -£ 444,434.40 Contribution Margin 12240 £ 18.69 £ 228,765.60 Fixed Costs 12240 -£ 20.00 -£ 244,800.00 Net Profit/Loss 12240 -£ 1.31 -£ 16,034.40 Table 4: Option 3 Option 3 also results in a significant increase in losses, and therefore cannot be recommended. Option 4: Increasing fixed costs by £50,000 per month through automation is forecast to reduce unit costs by £5.00, and increase sales by 1,000 units per month. Under these conditions, the results are as shown in Table 5: Item Sales per Unit Total Digital Memory Card 13000 £ 55.00 £ 715,000.00 Variable Costs 13000 -£ 30.46 -£ 395,980.00 Contribution Margin 13000 £ 24.54 £ 319,020.00 Fixed Costs 13000 -£ 20.00 -£ 310,000.00 Net Profit/Loss 13000 £ 4.54 £ 9,020.00 Table 5: Option 4 Option 4 does result in a strong improvement in Corgan’s bottom line despite the increased expenditures, and can be recommended provided the assumptions about the increased sales, reduction in unit costs, and increase in fixed costs are all correct. Because these are variables, however, a sensitivity analysis must be conducted to determine, first of all, what would be the outcomes given unexpected changes in any of the price, cost, or volume inputs, and second, to determine whether the basic analysis is misleading, and the use of a more sophisticated function in the analysis would return the same or different results. 3. Sensitivity Analysis Sensitivity analysis is also sometimes called “what-if” analysis, and is a method of using a spreadsheet program such as Microsoft Excel to assess the change in outcomes if one or more variables are changed (Al Ghamdi, 2005, pp. 385-386). There are two ways to conduct the analysis in Excel, either through the use of the data tables function, or through a function called the Goal Seek command (Winston, 2007). Because spreadsheet analyses do present some risk of error (Panko, 1998), the latter, less complex analysis is preferred, and provides robust results for the current analysis while providing a table in which any user can change variables as desired to test different scenarios. To begin the analysis, the baseline current P/L statement is reorganised into a slightly different format to display all the relevant variables, as shown in Table 6: Unit Price £ 55.00 Demand 12000 Unit Cost -£ 35.46 Monthly Fixed Costs -£ 240,000.00 Revenue £ 660,000.00 Variable Costs -£ 425,520.00 Profit -£ 5,520.00 Table 6: Current P/L Statement The first test that is conducted is to determine how many units must be sold to reach a “break-even” bottom line, which is done by instructing the Excel program to change the “demand” (sales) variable to produce the “goal” of Profit = 0. At the current selling price and costs, the “break-even” point is reached with the sale of 12,283 units, as shown in Table 7: Break-Even (Profit = 0) Unit Price £ 55.00 Demand 12282.49744 Unit Cost -£ 35.46 Monthly Fixed Costs -£ 240,000.00 Revenue £ 675,537.36 Variable Costs -£ 435,537.36 Profit £ - Table 7: Current Break-Even Point Increasing the fixed costs for advertising by £30,000 as in Option 1 requires the sale of 13,818 units to reach the break-even point. This suggests that Option 1 might be a viable choice, contradicting the results of the basic analysis; the balance of 182 units, assuming the forecast volume of 14, 000 is correct, would result in a profit of £3,693. A. Increase Fixed Costs by £ 30,000 (Option 1) Unit Price £ 55.00 Demand 13817.80962 Unit Cost -£ 35.46 Monthly Fixed Costs -£ 270,000.00 Revenue £ 759,979.53 Variable Costs -£ 489,979.53 Profit £ - Table 8: Break-Even Analysis, Option 1 Option 2 suggests a profit as well using the “Goal Seek” analysis function, as the break-even point is reached at only 19,945 units: B. Increase Fixed Costs by £ 50,000 and Reduce Unit Price (Option 2) Unit Price £ 50.00 Demand 19944.97937 Unit Cost -£ 35.46 Monthly Fixed Costs -£ 290,000.00 Revenue £ 997,248.97 Variable Costs -£ 707,248.97 Profit £ - Table 9: Break-Even Analysis, Option 2 Option 3, however, remains unworkable as the 2% forecast increase in sales (i.e., from 12,000 to 12,240 units), remains below the number that must be sold to break-even: C. Increase Unit Costs and Monthly Fixed Costs, 1 Month (Option 3) Unit Price £ 55.00 Demand 13483.14607 Unit Cost -£ 36.31 Monthly Fixed Costs -£ 252,000.00 Revenue £ 741,573.03 Variable Costs -£ 489,573.03 Profit £ - C. Increase Unit Costs and Monthly Fixed Costs, Month 2+ (Option 3) Unit Price £ 55.00 Demand 12841.09149 Unit Cost -£ 36.31 Monthly Fixed Costs -£ 240,000.00 Revenue £ 706,260.03 Variable Costs -£ 466,260.03 Profit £ - Table 10: Break-Even Analysis, Option 3 Option 4 appears to be viable just as in the basic initial analysis, since the break-even point is actually below the number of units currently being sold each month: D. Increase Fixed Costs by £ 50,000 and Reduce Unit Cost by £ 5.00 (Option 4) Unit Price £ 55.00 Demand 11817.44091 Unit Cost -£ 30.46 Monthly Fixed Costs -£ 290,000.00 Revenue £ 649,959.25 Variable Costs -£ 359,959.25 Profit £ - Table 11: Break-Even Analysis, Option 4 Therefore, Option 4 is confirmed as the best alternative of the four options presented. For the sake of comparison, however, the following chart shows that slight increases in selling prices – which would not entail any changes to existing sales volume or costs – can generate profit as well, even making the arbitrary assumption of a 10% loss in volume for each £1.00 increase in price: Chart 1: Effect of Price Increase 4. Final Recommendation From the sensitivity analysis, Option 4, i.e., increasing automation and fixed costs by £50,000 per month with the resulting reduction in unit costs of £5.00 would result, if the sales volume does not increase, in a profit of £4,490.82 per month. The same profit could be achieved with a price increase of between £0.79 and £0.88 per unit, with no additional fixed costs. Therefore, these two choices are recommended as the options available to Corgan Ltd. References Al Ghamdi, S.M. (2005) “The Use of Strategic Planning Tools and Techniques in Saudi Arabia: An Empirical Study”. International Journal of Management, 22(3), 376-395. Panko, R.R. (1998) “What We Know About Spreadsheet Errors”. Journal of End User Computing, 10(2), 15-21. Winston, W.L. (2007) Microsoft Excel Data Analysis and Business Modeling, 2nd ed. Microsoft Publishing. Read More