This preview shows page 1. Sign up to view the full content.
Unformatted text preview: This means that volume can fall by 22 percent, a relatively large
amount, before U-Develop ﬁnds 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
The excess of projected or
actual sales over the breakeven volume expressed as a
percentage of the break-even
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 ﬁrst. 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 ﬁxed costs) for U-Develop are entered. The proﬁt 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 ﬁnd
the volume associated with a given desired proﬁt 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 ﬁeld, enter the cell address for the target proﬁt calculation (B7).
for Success in College
The formula in cell B7 is: ((B3-B4)*B8)-B5.
3. In the “To value:” edit ﬁeld, enter the target proﬁt (in this example, the target proﬁt is
zero because we are looking for the break-even point).
4. In the “By changing cell:” edit ﬁeld, 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 sufﬁce.)
5. Click “OK” and the program will ﬁnd 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
would change Step 4 above to enter the cell for Price (B3) A P TﬁEnd the Ianswer ($0.66).N E Exhibit 3.6 1
9 Screenshot of
for CVP Analysis—
U-Develop Exhibit 3.7
U-Develop B Price
Fixed cost $ 0.60
$ 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
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.
- Spring '07