Use the files(the 3 PDF files and other data files) I provided to complete this work.
The instructions are in the picture below. (Complete module 3 – Lab 2, module 5 – Lab 2 and module 6- Lab 2&3). Specific steps are in the 3 PDF files I provided. After you finish all the work, there should be 4 Excel files to give me)
My name: Beck Wang (Do not forget to include my name per instructions as my teacher will give zero if missing my name. Follow the instructions.)
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 1/11
Chapter 3: Working with Large Worksheets, Charting, and What-If Analysis: 3-14f In the Labs
Book Title: Microsoft® Office 365™ & Excel ®2016: Comprehensive
Printed By: Beck Wang (Beck.Wang@george
own.ca)
© 2017 Cengage Learning, Cengage Learning
Review
3-14f In the Labs
Design, create, modify and/or use a workbook following the guidelines, concepts, and skills
presented in this module. Labs 1 and 2, which increase in difficulty, require you to create
solutions based on what you learned in the module; Lab 3 requires you to apply you
creative thinking and problem-solving skills to design and implement a solution.
Lab 1:
Eight-Year Financial Projection
Problem: Your supervisor in the finance department at August Online Technology
has asked you to create a worksheet for the flagship product that will project the
annual gross margin, total expenses, operating income, income taxes, and net
income for the next eight years based on the assumptions in Table 3–9. The desired
worksheet is shown in Figure 3–85.
Table 3–9
August Online Technology Financial Projection Assumptions
Units Sold in Prior Year 235,411
Unit Cost $150.00
Annual Sales Growth 3.25%
Annual Price Increase 3.00%
Margin 29.90%
Figure 3–85
javascript:
javascript:
javascript:
javascript:
javascript:
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 2/11
Perform the following tasks:
Instructions Part 1: Run Excel, open a blank workbook, and then create the
worksheet.
1.
Apply the Parallax theme to the worksheet.
2.
Enter the worksheet title August Online Technology in cell A1
and the subtitle Eight-Year Financial Projection fo
Product X in cell A2. Format the worksheet title in cell A1 to 26-
point and the worksheet subtitle in cell A2 to 16-point. Enter the
system date in cell I2 using the NOW function. Format the date to the
14-Mar-12 style.
3.
Change the following column widths: characters; B through
characters. Change the heights of rows 7, 15, 17, 19, and
22 to 18.00 points.
4.
Enter the eight column titles Year 1 through Year 8 in the range B3:I3
y entering Year 1 in cell B3 and then dragging cell B3’s fill handle
through the range C3:I3. Format cell B3 as follows:
Increase the font size to 12.
Center and italicize it.
Angle its contents 45 degrees.
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 3/11
5.
Use the Format Painter button to copy the format assigned to cell B3
to the range C3:I3.
6.
Enter the row titles, as shown in Figure 3-85, in the range A4:A19.
Change the font size in cells A7, A15, A17, and A19 to 14-point. Add
thick bottom borders to the ranges A3:I3 and A5:I5. Use the Increase
Indent button (Home tab | Alignment group) to increase the indent of
the row titles in cell A5, the range A8:A14, and cell A18.
7.
If requested by your instructor, change the entry in row 14 by inserting
your surname prior to the text, Web Services.
8.
Enter the table title Assumptions in cell A22. Enter the assumptions
in Table 3–9 in the range A23:B27. Use format symbols when entering
the numbers. Change the font size of the table title in cell A22 to 14-
point and underline it.
9.
Select the range B4:I19 and then click the Number Format Dialog Box
Launcher (Home tab | Number group) to display the Format Cells
dialog box. Use the Number category (Format Cells dialog box) to
assign the appropriate style that displays numbers with two decimal
places and negative numbers in black font and enclosed in
parentheses to the range B4:I19.
10.
Complete the following entries:
Copy cell C4 to the range D4:I4.
.
Copy cell B5 to the range C5:I5.
.
Copy cell B6 to the range C6:I6.
. Copy
cell B8 to the range C8:I8.
javascript:
javascript:
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 4/11
; ;
; ; ;
; ; .
.
Copy cell C10 to the range D10:I10.
. Copy cell B11
to the range C11:I11.
. Copy cell B12
to the range C12:I12.
. Copy cell B13
to the range C13:I13.
.
Copy cell C14 to the range D14:I14.
. Copy cell
B15 to the range C15:I15.
.
Copy cell B17 to the range C17:I17.
Year 1 Income Tax (cell B18): If Year 1 Operating Income is less
than 0, then Year 1 Income Tax equals 0; otherwise Year 1
Income Tax equals 33% * Year 1 Operating Income. Copy cell
B18 to the range C18:I18.
.
Copy cell B19 to the range C19:I19.
In cell J4, insert a column sparkline chart (Insert tab | Sparklines
group) for cell range B4:I4.
Insert column sparkline charts in cells J5, J6, J8:J15, and
J17:J19 using ranges B5:I5, B6:I6, B8:I8 – B15:I15, and B17:I17
– B19:I19 respectively.
11.
Apply the Accounting number format with a dollar sign and two
decimal places to the following ranges: B4:I4, B6:I6, B8:I8, B15:I15,
B17:I17, and B19:I19. Apply the comma style format to the following
anges: B5:I5 and B9:I14. Apply the Number format with two decimal
places and the 1000 separator to the range B18:I18.
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 5/11
12.
Change the background colors, as shown in Figure 3–85. Use Blue,
Accent 1, Lighter 40% for the background colors.
13.
Save the workbook using the file name, Lab 3-1 August Online
Technology Eight-Year Financial Projection.
14.
Preview the worksheet. Use the Orientation button (Page Layout tab |
Page Setup group) to fit the printout on one page in landscape
orientation. Preview the formulas version (����+`) of the worksheet in
landscape orientation using the Fit to option. Press ����+` to instruct
Excel to display the values version of the worksheet. Save the
workbook again.
Instructions Part 2: Create a chart to present the data, shown in Figure 3–86. If
necessary, run Excel and open the workbook Lab 3-1 August Online Technology
Eight-Year Financial Projection.
Figure 3–86
1.
Use the nonadjacent ranges B3:I3 and B19:I19 to create a Stacked
Area chart (Hint: use the Recommended Charts button). When the
chart appears, click the Move Chart button to move the chart to a new
sheet.
2.
Change the chart title to Projected Net Income .
javascript:
javascript:
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 6/11
3.
Use the Chart Elements button to add a vertical axis title. Edit the axis
title text to read Net Income . Bold the axis title.
4.
Change the Chart Style to Style 4 in the Chart Styles Gallery (Chart
Tools Design tab | Chart Styles group). Use the ‘Chart Quick Colors’
utton (Chart Tools Design tab | Chart Styles group) to change the
color scheme to Monochromatic, Color 5.
5.
Rename the sheet tabs Financial Projection and Net Income Chart.
Rea
ange the sheets so that the worksheet is leftmost and change
the tab colors to those of your choosing.
6.
Click the Financial Projection tab to return to the worksheet. Save the
workbook using the same file name (Lab 3-1 August Online
Technology Eight-Year Financial Projection) as defined in Part 1.
Instructions Part 3: Use Goal Seek to analyze three different sales scenarios. If
necessary, open the workbook Lab 3-1 August Online Technology Eight-Yea
Financial Projection.
1.
Divide the window into two panes between rows 6 and 7. Use the
scroll bars to show both the top and bottom of the worksheet. Using
the numbers in columns 2 and 3 of Table 3–10, analyze the effect of
changing the annual sales growth (cell B25) and annual price increase
(cell B26) on the net incomes in row 19. Record the answers for each
case and submit the results in a form as requested by your instructor.
Table 3–10
August Online Technology Alternative Projections
Case Annual Sales
Growth
Annual Price
Increase
1 4.25% 2.00%
2 2.25% 3.00%
3 1.25% 4.00%
javascript:
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 7/11
2.
Close the workbook without saving it and then reopen it. Use the
‘What-If Analysis’ button (Data tab | Forecast group) to goal seek.
Determine a margin that would result in a Year 8 net income of
$1,500,000. Save the workbook with your needed changes as Lab 3-1
August Online Technology Eight-Year Financial Projection GS. Submit
the workbook with the new values or the results of the goal seek as
equested by your instructor.
3.
How would you use what-if analysis tools to determine what
Annual Sales Growth you would need to achieve in order to keep
prices steady over the eight-year projection period?
Lab 2:
Updating a Weekly Payroll Worksheet
Note: To complete this assignment, you will be required to use the Data Files.
Please contact your instructor for information about accessing the required files.
Problem: PHM Reliable Catering is a company that provides catering services to
oth small and large businesses. You have been asked to update the weekly payroll
eport to reflect changes in personnel, to update certain mandatory deductions, and
to add overtime computations. The final worksheet is shown in Figure 3–87. Run
Excel. Open the workbook, Lab 3-2 PHM Reliable Catering Weekly Payroll Report.
Figure 3–87
Perform the following tasks:
javascript:
4/19/22, 8:17 PM Print Preview
https:
ng.cengage.com/static/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&nbI… 8/11
1.
Save the workbook using the file name, Lab 3-2 PHM Reliable
Catering Weekly Payroll Report Complete.
2.
Apply the Depth theme to the worksheet.
3.
Delete rows 12 through 14 to remove the statistics below the Totals
ow.
4.
Delete column B. Set column A width to 31.00 and columns B through
K to XXXXXXXXXXSelect row 3 and set text to wrap in this row using the
Wrap Text button (Home tab | Alignment group), and then set the row
height to best fit.
5.
Delete the record for the employee Evans, Timothy. Add two blank
lines directly above the row for Mi, Emily, and add the information fo
the two new employees listed in