excel model assigment

EA 9 Instructions - Independent Challenge 3.docx

Module 3 Independent Challenge 3

Independent Challenge 3

Advantage Calendars is a Dallas-based printer that prints and assembles calendars. As the finance manager for the company, one of your responsibilities is to analyze the monthly reports from the five district sales offices. Your boss, Joanne Bennington, has just asked you to prepare a quarterly sales report for an upcoming meeting. Because several top executives will be attending this meeting, Joanne reminds you that the report must look professional. In particular, she asks you to highlight the fact that the Northeastern district continues to outpace the other districts.

a

Plan a worksheet that shows the company’s sales during the first quarter. Assume that all calendars are the same price. Make sure you include the following:

· The number of calendars sold (units sold) and the associated revenues (total sales) for each of the five district sales offices. The five sales districts are Northeastern, Midwestern, Southeastern, Southern, and Western.

· Calculations that show month-by-month totals for January, February, and March, and a 3-month cumulative total.

· Calculations that show each district’s share of sales (percent of Total Sales).

· Labels that reflect the month-by-month data as well as the cumulative data.

· Formatting enhancements such as data bars that emphasize the recent month’s sales surge and the Northeastern district’s sales leadership.

b

Ask yourself the following questions about the organization and formatting of the worksheet: What worksheet title and labels do you need, and where should they appear? How can you calculate the totals? What formulas can you copy to save time and keystrokes? Do any of these formulas need to use an absolute reference? How do you show dollar amounts? What information should be shown in bold? Do you need to use more than one font? Should you use more than one point size?

c

Start Excel, then save a new, blank workbook as EX 3-Advantage Calendars to the location where you store your Data Files.

d

Build the worksheet with your own price and sales data. Enter the titles and labels first, then enter the numbers and formulas. You can use the information in Table 3-4 to get started.

Table 3-4

Advantage Calendars
1st Quarter Sales Report
		January	February	March	Total
Office	Price	Units Sold	Sales	Units Sold	Sales	Units Sold	Sales	Units Sold	Sales	Total % of Sales
Northeastern
Midwestern
Southeastern
Southern
Western

e

Add a row beneath the data containing the totals for each column.

f

Adjust the column widths as necessary.

g

Change the height of row 1 to 33 points.

h

Format labels and values to enhance the look of the worksheet, and change the font styles and alignment if necessary.

i

Resize columns and adjust the formatting as necessary.

j

Add data bars for the monthly Units Sold columns.

k

Add a column that calculates a 25% increase in total sales dollars. Use an absolute cell reference in this calculation. (Hint: Make sure that the current formatting is applied to the new information.)

l

Delete the contents of cells J4:K4 if necessary, then merge and center cell I4 over column I:K.

m

Add a bottom double border to cells I10:L10.

n

Enter your name in an empty cell.

o

Check the spelling in the workbook, change to a landscape orientation, save your work, then compare your work to Figure 3-31.

Figure 3-31

p

Preview the worksheet in Backstage view, then submit your work to your instructor as directed.

q

Close the workbook file, then exit Excel.

2