Excel Project Part A Instructions (1)

Business Productivity Tools and Technology MS E XCEL P ROJECT P ART A (T EAM AND I NDIVIDUAL ) – O UT OF 225 MARKS BACKGROUND INFORMATION BCMP Refresh Beverages Inc . is a fictitious Calgary based beverage business. Your team has been hired as consultants to assist the CEO (your instructor) in developing a number of Excel spreadsheets. The consultants will demonstrate their expertise in Excel formulas, functions, data analysis and apply professional formatting skills. WORKING WITH PROJECT FILES Each student must complete their own work using their own files. DO NOT split the project up between group members. We want you to be competent in all the skills not just the ones you choose to do. Collaborate as a team and assist each other on each task of the project. Each student will submit a completed project file. Some components of each student’s submission will be similar to others within their team, while other components will be unique to each student. Students will submit their individual completed project and the instructor will grade one submission as indicated by your team. However, each individual submission will be reviewed for completeness and to confirm that files were not copied. DATA FILES MUST NOT BE SHARED. Your instructor will randomly check files for potential integrity violations and report any academic misconduct to your Academic Chair. See policy AC 3.4.1 . All submissions will be checked for accuracy and completeness. Backup each session by copying your work to a USB and/or your OneDrive storage. Extensions are not provided for lost files. Late submissions will be deducted 10% per day up to 5 days late. Projects will not be accepted after that time. Southern Alberta Institute of Technology │ School of Business | Winter 2024 Page 1

Business Productivity Tools and Technology Task 1: Get Started (13 marks) Each student is to download the following file from D2L > Assessments > Assignments > Excel Project Part A : Excel_Project Part A. xltx (template) and Excel_Project Additional Data.xlsx 1.1 Open Excel_Project Part A.xltx . Enable Editing and Save As Excel - Lastname Part A.xlsx (replace Lastname with your last name). Be sure to save this as an Excel workbook rather than the template you downloaded. 1.2 Choose a theme colour different than your team mates to be used through the Excel projects. 1.3 Fill in the required information on the 1. Student Information worksheet including the individual colour theme which is different than the other group members. 1.4 Set the following properties: (2)  Author : Enter your full name  Keywords : Enter your SAIT ID number 1.5 Run the Accessibility checker and add alternative text “ Technology Image ” to the image. (2) 1.6 Hyperlink cell B3 to your SAIT email address with your First and Lastname as the Text to display and the subject Excel Project Part A . (3). Adjust the font size of cell C3 appropriately. 1.7 Customize the 1. Student Information worksheet page setup as follows:  Set to print on a single landscape page . (1)  Set the margins to a half-inch and centre the print job vertically and horizontally . (2)  Set the print area to A1:C13 . (1) 1.8 Change the worksheet tab colour of the Student Information worksheet to match your theme colour chosen. (1) 1.9 Rename Sheet1 to 3. Sales and Profit Report . (1) 1.10 Notice that on 3. Sales and Profit Report worksheet, that the cells C4:F14 have random numbers being generated. Press Function key F9 (recalculation key) once or twice and observe the numbers changing. Select C4:F15 . Copy to the Windows clipboard. From the Paste options, choose Paste Values | Values in the same location to replace the random numbers . Press Function key F9 and ensure the cell values no longer change . Southern Alberta Institute of Technology │ School of Business Page 2

Business Productivity Tools and Technology Task 3: Sales and Profit Report – Basic Formatting Skills (50 marks) 3.1 Insert 2 new rows at the top of the 3. Sales and Profit worksheet. (1) 3.2 Using your colour theme, create the heading as shown below (refer to steps 3.2.a – 3.2.g for details). (Note: borders may not appear as set when in Normal view) 3.2.a. Use line-breaks to separate line content – see above. Merge and center title in row 1 across columns A:F . (2) 3.2.b. Enter the first line of text as shown. Replace the text in the 2 nd line with the Last Names of your team members. Example: Marier, Lillejord, Jhamb – Consultants On the 3 rd line, enter report name as shown in the example . (2) 3.2.c. On the 4 th line of the heading, type in the current date (not a function.) (1) 3.2.d. Increase the row height to 90 to fully display the main title. (1) Horizontally and vertically centre the contents of cell A1. (1) 3.2.e. Change the font of the cell contents to a different sans-serif ( excluding Arial and MS Sans Serif ) font. See: https://en.wikipedia.org/wiki/List_of_sans_serif_typefaces for examples of sans serif fonts that may be installed on your computer. Ensure that your font reflects a professional business look. Apply contrasting font colour that you will continue to use consistently throughout this workbook . (2) 3.2.f. Increase font size to 20 and apply bold to the first line . Increase font size of sub- headings to size 13 . (2) 3.2.g. Add a thick coloured border , matching the font colour, to the top and bottom of cell A1 . Add a thin coloured border matching the font colour, to the left and right of cell A1 . Apply a contrasting fill colour. (2) 3.3 Apply the Comma number format with 0 decimal places to any cell that reflects a quantity value ( C6:G18 ). (2) Note: the format may change automatically by Excel. 3.4 Apply the Currency format with 2 decimal places to any cells that reflects dollar amounts in first row and Total row. Rows in-between should not display the currency symbol and should include a Comma and 2 decimal places . Note: the format may change automatically by Excel, however, all values in these columns must use a comma as the thousands separator and properly align with currency values. Widen any column that displays #####. (6) 3.5 Apply the Percentage format with 2 decimal places to any cells with percentage values. (1) 3.6 In B20 through 25 format results appropriately (dollar values should have Currency format . Numbers sold should not have currency symbol, however, should have the comma as the thousands separator, and have no decimals .) (2) Southern Alberta Institute of Technology │ School of Business Page 4

Business Productivity Tools and Technology 3.7 Apply single-accounting underlines to H17:K17 and M17 . Apply double-accounting underlines to H18:K18 and M18 . (4) 3.8 Apply Top and Double-Bottom Borders to cells C18:G18 . (2) 3.9 Select the titles in row 5 . Apply Bold, Vertically and Horizontally Align Centre, Wrap Text and modify the font colour to match the main titles. Apply a thick border style , matching the font colour that will outline each cell containing text in row 5 . (4) 3.10 Select the beverage names A6:A17 . Apply right alignment and increase the indent once for spacing. Add bold to the text. (2) 3.11 Set the width of columns C through G to 11.00 . Set the remaining columns to auto fit . (2) 3.12 Use the Format Painter to apply the format from cell A5 to cell A18 . (2) 3.13 Simultaneously freeze Column A and the first 5 Rows . (3) 3.14 Add a Note (Office 365) to cell K5 as shown below. The text for the note will contain your First and Last name on the top line – on the line below add the text “ Distribution charges include carbon tax ”. Format the 2 nd line text to italic. Fill the background with a custom color mix of Red: 94 , Green: 243 , Blue: 83 . Set the note to always be visible on both the worksheet and the printout as displayed on the worksheet. Resize the note width so the text is on a single line. Position the note in rows 2 & 3, aligning the left border of the note to the left edge of column K and adjust the width and height to remove unneeded space. For assistance on working with Notes (Office 365) or Comments (Office 2019) view the following video: http://www.mrexcel.com/excel-tips/excel-2020-old-style-comments-are-available-as-notes/ (3) 3.15 Apply a conditional format to the numbers of beverages sold in cells C6:F17 using your choice of any coloured 3-item “ Directional ” Icon set. Edit the rule to identify the numbers over 4000 as Green, over 3000 as Yellow and remaining values as Red. Ensure there is only one conditional format is set. (3) Task 4: Sales and Profit Report – Formula Verification (2 marks) The CEO would like to verify that you have used the most efficient and correct formulas and functions on the Sales and Profit Report sheet. This additional sheet will be printed out by the CEO and then compared to the results of the calculations. 4.1 Using the Move or Copy sheet worksheet command, create a copy of the 3. Sales and Profit Report worksheet and place it before the 3. Sales and Profit Report sheet. 4.2 Rename this copied sheet to 2. Sales and Profit Formulas . 4.3 Change the display on this sheet to Show Formulas . (2) Task 5: Sales and Profit Report – Chart Skills (48 marks) The CEO requires a visual presentation of your results. The CEO would like to see the Alberta unit sales for each type of beverage. Additionally, the CEO would like to see a visual representation of the % Markup compared with the % of Total Profit for each beverage type. Southern Alberta Institute of Technology │ School of Business Page 5

Business Productivity Tools and Technology 5.16 Rotate the category axis alignment to a custom angle of (50  ) – negative 50 degrees angle. (2) 5.17 Apply a gradient fill background colour to the chart area using a complementary palette matching your colour choice. (1) 5.18 Move the Legend to display on the right . (2) 5.19 Format the chart professionally to ensure it is readable and visually pleasing (fonts, colours, etc.) (3) 5.20 Add a Linear Forecast for the % Markup data. Set the forecast period to be Forward for 3 periods (3) . See: https://www.howtogeek.com/429126/how-to-work-with-trendlines-in-microsoft- excel-charts/ for assistance in working with chart trend lines. Task 6: 2023 Orders Listing - Large Data Sets – Sort, Table and Filter Skills (35 marks) BCMP Refresh Beverages Inc. is looking to do some analysis on last year’s sales. This can be accomplished by using Sort and Filter tools available to lists as well as lists converted to tables. Orders can be called in, faxed, or ordered online through their website any day of the year. 6.1 Activate the 6. 2023 Orders Listing worksheet. Convert the data to a table. Name the table Orders2023. Apply any table style Medium 8 through 14 , matching your color choice if available. (3) 6.2 Add a column heading Ship Date in column F . The table should automatically expand with the heading entered. Open the file ExcelProject Additional Data . From the 2023 Orders Listing worksheet, copy the values under the Ship Date and paste them in your project file, starting in cell F2 . Ensure the date is part of the table. (2) 6.3 In the next column, enter Delay as the heading. (1) Format the heading in columns F & G to match the other column headings. 6.4 Insert two new rows at the top of the worksheet and copy the heading from the 3. Sales and Profit Report sheet to cell A1 . Match the row height of Row 1 to the height of the original worksheet. Ensure the heading matches all formats from the original worksheet . Merge and Centre across the used columns of the current worksheet . Reapply borders if necessary. Change the title Sales and Profit Report to 2023 Orders Listing . 6.5 Replace all occurrences of Salesperson Byas to Bylas . (2) 6.6 In G4, create a Structured Reference formula to calculate the number of days between the Order and Ship Dates . Your answer should be a positive number. (2) 6.7 Add a Total row to calculate only the Average Order Amount and the Average Delay , replace the text in column A with Average . (2) 6.8 Within the table, sort data by Province then by Salesperson each in ascending order. Observe the results. (1) 6.9 Add a 3 rd sorting level ascending chronologically by Order Date and a 4 th sorting descending by Order Amount . (3) 6.10 Using the Filter data tool , filter to view the Alberta records. Apply additional filters for Sales Person Walker and Order Amounts equal to or greater than $1,000 . (3) Southern Alberta Institute of Technology │ School of Business Page 7

Business Productivity Tools and Technology 6.11 Select and copy all the displayed cells, starting with cell A1 , after the filters have been applied. Insert a new worksheet. Using Paste Special or Other Paste Options , paste the filtered results as a Picture in the new worksheet. (3) 6.12 Rename the new worksheet to 7. 2023 Walker Large Orders . Position the worksheet after the 6. 2023 Orders Listing. (1) 6.13 On the 6. 2023 Orders Listing worksheet, remove the individual filters; the filter button will remain visible . Do not use Clear from Data ribbon - SORT will not be graded . (2) 6.14 Apply new filters to display all Alberta Orders in the month of May . (2) 6.15 Apply the Quick Analysis Tool to identify the Top 10% of all the values in the Order Amount column. (3) 6.16 Modify the Top 10% to Top 15% . (2) 6.17 Apply the Quick Analysis Tool to identify any delays of more than 5 days . Use a Custom Format that clears the default background fill and includes no fill - Red Outline border . (3) Southern Alberta Institute of Technology │ School of Business Page 8

Business Productivity Tools and Technology 7.10 You would like to explore text functions in Excel to manage the joining of text to create unique values. In cell L1 enter the text Full Name . In L2 use the TEXTJOIN function, ignoring spaces, to join the lastname with the firstname using a comma and a space as a delimiter. Fill or copy down for all Employees. See https://exceljet.net/excel-functions/excel-textjoin-function . (2) Example: In cell M1 enter the text Full Name and Province . In M2 use the CONCAT function, to join Lastname, Firstname and Province, separated by the constant characters shown below. Fill or copy down for all Employees. See https://exceljet.net/excel-functions/excel-concat-function . (3) Example: In cell N1 enter the text Email . In N2 , extract the lastname followed by the first 4 LEFT characters of the firstname, followed by “@BCMP.com”. (4) Nest this function within the LOWER function so the entire results are all lowercase. Example: 7.11 Apply the Table Style that matches the style applied in step 7.8. Convert to a normal range of data. 7.12 Move the data in cells L1:N14 to A40 . Task 8: Finalizing and Submitting the Project Part A (6 marks) 8.1 On the 3. Sales and Profit Report worksheet adjust the page setup to landscape orientation and set the scaling option to Fit All Columns on One Page . (2) 8.2 Insert custom headers and footers to appear on all visible worksheets using the Page Setup dialogue box : a. Header - Include your lastname in the left section and the Sheet Name code in the right section (2) b. Footer - Enter the Last Names of all team members on the left side and the File Name code in the right section (2) 8.3 Ensure the active cell on each worksheet is in the Home position (cell A1 ) and that all sheets are in numeric order. A 5-point deduction will be applied if this is not done. 8.4 Indicate which file you want your instructor to grade on the 1. Student Information worksheet. This will be the last sheet you activate so that it will open up on this sheet when your instructor marks the file. Save your file. 8.5 Each student submits their individual project to Brightspace | Assignments | Excel Project – Part A . Southern Alberta Institute of Technology │ School of Business Page 10

Business Productivity Tools and Technology Please Note: Your instructor will be checking all files submitted by your team. Team members may receive different grades for significantly incomplete or incorrect steps. Minor differences will not be evaluated. Late submissions will be deducted 10% per day up to 5 days late. Projects will not be accepted after that time. Southern Alberta Institute of Technology │ School of Business Page 11