Page 47 -

USING EXCEL FOR BREAKEVEN ANALYSIS 27

CASE PROBLEM Uhuru Craft Cooperative, Tanzania

he Uhuru Craft Cooperative in Tanzania was scheme and is considering investing in an additional
T established a few years ago by a number of aid workshop where local women will be able to produce
agencies in an attempt to help the local economy traditional children’s dolls. The initial investment will be
diversify and increase income levels of the local com- around E25 000 for a new building and for sewing
munity. The community itself is largely subsistence- equipment that the local women will use. It’s estimated
based, has very low income levels, low levels of that it will cost around E10 per item to produce, market
education, poor quality housing. The Cooperative and ship the dolls to Europe. One FairTrade company
brings together local craftworkers – wood carvers, has already expressed an interest in buying the prod-
potters, weavers and so on, to produce a variety of uct indicating it would be prepared to pay around E30
craft products using traditional techniques. These per item. A second option under consideration is to
products are then sold on to FairTade retail compa- buy second hand sewing equipment which will reduce
nies across Europe for re-sale to consumers. The the initial investment to E17500. However, this will
local craftworkers are paid immediately by the Coop- push up the cost per item to E12.
erative and do not have to worry about marketing,
sales, transportation of their products as all of this is
Managerial Report
taken care of by the Cooperative. The Cooperative
operates on a commercial basis but any profit made Prepare a report for the Cooperative Manager. Your
by the Cooperative is ploughed back into the project report should include:
or into the local community. The Cooperative also
1 Information about the breakeven sales volume if
organizes training for local young people so they
new sewing equipment is purchased.
can develop their own craft skills. The project has
proved both popular and successful to such an 2 Information about the breakeven sales volume if
extent that expansion is now under consideration second hand sewing equipment is purchased.
and the Cooperative is looking for your help. The 3 Any other advice you can give that would help the
Cooperative is keen to bring more women into the Cooperative decide what to do.

Appendix 1.1 Using Excel for Breakeven Analysis

We introduced the Nowlin Plastics production example to illustrate how quantitative
models can be used to help a manager determine the projected cost, revenue and/or
profit associated with an established production quantity or a forecasted sales
volume. We now introduce spreadsheet applications by showing how to use Micro-
soft Excel to perform a quantitative analysis of the Nowlin Plastics example.
Refer to the worksheet shown in Figure 1.4. We begin by entering the problem
data into the top portion of the worksheet. The value of 3000 in cell B3 is the setup
cost, the value of 2 in cell B5 is the variable labour and material costs per unit and
the value of 5 in cell B7 is the selling price per unit. In general, whenever we perform
a quantitative analysis using Excel, we will enter the problem data in the top portion
of the worksheet and reserve the bottom portion for model development. The label
‘Models’ in cell A10 helps to provide a visual reminder of this convention.
Cell B12 in the models portion of the worksheet contains the proposed produc-
tion volume in units. Because the values for total cost, total revenue and total profit
depend upon the value of this decision variable, we have placed a border around cell
B12 and screened the cell for emphasis. Based upon the value in cell B12, the cell
formulas in cells B14, B16 and B18 are used to compute values for total cost, total
revenue and total profit (loss), respectively. First, recall that the value of total cost is

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.