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%
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

Search JabSto ::

Custom Search