Creating a Current and Projected Quarterly Analysis Worksheet
Purpose: To demonstrate the ability to create a data series, use the Format Painter button, copy a range to a
nonadjacent range, apply formulas that use absolute referencing, create a chart, use goal seeking, and perform
what-if analysis.
Problem: You are the accountant for JJW Discount Warehouse. The president of the company, JW, is
concerned that the store is carrying too many customers on its own in-store credit accounts (referred to as FE
Accounts). He has asked you to create a worksheet for the second quarter of sales that depicts the amount of
sales by cash, bank cards (referred to as BC), credit through a local finance company (referred to as Finance
Co.), and layaway. Furthermore, he wants to see projected third quarter sales. Finally, the worksheet should
show the percent of total sales by each type of payment for April, May, and June.
Part 1 Instructions: Perform the following tasks to create the worksheet in Figure E3A-1.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Bold the entire worksheet. Enter the worksheet title, JJW Discount Warehouse, in cell A1. Enter a subtitle,
Quarterly Sales Analysis, in cell A2. Change the height of rows 1 and 2 to 18.00 points.
Change the font of cells A1 and A2 to any available font besides Arial or Times New Roman.
Center the two titles across columns A through G.
Change the background color of the first two rows to aqua (column 5, row 3 of the Color palette) and the
color of the text to white.
Enter the words, Prepared by, followed by your initials in cell A3. Enter the NOW function in cell G3, and
change the format to dd-mmm-yy style.
Enter the words, Second Quarter Sales, in cell A4 and the words, Projected Third Quarter Sales, in cell E4.
Enter the first month of the third quarter, APR, in cell B5 and right-align it. Use the fill handle to create
the month series in row 5, so the headings read APR through SEP.
Change the column headings’ background colors in row 5 to sky blue (column 6, row 4 of the Color
palette).
Enter the row titles from Table E3A-1 below, starting in cell A6.
Change the width of column A to 12.00 points and of columns B through G to 10.57 points.
Enter the data shown in Table E3A-1 below in the range B6:D10. Format the range B6:G10 to Comma
style with two decimal places.
Cash
BC
FE Accts
Finance Co.
Layaway
APR
11,985.00
9,951.19
7,384.36
4,582.57
609.74
MAY
10,697.00
10,624.16
8,212.04
4,127.48
681.36
Table E3A-1
Page 1
JUN
11,357.00
9,852.19
7,613.36
4,546.57
673.74
12. The projected third quarter sales are based on previous quarter’s sales. To calculate the month of July,
increase June sales by 1.5 percent. August is expected to have a 3.0 percent increase over July sales, and
September is expected to have a 5.5 percent increase over August. Enter the following formulas in the
designated cells: E6=D6*1.015; F6=E6*1.03; and G6=F6*1.055. Use the fill handle to copy the formulas
for each column.
13. Enter the title, Total Sales, in cell A11 and change the background colors in the range A11:G11 to yellow.
Use the SUM function to calculate the total sales for April. Use the fill handle to copy the sum for all the
columns. Format the range B11:G11 to comma style with two decimal positions.
14. Complete the following entries.
a. In cell A12, enter the title, Analysis
b. Copy the labels for the five types of sales, Cash, BC, FE Accts, Finance Co., and Layaway, from cells
A6:A10 to A13:A17
c. Calculate the percentages for each of the five types of sales by dividing the APR value by the Total.
The APR cash sales in cell B13 is calculated using the following formula: =B6/$B$11
d. Use the fill handle to copy the formula down to cell B17. Format these cells as Percent with no
decimal places
e. Create similar entries for MAY and JUN in columns C and D
f. Change the color of the range A12:D17 to gray-25%
g. Adjust the height of row 12 to 21.00 point
15. Enter your name in cell A20. In the cells directly below your name, enter your course identification,
computer lab assignment (Lab Test A – Excel Chapter 3), date, and instructor name.
16. Rename the Sheet 1 tab, Analysis.
17. Save the workbook using the file name, Yourname - Excel Chapter 3 - Lab Test A, where Yourname is
your own last name.
18. Preview and print the worksheet. Preview and print the formulas (CTRL+`) in landscape orientation using
the Fit to option button in the Page Setup dialog box.
19. Redisplay the values version of the worksheet.
Part 2 Instructions: Using Chart Wizard, create the 3D pie chart chart illustrated in Figure E3A-2 showing
the monthly amount of sales based on JJW Discount Warehouse’s customer credit (FE Accts). This chart uses
the nonadjacent ranges A5:G5 (category) and A8:G8 (data series). Place this chart on a separate sheet.
Make the following modifications to the chart.
1.
3.
4.
Double underline the chart title and make its font size 24 point.2.Rename the tab for the chart to Credit
Chart. Rearrange the tabs so Analysis is before Credit Chart.
Delete unused sheets and save the workbook again.
Print both sheets.
Page 2
Part 3 Instructions:
1.
You determine that the increases for each type of sale may not be the same. You need to add an additional
area to the worksheet called Projections. Enter the label, Projections, in cell E13. Put the data from Table
E3A-2 in cells E14:F17.
Projections
BC
FE Accts
Finance Co.
Layaway
1.07
1.065
1.015
1.03
Table E3A - 2
2.
3.
4.
5.
Modify the formulas used for noncash third quarter projections in cells E7:G10. Using absolute
references, update the July formulas using the projections in the range F14:F17. Use the fill handle to copy
these formulas across for August and September sales.
JW is concerned that too many sales are made on in-house charge accounts. Use the Goal Seek command
to determine the FE Accts amount for April, if the percentage is to be only 12%.
Save the workbook as Yourname - Excel Chapter 3 - Lab Test A-P3.
Print this revised Analysis sheet. It should display as shown in Figure E3A-3.
Page 3
Figure E3A - 1
Page 4
Figure E3A - 2
Page 5
Figure E3A - 3
Page 6
Purchase answer to see full
attachment