Microsoft Office Tutorials and References
In Depth Information
3 What-If Analysis, Charting, and
Working with Large Worksheets
Worksheets normally are much larger than those created in the previous chapters, often
extending beyond the size of the window. Because you cannot see the entire worksheet on
the screen at one time, working with a large worksheet sometimes can be frustrating. This
chapter introduces several Excel commands that allow you to control what displays on the
screen so you can view critical parts of a large worksheet at one time. One command lets
you freeze the row and column titles so Excel always displays them on the screen. Another
command splits the worksheet into separate window panes so you can view different parts
of a worksheet on the screen at one time. Hiding the Ribbon will allow a larger portion of
the worksheet to be visible.
When you set up a worksheet, you should use cell references in formulas whenever
possible, rather than constant values. The use of a cell reference allows you to change a
value in multiple formulas by changing the value in a single cell. The cell references in
a formula are called assumptions. Assumptions are values in cells that you can change to
determine new values for formulas. This chapter emphasizes the use of assumptions and
shows how to use Excel to answer what-if questions such as, what happens to the semi-
annual operating income if you decrease the marketing expenses assumption by 2%? Being
able to analyze quickly the effect of changing values in a worksheet is an important skill in
making business decisions.
This chapter also introduces you to techniques that will enhance your ability to
create worksheets and draw charts. From your work in Chapter 1, you are aware of how
easily you can create charts. This chapter covers additional charting techniques that allow
you to convey your message in a dramatic pictorial fashion such as an exploded 3-D Pie
chart. This chapter also covers other methods for entering values in cells, such as allowing
Excel to enter values for you based on a pattern of values that you create, and formatting
these values. In addition, you will learn how to use absolute cell references and how to use
the IF function to assign a value to a cell based on a logical test.
Project — Financial Projection Worksheet with
What-If Analysis and Chart
The project in the chapter follows proper design guidelines and uses Excel to create the
worksheet and pie chart shown in Figure 3–1. Campus Clothiers manufactures and sells
customized clothing to college students on campuses around the country. Each June and
December, the director of fi nance and accounting submits a plan to the management team
to show projected monthly revenues, costs of goods, gross margin, expenses, and operating
income for the next six months. The director requires an easy-to-read worksheet that
shows fi nancial projections for the next six months. The worksheet should allow for quick
analysis if projections for certain numbers change, such as the percentage of expenses
allocated to marketing. In addition, a 3-D Pie chart is required that shows the projected
operating income contribution for each of the six months.
Search JabSto ::

Custom Search