Goals: Design and organize data to create a worksheet

Employ quantitative functions to analyze the data

Select and design a chart to appropriately present your managers request

You are working for a department store Nordstems that has three branches. Your boss has asked you to create a worksheet to show results for weekly sales of the various products in the branches that include: apparel, cosmetics, and luggage.

Each reported the following data:

• Branch 1: They sold $824.39 in apparel; $684.29 in cosmetics; $1,508.65 in luggage
• Branch 2: They sold $587.96 in apparel; $468.27 in cosmetics; $600 in luggage
• Branch 3: They sold $869.42 apparel; $751.65 in cosmetics; $487.39 in luggage

Construct a worksheet according to the following format requirements (BL3):

1. Format the worksheet: Page: Landscape Orientation, Fit to 1 by 1; Margins: Centered on page vertically and horizontally; Header: Left - Chapter 1 Printout Exam, Center - Your Name, Right - IT115-School_Id#
2. The title of the worksheet should be Nordstems
3. The subtitle should be called Weekly Sales as of (use todays date)
4. Insert the reported data for each branch and department stated above into the worksheet. Include a total column for branch totals and a total row for department totals.
5. Center and underline the column headings
6. Merge, and center the worksheet title. Format the title using the Title cell style. Bold the title
7. Merge, and center the worksheet subtitle. Format the subtitle using the Heading 1 cell style
8. Format the workbook according to the accounting and comma style (Hint: Only the first row and total row should have $)
9. Format the total ROW with the Total cell style.
10. Create a formula that totals sales of all items for each branch
11. Create a formula that totals sales for each department. Format the total line for sales by department with the Total cell style.
12. Autofit column widths for ALL columns
13. Your Manager wants you to create a 3-D Pie Chart on a new sheet to show the total revenue that each branch contributes to overall weekly revenue at Nordstems. You must show the revenue contributions of each branch in the form of a percentage. For example, your answer needs to demonstrate that Branch 1 contributed 50%, Branch 2 contributed. Make sure that you title the chart appropriately and that the fonts are big enough to easily read.
14. The chart should show the percent for each piece of the pie and have a legend for each color of the pie.
15. Apply the following header to your chart worksheet as follows:

Chapter 1 Printout Student Name IT115-School_Id#

16. Rename the chart worksheet to Revenue Chart
17. Rename the data worksheet to Revenue Data

Type the answers to the following quantitative reasoning questions (put answers below your spreadsheet and make sure you number each question): (BL3):

1. What is the total value of weekly sales for all branches?
2. Which itemapparel, cosmetics, or luggage in the spreadsheet accounted for the highest weekly sales?
3. What branch sold the most apparel?
4. If you were going to eliminate a branch that contributed the least percentage of sales to your company, what branch would that be?
5. If the goal was to sell over $500 in cosmetics, which Branch(es) met the goal?