Excel Project Part A Instructions PDF

Preview

Summary

Project instructions for excel Project part A...

Description

Business Productivity Tools and Technology

MS EXCEL PROJECT PART A (TEAM AND INDIVIDUAL) – OUT OF 185 MARKS Introduction: 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. Discuss any challenges within your team using your TEAM group channel or Brightspace discussion as determined by your instructor. Any team issues should be resolved following the guidelines set in your Team Charter. Your team may request guidance if the team discussions do not resolve the issue. Backup each session by copying your work to a USB and/or your OneDrive storage. Failure to backup your work could result in data loss and require redoing part or all of the project requirements. Extensions are not provided for lost files. DATA FILES Data files are downloaded from MyLAB IT This project consists of Part A and Part B as distinct gradable components. See course schedule for due dates.

GETTING STARTED WITH YOUR TEAM

You will be working in assigned and/or self-selected teams (instructor discretion) on custom Excel Projects (2 parts). An MS-TEAMS channel will be setup for each group within the BCMP class TEAM for synchronous group meetings. Access your BCMP TEAM and then your assigned Group channel. You can see your group members via the Team Information icon: Your groups are also posted in the announcements of D2L. Here are the steps for working as a group and sharing responsibility for this project: Working with Your Team STEP 1: Communicate with your team members via email or the MSTEAMS group channel Chat. STEP 2: Assign a Team Leader who is responsible for reviewing and submitting the completed file and any communications to the instructor. Assign a second team member as the Team Coordinator who is responsible for compiling the sheets. They will distribute the completed project to all team members for their digital signature sign off and then return the verified file to the Team Leader for final review of the content for completeness and consistency before submitting the file. See Task 8 for assistance on sharing and compiling the Excel worksheets into a single file. Working and collaborating with a team is a fundamental skill that you will require throughout your courses and in your future work experience. These two roles will be undertaken by different students for Part B. STEP 3: As a group, review the requirements of the project tasks. Southern Alberta Institute of Technology │ School of Business | Fall 2021

Page 1

Business Productivity Tools and Technology Complete the Project Matrix Agree on a fair and even division of tasks and commit to timelines to complete these tasks. Use the 1. Project Matrix sheet in the Excel Project Data file located in MyLAB IT to determine the roles that will be assumed. This is a challenging project and the three roles assigned to each task may require a more collaborative approach. STEP 4: Plan to meet regularly and ensure team members are meeting their commitments to complete tasks. Discuss any questions/challenges etc. Team meetings should be used to discuss interpretation of tasks or methods to complete. Use MS-TEAMS to share screens and assist each other. STEP 5: The tasks in this project are independent with exception of Task 2 and 3 which requires separate calculation and formatting tasks. This would be best accomplished by one student or two students sharing the responsibility for these tasks. Task 4 requires charting data based on the completion of Task 3. The tasks can be completed individually and then submitted as a common file in MS Teams Files but should be worked on using the full Office version of Excel. Data files may be shared only between team members. Project Instructions

Task 1: Project Organization - Getting Started (3 marks) In My Lab IT | Course Materials | Group Capstone Projects - EXCEL, download the Excel_Project Part A.xltx file and the Excel_Project Additional Data.xlsx file. Enable Editing for the Excel_Project Part A.xltx. Save As Excel - Lastname Part A.xlsx changing the file type to Excel Workbook (where Lastname is replaced with your last name). The ExcelProject Additional Data.xls file may be saved with the default file name and file type. Be sure to save this as an Excel workbook rather than the template you downloaded. (2)

Fill in the required information on the 1. Project Matrix worksheet. (1)

Task 2: Sales and Profit Report – Basic Calculation Skills (35 marks) The CEO requires a worksheet that will identify the distribution of beverage sales and the effect that recycling costs have on sales and profit. Sales statistics are also important in planning new product launches. Open the Excel-Lastname.xlsx file and complete all the required components in this project. Do not format any cells while working through Task 2. 2.1

Notice that on Sheet1 cells C4:F14 have random numbers generated. Press Function key F9 (recalculation key) once or twice and observe the numbers changing. (1)

2.2

Select C4:F14. Copy to the Windows clipboard – Ctrl + C. 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. (3)

2.3 2.4

Rename Sheet1 to 3. Sales and Profit Report. (1) In G4 enter a function to total the number of Liquid Sunshine Organic Orange Juice beverages sold. Copy or fill vertically G5:G14. (1) After completing each function/formula below, copy or fill vertically for the remaining cells to complete the steps. Do not complete any formatting until Task 3. (2) Southern Alberta Institute of Technology │ School of Business

Page 2

Business Productivity Tools and Technology 2.5

2.6

2.7

2.8 2.9 2.10 2.11

2.12

2.13 2.14

2.15

2.16 2.17

In H4, determine the Total Sales Revenue excluding Recycle Fee for Liquid Sunshine Organic Orange Juice. Enter a formula that will multiply the Total Number of Beverages Sold by the Suggested Retail located on the Pricing & Recycling Fees worksheet. (2) Each beverage container sold has a recycling levy charged. In I4 multiply the Total Number of beverages sold in Calgary and Edmonton Malls for Liquid Sunshine Organic Orange Juice by the Alberta Recycling Fee located on the Pricing & Recycling Fees worksheet. Apply Order of Operations in this formula and appropriate cell addressing. (2) In J4 calculate the recycling fee for the remaining provinces by multiplying the Total Number of beverages sold in the remaining provinces by the Recycling Fee located on the Pricing & Recycling Fees worksheet. Apply Order of Operations in this formula and appropriate cell addressing. (2) Position the active cell anywhere in Column K. Insert a column to the left of this column. (2) In the new blank cell K3 enter the text Distribution Charges, (2) In K4 calculate the Distribution Charge as 5% of the Total Sales Revenue excluding Recycling Fee. (2) In L4 enter a formula that will compare the Retail and Wholesale Prices located in the Pricing & Recycling Fees sheet to calculate the % markup on the sale of Liquid Sunshine Organic Orange Juice. Note: Incorrect formatting will be corrected in Task 3. (2) For assistance on this formula: https://exceljet.net/formula/get-percent-change Note: if the formula is entered correctly, the result will be a positive value. In M4 enter a formula that will calculate the Total Profit for the Liquid Sunshine Organic Orange Juice. Your formula will consider the difference between Retail and Wholesale Prices, multiplied by the Total Number of Beverages Sold. The Distribution Charge is then subtracted from the profit. Apply Order of Operations in this formula. (2) Using a function, in C15, total the numbers in this column. Copy or fill the function across to cell M15, delete the total in L15 (2) In N4 enter a formula to calculate the Liquid Sunshine Organic Orange Juice % of Total Profit referring only to the values in column M in this formula. Apply appropriate addressing schemes and copy or fill the formula down the column to row 14. (3) In B17 use a function to determine the number of types of beverages located in column A. Complete the remaining statistical and math functions in B18:B22, applying the appropriate ranges to each function. (6)

Hide the Pricing & Recycling Fees worksheet (3-point penalty if not done) Save your file.

Task 3: Sales and Profit Report – Basic Formatting Skills (47 marks) 3.1 3.2

Insert 2 new rows at the top of the 3. Sales and Profit worksheet. (1) Create worksheet headings as specified below and similar to the following including:

(note borders may not appear as set)

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 2nd line with the Last Names of your team members. Example: Nelson Jhamb Flottat – Consultants (1) 3.2.c. On the 4th line of the heading, type in the current date, this is not a function. (1) 3.2.d. Increase the row height to 90 to fully display the main title. (2) 3.2.e. Horizontally and vertically centre the contents of cell A1. (1) 3.2.f. 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 Southern Alberta Institute of Technology │ School of Business

Page 3

Business Productivity Tools and Technology 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.g. Increase font size to 20 and apply bold to the first line. Increase font size of sub-headings to size 14. Apply chosen font colours consistently to the titles. (1) 3.2.h. Add a thick coloured border to the top and bottom of cell A1. Add a thin coloured border to the left and right of cell A1. Each border will match the title font color. Apply a contrasting fill colour. (2) 3.3 Apply the Comma number format and with 0 decimal places to any cell that reflects a quantity value (C6:G17). (2) Note: the format may change automatically by Excel. 3.4 Apply the Accounting 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 align with values with $ symbol. 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 B19 through 24 format results appropriately (dollar values should have Accounting style. Numbers sold should not have currency symbol, however, should have the comma as the thousands separator, and should have no decimals.) (2) Note: Every cell displaying numbers must have one of the above formats applied. If any cells are not formatted it will result in a 3 point total deduction. Ensure numbers align within columns. 3.7 3.8 3.9

3.10 3.11 3.12 3.13

Apply single-accounting underlines to H16:K16 and M16. Apply double-accounting underlines to H17:K17 and M17. (3) Apply Top and Double-Bottom Borders to cells C17:G17. (3) 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. (3) Select the beverage names A6:A16. Apply right alignment and increase the indent twice for spacing. Add bold to the text. (2) Set the width of columns C through G to 11.00. Set the remaining columns to best fit. (2) Use the Format Painter to apply formats applied to cell A5 to A17. (2) Simultaneously freeze Column A and the first 5 Rows. (1)

3.14

Add a Note (Office 365) or Comment (Office 2019) to cell K5. The text for the note will contain your First and Last name on the top line – add the text “Distribution charges include carbon tax” on the line below. Format the 2nd line text to italic. Fill the background with a custom color mix of Red: 224, Green: 252, Blue: 224. 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 Distribution charges include carbon tax is on a single line. Position the note in the 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 354) or Comments (Office 2019) view the following video: http://www.mrexcel.com/excel-tips/excel2020-old-style-comments-are-available-as-notes/ (5)

3.15

Apply a conditional format to the numbers of beverages sold in cells C6:F16 using your choice of an “Indicator” Icon set. Edit the rule to identify the numbers over 4000 as Green, over 3000 as Yellow and remaining values as Red. (2) Position in cell A6. Save your file and copy your file to a backup storage location or ensure Sync to OneDrive is active.

3.16

Task 4: Sales and Profit Report – Formula Verification (3 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. Southern Alberta Institute of Technology │ School of Business

Page 4

Business Productivity Tools and Technology 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 4.3

Rename this copied sheet to 2. Sales and Profit Formulas. Change the display on this sheet to Show Formulas. Do not Hide formulas. (3)

Task 5: Sales and Profit Report – Chart Skills (38 marks) The CEO requires visual presentation of your results. The CEO would like to see the Alberta unit sales for each type of beverages. Additionally, the CEO would like to see a visual representation of the % Markup compared with the % of Total Profit for each beverage type. 5.1

5.2

Select the ranges to plot from the 3. Sales and Profit Report. You will use the Beverages types in the A column and the numbers sold for Calgary and Edmonton locations as the data. The non-adjacent data ranges will be A5:A16; C5:D16. Create a Clustered Column Chart, located on a separate chart sheet named 4. Alberta Beverages Sold. (3) Move the 4. Alberta Beverages Sold sheet to the right of the 3. Sales and Profit Report. (1)

5.3

Customize the Chart as follows: 5.3.a. Change the color of the North Calgary Mall data columns to a different solid fill colour, matching the previous colour selection for fonts and borders. (1) 5.3.b. Change the South Edmonton Mall data columns to Pattern –Diagonal Stripes: Wide downward. Add a 2 pt. border using the same color as the solid filled data bars. (2) 5.4 Add a Data Table with No Legend Keys. (2) 5.5 5.6

Modify the Value axis Major unit step value to reveal more units on the chart, but still be easily readable. (2) Add a Value axis title Number of Beverages. Set the font colour of the axis title to the same as the solid data series and font size to 16 pts. Change the chart title to Alberta Beverages Sold. Set the title font colour to the same as the solid data series and font size to 20 pts. (3)

5.7

Format the Plot Area of the chart to display a copyright free online picture of beverage containers. Set the image Transparency to 65% (3)

5.8

Add a Data Label Callout to only the North Calgary Mall – NRG Drink data point. Move the callout above the NRG Drink data columns and increase the font size to 12, add Bold and the font color should match the chart colors (3). Modify the Data Label to display only the Series Name and Value (2)

5.9 5.10

Change the sheet tab colour to be consistent with your previous colour selection. (1) Return to the 3. Sales and Profit Report worksheet. Create a new Line Chart with Markers chart located on a separate chart sheet named 5. Markup Impact on Profit. This chart will compare the % Markup to the % Total Profit for each of the Types of Beverages, using the non-adjacent data ranges of A5:A16; L5:L16; N5:N16 (3) Move the chart sheet to the right of the 4. Alberta Beverages Sold chart. (1) Edit the chart title to Projected Markup Impact on Profit and increase the font size and add the appropriate font colour. Change the chart colours to a monochromatic palette that matches that your colours. (3) Add a data label to the highest data point marker of the % Markup series showing the Series name, Category name and Value. Do not uncheck the Show Leader Lines option. Move the data label away from the data point to view the leader line. (3) Increase the size of the data label and colour the data label text to a similar colour of the data series. (2) Apply the previous steps for the highest point of the % of Total Profit series. (1) Add a Linear Forecast for the % Markup Data. (2) Set the forecast period to be Forward for 3 periods (3). See: https://www.howtogeek.com/429126/how-to-work-with-trendlines-inmicrosoft-excel-charts/ for assistance in working with chart trend lines.

5.11 5.12

5.13

5.14 5.15

Southern Alberta Institute of Technology │ School of Business

Page 5

Business Productivity Tools and Technology 5.16

Save your file and copy your file to a backup storage location.

Task 6: 2021 Orders Listing - Large Data Sets – Sort, Table and Filter Skills (25 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 in or ordered online through their website any day of the year. 6.1 6.2

Activate the 6. 2021 Orders Listing worksheet. Add a column heading title Ship Date in column F. Open the file ExcelProject Additional Data. From the 2021 Orders Listing worksheet, copy the values under the Ship Date and paste in your project file, starting in cell F2. Add a second new column to the table named Delay. (2)

6.3

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 2021 Orders Listing. 3-point penalty if headings not set as defined. Convert the data to a table, name the table Orders2021. Apply any table style Medium 8 through 14, matching your color scheme if available. (3) In G4, create a Structured Reference formula to calculate the number of days between the Order and Ship Dates, this will be a positive number. (2) Add a Total row to calculate only the Average Order Amount, replace the text in column A with Average Order. (2) Within the list, sort data by Province then by Salesperson both in ascending order. Add another sorting level chronologically ascending by Order Date and then descending by Order Amount. (3) Using the Fi...