Microsoft Office Tutorials and References

In Depth Information

EX 158

Excel Chapter 2
Formulas, Functions, Formatting, and Web Queries

Cases and Places
continued

•• 3: Create a Fund-Raising Analysis Worksheet

You are the chairperson of the fund-raising committee for a local charity. You want to compare

various fund-raising ideas to determine which will give you the best proﬁ t. The data obtained from

six businesses about their products and the format of the desired report are shown in Table 2–14. The

required formulas are shown in Table 2–15. Use the concepts and techniques presented in this project

to create and format the worksheet.

Table 2–14 Fund-Raising Data and Worksheet Layout

Cost

per Unit

Proﬁ t per

2000 Sales

Proﬁ t per

5000 Sales

Product

Company

Margin

Selling Price

Candles

Woodland Farms

$4.75

40%

Formula A

Formula B

Formula C

Candy

Polkandy

3.00

70%

Coffee

Garcia Coffee

6.50

45%

Cookie dough

Oh, Dough!

2.90

65%

Flower bulbs

Early Bloom

2.40

50%

T-shirts

Zed’s Sports

5.75

42%

Minimum

Formula D

Maximum

Formula E

Table 2–15 Band Fund-Raising Formulas

Formula A = Cost per Unit / (1 – Margin)

Formula B = 2000 * (Selling Price – Cost per Unit)

Formula C = 5000 *110% * (Selling Price – Cost per Unit)

Formula D = MIN function

Formula E = MAX function

•• 4: Design and Create a Projected Budget

Make It Personal

For the next six-month period, forecast your income for each month, your base expenditures for each

month, and your special expenditures for each month. Base expenditures include expenses that occur

each month, such as food and loan payments. Special expenditures include expenses that are out of the

ordinary, such as the purchase of gifts, automobile insurance, and medical expenses. With this data,

develop a worksheet calculating the amount of remaining money at the end of each month. You can

determine this amount by subtracting both expenses from the anticipated income.

Include a total, average value, highest value, and lowest value for income, base expenditures,

special expenditures, and remaining money. Use the concepts and techniques presented in this project

to create and format the worksheet.

Create a 3-D Pie chart on a separate sheet illustrating the portion each month’s special expenditures

deducts from the total remaining money after all six months have passed. Use Microsoft Excel Help to

create a professional looking 3-D Pie chart with title and data labels.

C6161_EXC_02.4c.indd 158

C6161_EXC_02.4c.indd 158

3/6/07 3:30:28 PM

3/6/07 3:30:28 PM