Microsoft Office Tutorials and References
In Depth Information
Cases and Places
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 fi 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 fi 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 fi 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 fi 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.
 
 
Search JabSto ::




Custom Search