Margin of safety 1 percentage the excess of projected

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: This means that volume can fall by 22 percent, a relatively large amount, before U-Develop finds itself operating at a loss. cor50782_ch01_001-072.indd 1 lan27114_ch03_080-109.indd 89 ✓ Related Resources See pages 000 to 000 of the Annotated Instructor’s Edition for general suggestions related to the chapters in Part One. margin of safety 1 percentage The excess of projected or actual sales over the breakeven volume expressed as a percentage of the break-even volume. 10/5/09 11:09:29 PM 10/22/09 10:33:59 PM REVISED PAGES Part II 90 Cost Analysis and Estimation CVP Analysis with Spreadsheets L.O. 3 Use Microsoft Excel to perform CVP analysis. PART 1 It is important to be able to do CVP analysis and understand the relations, so it is important to work examples and do problems by hand at first. However, a spreadsheet program such as Microsoft Excel® is ideally suited to doing CVP routinely. Exhibit 3.6 shows a Microsoft Excel worksheet for U-Develop. The basic data (price per unit, variable cost per unit, and total fixed costs) for U-Develop are entered. The profit equation (or formula) is shown in the formula bar of the spreadsheet. Once the data are entered, an analysis tool such as Goal Seek can be used to find the volume associated with a given desired profit level. In the left side screenshot of Exhibit 3.7, the problem is set up as follows: Orientation 1. With the spreadsheet open, choose the “Data” tab and select “What-If Analysis” from Preparing and Organizing Yourself the ribbon. Then select “Goal Seek” from the drop-down box. 2. In the “Set cell:” edit field, enter the cell address for the target profit calculation (B7). for Success in College The formula in cell B7 is: ((B3-B4)*B8)-B5. 3. In the “To value:” edit field, enter the target profit (in this example, the target profit is zero because we are looking for the break-even point). 4. In the “By changing cell:” edit field, enter the cell address of the volume variable ($B$8). (The 5,000 volume in cell B8 in Exhibit 3.6 is only a placeholder; any number will suffice.) 5. Click “OK” and the program will find the break-even volume as shown in the right side screenshot of Exhibit 3.7. Although this spreadsheet is extremely simple, it can easily be edited to analyze alternative scenarios, so-called what-if analyses. For example, we could ask, “Given that I expect to sell 5,000 prints, what price do I need to charge to break even?” In this case, we C H and RS N PART O would change Step 4 above to enter the cell for Price (B3) A P TfiEnd the Ianswer ($0.66).N E Exhibit 3.6 1 2 3 4 5 6 7 8 9 Screenshot of Spreadsheet Program for CVP Analysis— U-Develop Exhibit 3.7 Screenshot of Spreadsheet Analysis Tool—Goal Seek 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A U-Develop B Price Variable cost Fixed cost $ 0.60 $ 0.36 $ 1,500 Profit Volume $ (300) 5,000 Set cell: B7 To value: 0 By changing cell: $B$8 cor50782_ch01_001-072.indd 1 lan27114_ch03_080-109.indd 90 OK 1 Price Variable cost Fixed cost...
View Full Document

This note was uploaded on 02/07/2014 for the course MIS 304 taught by Professor Mejias during the Spring '07 term at Arizona.

Ask a homework question - tutors are online