Creating a Current and Projected Quarterly Analysis Worksheet

timer Asked: Oct 1st, 2018

Question Description

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.

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

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors