Getting Started
- Download and open MRK264 W24_Excel Test 1 Worksheet.xlsx workbook and Excel Test One Instructions.docx from Blackboard > Week Seven > Test One folder.
- Save the workbook in the format MRK264 SFF_firstname.lastname.xlsx (use your name!) [2]
- Complete the following tasks. Remember to save your workbook regularly!
Documentation Worksheet
1. Enter your full name in cell B3 [1]
2. Enter a function in cell B4 that will always return the current date and format it using the Long Date format (i.e. October 19, 2023). [3]
In the Statistics Worksheet
3. Use an IF statement in cell G5 that returns the word “No Change”.
if Total Sales are greater than or equal to $400,000 and “Review”
if less than $400,000. Copy the formula down to cells G6:G8 [5]
4. Create a suitable formula in cell H5 that will display the Estimated 2024 Total by using the Total value in F5 and the Proposed Annual Increase in Cell F11 as inputs. Use an absolute reference where appropriate [2 Marks]. Copy the formula down to cells H6:H8. (Hint: you did similar formulas in class and there is more than one possible variation of the same method). [4]
5. Create a suitable formula in cell J12 to calculate the Stretch Target Sales Total, this time using the Stretch Target Sales growth rate in Cell J13. [4]
6. Use Goal Seek to determine the percentage growth rate needed in cell J13 to achieve a stretch target total Sales in 2024 sales of exactly $2,205,000. [4]
On the Category Worksheet
7. Enter the VLOOKUP function into cell B4. Use A4 as the Lookup_value and the entire table beginning in row 7 as the Table_array. Use an exact match Range_lookup. The lookup result should display the Category in B4. Test that your function works by selecting a Customer ID from the drop-down list in A4. [5]
8. Without using a IF function, but rather a similar variant, enter the relevant function in Cell H4 to count the number of customers that are based in the “East” Region. [5]
On the Customers Worksheet
9. Merge and Center the Heading in Row 1 between columns A:G, then left align the title. [2]
10. Format the merged cells in Row 1 in the Heading 1 Style [1]
11. Enter the following names to the bottom of the list: [2]
|
George |
Cheng |
Funding Equity Corp |
8039 Howard Ave |
East Toowoomba |
QLD |
|
Peter |
Jones |
Cox, J Thomas Jr |
7 Hugh Wallis Rd |
Koolan Island |
WA |
|
Sally |
Miller |
Motel 6 |
8 Austin Bluffs Pky |
Bimbijy |
WA |
12. Enter a function in cell A3 that will count the number of contacts (Hint: Count Alphanumeric). [3]
13. Change the Font of cells A3:G46 to Calibri with Font size 11. [2]
14. In cell G6 enter jean.cecchinato@domain.com and then use the Flash Fill function to complete the remaining email addresses. [4]
15. Use Autofit to resize the width of columns A:G [1]
16. Change the width of columns C and D to exactly 32 [2]
17. Delete row 15 [2]
18. Apply the All-Borders border style to cells A5:G45. [2]
On the Cookies Worksheet
19. Set an indent of 1 for the labels in the range C3:C5. [2]
20. Use the appropriate functions in cells C3:C5 to calculate the related results based on the Quantity column in the data. [3]
21. Use Conditional Formatting to place Orange Solid Fill Data Bars in the Total Value column, cells H8:H80. [2]
22. Use Conditional Formatting to Highlight Quantity values that are Less Than 120 with Light Red Fill with Dark Red Text. [2]
23. Use Conditional Formatting to Highlight Quantity values that are Greater Than 120 with Light Green Fill with Dark Green Text. [2]
24. Set the Page Layout margins to Normal. [1]
25. Set the Print Area to A1:H80. [1]
26. Set Print Titles to repeat Row 1 on each page. [2]
27. Set a page footer that includes your name in the Left, the Sheet Name in the centre and the Page Number in the right footer area. [3]
On the Charts Worksheet
28. Create Column Sparklines in the Location Range H5:H15 using the Data Range B5:F15. [2]
29. Create a 3D Pie Chart using the non-adjacent range A5:A15, G5:G15. Set the title to Canadian Sales, move the legend to the right side and add data labels formatted as Best Fit. Pull any single pie piece out slightly. Position the chart so that the top left corner sits in cell A16. [4]
30. Using the provided example, recreate the Combination Chart as exactly as you can, then position it directly below the example image. Do not copy and paste the example, you must Insert a new chart and modify it to match. [12]
Ensure that you saved your workbook in the format MRK264 SFF_firstname.lastname.xlsx and then upload your completed Excel file to Blackboard.