Create and edit 4 Excel files based on instructions I provided
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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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 (
[email protected]) © 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 your 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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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 for 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 by 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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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 ranges: 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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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’ button (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. Rearrange 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-Year 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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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 requested 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 both small and large businesses. You have been asked to update the weekly payroll report 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/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236895&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 row. 4. Delete column B. Set column A width to 31.00 and columns B through K to 11.00. Select 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 for the two new employees listed in