Microsoft Office Tutorials and References

In Depth Information

**Cases and Places**

Apply your creative thinking and problem solving skills to design and implement a solution.

•
E
ASIER
••
M
ORE
D
IFFICULT

•
1: Five-Year Sales Projections

You have been asked to develop a worksheet for Millennium Steel that shows annual growth for the

next ﬁ ve years based on the prior year’s sales and growth data. Include an embedded exploded 3-D Pie

chart that shows the contribution of each year to the total gross margin. The data and general layout of

the worksheet, including the totals, are shown in Table 3–16.

Table 3–16 Millennium Steel Sales Data and General Layout

Year 1

Year 2

Year 3

Year 4

Year 5

Total

Sales

Formula A

—

Cost of Goods

Formula B

—

Gross Margin

Formula C

—

Assumptions

Prior Year’s Sales

35234500

Annual Growth Rate

1.75%

12.35%

5.00%

1.25%

8.75%

Annual Cost Rate

41.25%

44.00%

33.00%

43.75%

34.25%

Premium

2.90%

3.10%

4.95%

2.50%

4.50%

Enter the formulas shown in Table 3–17 in the locations shown in Table 3–16. Copy formulas A, B, and

C to the remaining years. The gross margin for the ﬁ ve years should equal $118,986,982.

Table 3–17 Millennium Steel Sales Projection Formulas

Formula A = Prior Year’s Sales * (1 + Annual Growth Rate)

Formula B = IF(Annual Growth Rate < 0, Sales * (Annual Cost Rate + Premium), Sales * Annual Cost Rate)

Formula C = Sales

Cost of Goods

Use the concepts and techniques developed in the ﬁ rst three projects to create and format the

worksheet and embedded 3-D Pie chart.

Use the Goal Seek command to determine the Year 1 annual growth rate that will generate a

total gross margin of $125,000,000. You should end up with a Year 1 annual growth rate of 2.35%.

Submit the workbook and results of the goal seek as requested by your instructor.

•
2: Bimonthly Projected Earnings and Expenditures

The
Chesterton Trib
is a small newspaper that publishes stories of local interest. Revenues are earned

from subscriptions and the sale of advertising space. A ﬁ xed percentage of the Net Revenue is spent on

marketing, payroll, commissions (advertising sales only), production costs, and reportorial expenses.

The editor has summarized the paper’s expenditures over the past year and the anticipated income from

subscriptions and advertising on a bimonthly basis as shown in Table 3–18.

With the data, you have been asked to prepare a worksheet for the next shareholder’s meeting

showing total revenues, total expenditures, and operating incomes for each bimonthly period. Include

a 3-D Cylinder chart on a separate sheet that compares the six bimonthly operating incomes. Use the

concepts and techniques presented in this project to create and format the worksheet and chart.