"You have been asked to develop a worksheet for Global Steel to show its annual growth for the next five years based on the prior year's sales and growth data. Include an embedded 3-D Pie chart that shows the contribution of each year' gross margin to the total gross margin. Table 1 in the following shows the data and general layout of the worksheet, with assumptions. Table 2 provides the formulas to be used. Design and format the worksheet and enter the formulas shown in Table 2 with proper cell references. The values in the Total column should be calculated using the SUM function.

Table 1 Global Steel Sales Data with Assumptions

Year 1 Year 2 Year 3 Year 4 Year 5 Total

Sales Formula A -------------------------------------------------------------------------> -

Cost of Goods Formula B -------------------------------------------------------------------------> -

Gross Margin Formula C -------------------------------------------------------------------------> -

Sales for Year 0 (Prior Year's Sales for Year 1) 26300000

Annual Growth Rate 2.250%

Annual Cost Rate 41.25%

Table 2 Global Steel Sales Projection Formulas

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

Formula B: Cost of Goods = Sales * Annual Cost Rate

Formula C: Gross Margin = Sales - Cost of Goods

You should use the concepts and techniques presented in Excel Chapters 1-3 to create and format the worksheet with an embedded 3-D Pie chart as required with the details listed in the following: 1. (35 points) Your finished workbook should contain one worksheet to reflect the given data and calculations. a. (5 points) The worksheet should have a descriptive title, subtitle, proper column and row headings, and a date. b. (30 points) All the formulas and numbers need to be correct. In the above Table 1, the Prior Year's Sales value is given for Year 1, which is the Sales for Year 0; Year 1 Sales amount is the Prior Year's Sales for Year 2, Year 2 should be the prior year for Year 3, and so on. More specifically

The given assumptions data should be entered correctly in your worksheet;

The formulas given in Table 2 should be followed, and the three totals should be calculated using the SUM function as required;

All formulas must use cell references, instead of actual constant values; and students should use proper relative and/or absolute cell references correctly in all formulas. 2. (30 points) Format the worksheet professionally. More specifically,

a. (10 points) The design of the worksheet layout should be very clear and easy to follow; students can set off the assumptions from the sales projection as a separate table, or can keep them together as presented in

Table 1; worksheet should have an appropriate sheet tab name instead of just "Sheet 1" (at the bottom of the screen above the Windows status bar). Students should use a sheet tab name that reflects the content of the worksheet.

b. (5 points) Format the titles and headings: Students should make use of proper cell styles and formatting to make row and column headings stand out from row/column data; title and subtitle for the worksheet should be formatted to stand out from the rest of the worksheet; the title and headings for the assumptions section should also be formatted properly to stand out. Do not use fonts or colors that are very hard to read; for example, artistic fonts or a choice of white font over yellow background are bad choices.

c. (10 points) Format the numbers professional and consistently: Financial data should be formatted professionally and consistently across the entire worksheet; $ signs should not appear for every data entry, but for the first and last rows only; the percentages should be formatted consistently across the worksheet. Again, please do not use fonts or colors that are hard to read.

d. (5 points) Additional worksheet formatting: Add proper Header and Footer to the worksheet; the relevant print preview settings of the worksheet should also be set up properly so that, when needed, the data and chart will print nicely on one page.

3. (25 points) Create a 3-D pie chart: a. (10 points) The 3-D pie chart should be created using the Gross Margin data calculated for the 5 years. Correct data and headings should be selected to create the chart. b. (10 points) Include an appropriate title for the chart, and make sure the comparison of the data is very straightforward with clear labels and percentages for each slice. Students may include any other chart features they deem necessary. The pie chart should be placed properly on the same worksheet as the data

. c. (5 points) The chart and chart elements should be formatted appropriately so that the data are visualized in an appealing and easy to understand manner.

Finally, use Goal Seek to determine the annual growth rate that will generate a five-year total gross margin of $100,000,000. Save the goal seeking results. Hint: An example of goal seeking can be found in Chapter 3 starting from page EX 168. Your final submitted file should show the goal seeking results."