Microsoft Office Tutorials and References
In Depth Information
is raised to 4.5 million. Obviously, there are many more questions that could be
asked and Ram will provide a formal critique of Julia’s workbook and analysis that
is organized around the 5 best practices. Julia hopes that by sending the workbook
to Ram he will suggest changes to improve the workbook.
1.6.2 Ram’s Critique
After considerable examination of the worksheet, Ram gives Julia his recommen-
dations for a “spreadsheet makeover” in Table 1.3. He also makes some general
analytical recommendations that he believes will improve the usefulness of the
Table 1.3 Makeover recommendations
General Comment—I don’t believe that you have adequately captured the uncertainty associated
with the problem. In most cases you have used a single value of a set, or distribution, of possible
values—e.g. you use 3,500,000 as the Sales Volume. Although this is the most likely value,
2,000,000 and 5,000,000 have a combined probability of occurrence of 35% (a non-trivial
probability of occurrence). By using the full range of possible values, you can provide the user
with a view of the variability of the resulting “bottom line value-Net Income” in the form of a
risk proﬁle . This requires randomly selecting (random sampling) values of the uncertain
parameters from their stated distributions. You can do this through the use of the RAND()
function in Excel, and repeating these experiments many times, say 100 times. This is known as
Monte Carlo Simulation . (Chaps. 7 and 8 are devoted to this topic.)
P1—The Workbook is simply a single spreadsheet. Although it is possible that an analysis would
only require a single spreadsheet, I don’t believe that it is sufﬁcient for this complex problem,
and certainly the customer will expect a more complete and sophisticated analysis.— Modify the
workbook to include more analysis, more documentation, and expanded presentation of results
on separate worksheets.
P2—There are many instances where variables in this problem are imbedded in cell formulas
(see Exhibit 1.2 cell G3). The variables should have a separate worksheet location for quick
access and presentation—a Brain . The cell formulas can then reference the cell location in the
Brain to access the value of the variable or parameter. This will allow you to easily make changes
in a single location and note the sensitivity of the model to these changes. If the client asks what
if questions during your presentation of results, the current spreadsheet will be very difﬁcult to
use.— Create a Brain worksheet.
P3—The new layout that results from the changes I suggest, should include a number of user
friendliness considerations—(1) create a table of contents ,(2) place important analysis on
separate worksheets, and (3) place the results of the analysis into a graph that provides a “risk
proﬁle” of the problem results (see Exhibit 1.7 ). Number (3) is related to a larger issue of
appropriateness of analysis (see General Comment).
P4—Document the workbook to provide the user with information regarding the assumptions
and form of analysis employed— Use text boxes to provide users with information on assumed
values (Sales Volume, Average Selling Price, etc.), use cell comments to guide users to cells
where the input of data can be performed, and name cell ranges so formulas reﬂect directly the
operation being performed in the cell.
P5—Provide the user with navigation from the table of content to, and within, the various
worksheets of the workbook— Insert hypertext links throughout.