Note: Follow the instructions carefully. Important: This i…
Note: Follow the instructions carefully. Important: This is an individual test, and the testing is governed by the university’s academic integrity policy. Formulas/Functions and calculations are worth more points than other simple skills. You must use cell references in the functions and formulas in order to receive full credit. Points will be deducted for using hard numbers when cell references should be used. When done, attach your Excel file by selecting “Add a file” at the bottom of the quiz. Be sure to enter your name in the textbox and hit Submit Quiz button. Complete the following [95 PTS.]: First, Save (Data-1A_Summer.xlsx) to your computer from GoView and then open it. Save the workbook as “ProctorUTest1A-YourLastName”. Save the file often during the test. Insert a new sheet named “Documentation”. Place the sheet to the beginning of sheet tab. In the Documentation sheet, enter your name and today’s date in cells A1 and A2 respectively. In the sheet1, rename the sheet as “SummerSale”. In the cell A1, merge and center across columns A-L, and apply “title” style. In the cell A2, merge and center across columns A-L. Apply Bold and Italic, and apply “Heading 1” style. For the entire column J, enter SUM function to find the total for each month. In column K, enter a calculation by subtracting the previous Month’s Total (in Column J) from the current Month’s total (in Column J). For example, K6=J6-J5. However, skip K5 and enter the calculation starting from K6. In column L, enter a percent calculation of “AMT Changed” by taking the current Month’s AMT Changed (in Column K) divided by the previous Month’s Total Amount (in Column J). Make sure that the percent has a % format with 2 decimal places. In Revenue section in J20:L31, do the same calculations for Total, AMT changed, and % Changed as done in steps 9 and 10. Be sure to complete K21 to K31 and L21 to L31 as done in previous steps. In C17:i17, calculate the total units sold for each department by applying the SUM function across columns C through i. In other words, add up all the units sold in that column, from row 5 down to row 16. In C32:i32, calculate the total Revenue for each department by applying the SUM function across columns C through i. In other words, add up all the Revenue in that column, from row 20 down to row 31. In C18:i18, calculate the Average amount for each column (C through J) by applying the AVERAGE function. Format Row Titles in Column A with “Units Sold” and “Revenues” as indicated in the Figure below: merge and center, alignment, Orientation (angle clockwise). To column titles (Rows 4 and 19) and row titles (Columns B), apply “Accent 2” in Styles. Colors may look different from the figure. Apply the built-in Theme “Ion” from the theme list to the whole area in the sheet. If no “Ion” is present, apply another theme available and specify exactly what you’ve chosen. In cells K5:K16, apply Conditional Formatting to format the cells with values between -15 and 15 Light Red with Dark Red. In cells L5:L16, apply Conditional Formatting to format the cells with bottom 5 values in Light Green with Dark Green. In cells L20:L32, apply Conditional Formatting to format the cells with Data Bars in Light Red. In column M, enter a SUM function to find the running (cumulative) total of the values in column J (Total). Be sure to use the absolute cell reference in the formula. Insert your Name and Date/Time to the Center section in the header of Summersale sheet. Adjust the Page Orientation to Landscape.
Read Details