EXCEL SPREADSHEET
A spreadsheet is a software tool that allows large amounts of data to be stored, organised, analaysed and presented in graphical form. A spreadsheet is extremely useful because of its ability to make simple work of a mundane task (for example calculating the average time for all 5000 participants for the Tour Down Under Event). Calculating averages is not difficult but the sheer volume of work is time-­-consuming.
A spreadsheet allows you to create your own “formula” and then apply that formula to all 5000 at the same time, reducing the amount of work necessary dramatically.
Data, information and knowledge are important in all types of businesses. It is very important the correct data is recorded in the business systems to get the correct information and produce the right knowledge.
In this assessment, you will learn how to manage and control data using spreadsheets.
Module 2-­-3 Spreadsheet Exercise
The weekly Spreadsheet exercise is designed to assist you in completing your Excel Assessment.
Instructions:
• Read the Tour Down Under (TDU) Case Study-­- Introduction.doc
• If you want to read the guide on using Excel, read the Basic Skills in Using Excel 2013. Pdf
(pages 1-­-9). However, if you prefer watching the video on how to use Excel, go to this
website: Introduction to Excel -­- https://www.youtube.com/watch?v=J4zq3R8b5dQ
• Create an Individual Participant Workbook
• You need to identify what data are needed when a participant registers at TDU.
• Go to this website to know details needed for individual participant
a. https://satourism.myshopify.com/products/bupa-­-challenge-­-tour
b. Go to select an event and select the BUPA Challenge Tour. You should see this screen:
•
c. The booking page will give you the details of what data you need to store in your excel workbook.
d. For each kind of data, create a column and add 3 sample data. For example, if from analysing the registration page, you found out that the Santos TDU organisers ask for the name, address mobile number, and amount paid, then your Excel workbook should look like this.
Name Address Date of Birth Mobile No. Amount Paid
John 1234 Adelaide St, Melbourne 05/01/1990 +61 40123456 $120
…
…
• Format your worksheet
a. The worksheet tab name is named individual registration
b. The document has a title that says Individual registration (make sure the cells are merged for the title)
c. Change the background colour of the column names to make it noticeable (you can choose the font and colour that you want)
d. Apply styles on the columns. For example, if the data that will be stored are dates, then it should be formatted to the style date with this format dd/mm/yyyy. If the data can contain money, format it using the money format $xxxx.xx
e. Your worksheet should something like this:
• Save your Excel workbook.
• Document either in Excel or in a Word document the step you undertook in producing the Individual Participant Regiistraion. Save your document.
• BACK-­-UP! BACK-­-UP! BACK-­-UP! Do not forget to keep a back-­-up files. Either upload in your google drive or dropbox; and/or just e-­-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
• You can show your work to your lecturer for feedback.
• REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL EXCEL SPREADSHEET ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 7.
Module 2.2 Spreadsheet Exercise
Note that the weekly spreadsheet exercise is designed to help you complete your Excel assessment.
This week we will be focusing on FORMULAS in Excel. A formula is an equation that performs calculations between cells in a worksheet or table. A formula in Excel always begins with an equal sign. A simple formula may contain cell references and operators.
Instructions:
1. Use the Excel sheet you created in Week 3.
2. Read the Basic Skills in Using Excel 2013. Pdf. (pages 9-13) provided during week 3. However, if you prefer watching the video on how to use Excel, go to this website: Introduction to Excel - https://www.youtube.com/watch?v=J4zq3R8b5dQ
3. Using the excel worksheet you have created in week 3, add a column at the end and name it fee paid (Note that participants may pay different registration fee depending on their financial circumstances, that is, discounts are given to some participants)
4. Add 5 sample data in your spreadsheet
5. Create another tab in your excel document and call the worksheet Race-budget,
6. Create a table that looks like this:
7. Under the Income/Funding table, add to the description the data -Registration-
8. In the registration row, add the unit cost of $145 and estimated quantity of 200. Your excel sheet should look similar to this:
9. Compute the estimate amt by using the formula : unit cost * estimate qty (create this formula in the estimate amount cell; excel should automatically compute the estimated amount)
10. Compute for the actual number of registration. CountA is a predefined excel formula that counts cells that are not empty, you can use this to count the number of people who registered for the event (refer to the Basic Skills in Using Excel 2013. pdf or https://www.youtube.com/watch?v=J4zq3R8b5dQ) to learn how to use formula.
11. Using the sum excel formula, compute the total of the estimated amount, and the total of the actual amount.
12. Your excel worksheet should look similar to this (amount reflected will be different because you have a different set of data):
13. Add more data in the description column (jersey, caps, minor sponsors and major sponsors)
14. Add data to the unit cost, estimate qty, actual qty and actual amount for jersey, caps, minor sponsors and major sponsors
15. Your excel worksheet should look similar to this:
16. Save your Excel workbook.
17. Document either in Excel or in a Word document the step you undertook in producing the Spreadsheet. Save your document.
18. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
19. You can show your work to your lecturer for feedback.
20. REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL EXCEL SPREADSHEET ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 7.
Module 3-5 Spreadsheet Exercise
Note that the weekly spreadsheet exercise is designed to help you complete your Excel assessment.
This week, you will learn how to use more functions in Excel (IF, ISBLANK , SUMIF); absolute and relative references; and creating charts or graphs.
Cell references can be relative, absolute, or mixed. A relative reference is a reference that adjusts to the new location in the worksheet when the formula is copied. An absolute reference is a reference whose location remains constant when the formula is copied. A mixed reference is a reference that contains both relative and absolute reference.
A chart is a visual representation of data from your workbook. Charts add a visual element to your workbook and help convey the information ina simple, easy-to-understand manner.
Instructions:
1. Use the Excel sheet you created in Week 4.
2. Read the Basic Skills in Using Excel 2013. Pdf. (pages 11-13).
3. Using the excel worksheet you have created in week 4, add data to the expenses table (except the amount column). We will use a formula to compute for the amount column. Your table should look similar to this:
4. Save your spreadsheet .
5. Compute the amount for each description by using a the IF function. If the value of the quantity is blank then the default value is 1, if the value of the unit cost is blank then the default value is 0. The amount is computed by multiplying the unit cost with the quantity. This is an example of the formula:
6. Save your spreadsheet .
7. Now let’s create a table and graph
8. Create a new worksheet and call it Budget Report
9. We will create a report that looks like this:
Budget Summary Column:
• Create the labels Total Expenses, Total Actual Income and Balance (should look like the figure shown above)
• Total Expenses is from the total expenses from the expense table.
• Total Actual is the total actual amount from the income/funding table.
• Balance is computed by computing the difference of the total expenses from the total income.
Expenses for each category:
• Create the labels for each category.
• To compute for the expenses for each category. You need to use the SUMIF formula. You have to get the sum of all items that belong to the same category. For example, if you want to compute for the total expenses for the location, find all categories equal to location under the category column and add their amount from the amount column.
• In the Expenses table above, it shows that the Jerseys and T-shirts belong to the Other category. Therefore the report in the summary expenses shows that the Other category has a total of 39,000.
• Note: You need to use absolute referencing to computing for the expenses. Refer to this video for details on how to do absolute referencing: https://youtu.be/NmVMjQzseLA
Create the Chart:
• You are free to create any style of chart you want. In this example, I used a bar graph to show the total expenses, actual income and the estimated income.
• For help in creating charts, watch this video : https://youtu.be/Ur3vDy1sLi
10. Save your Excel workbook.
11. Document either in Excel or in a Word document the step you undertook in producing the Spreadsheet. Save your document.
12. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
13. You can show your work to your lecturer for feedback.
14. REMEMBER THAT THIS EXERCISE IS PART OF THE FINAL EXCEL SPREADSHEET ASSESSMENT YOU HAVE TO SUBMIT ON WEEK 7.
Module 3-6 Spreadsheet Exercise
Note that the weekly spreadsheet exercise is the last exercise designed to help you complete your Excel assessment.
This week, we will focus on creating pivot tables. A pivot table is a powerful built-in data-analysis feature in Excel. It analyses, summarises, and manipulates data in large lists, worksheets or other collections. It is called a pivot table because fields can be moved within the table to create different type of summary lists, providing a “pivot”.
Additional Case Study Information:
The organisers of Santos Tour Down Under wanted to have separate spreadsheet of photos, stickers, and calendars sold. These photos, stickers and calendars contain highlights of the Tour Down Under event and photos of South Australia. It hired several sales representative to promote this product. The country, product, month and the amount of sales for each representative is recorded.
Instructions:
1. Use the Excel sheet you created in Week 5.
2. Created a tab called Product Sales and copy the worksheet provided in your worksheet. (Product Sales.xlsX)
3. Your teacher will give you additional data you need to add to your worksheet. Each student will be given a different set of data. If you haven’t received it, please contact your teacher.
4. Read the Problem Solving Skills in Excel. Pdf. (pages 10-13) and Pivot Table Source Data Checklist. pdf
5. You can also watch this video for more details on how to create pivot tables: https://youtu.be/9NUjHBNWe9M
6. Save your spreadsheet .
7. Select the worksheet Product Sales. Click any cell in the list.
8. Insert a pivot table
9. In the pivot table builder – select the COUNTRY from the field name and drag it to the column. Select the PRODUCT from the field name and drag to the ROW and select the SALE form the field name and drag to the values. Your pivot table should look like this:
10. Create the chart using the data in the pivot table.
11. Your final worksheet should look like this:
12. Now create another pivot table and chart, showing the total amount of sale per sales representative. In the pivot table builder, the rows should have the sales representative, columns should have the month and the values contain the sum of the sales. Your final pivot able should look like this:
NOTE THAT TO BE ABLE TO PRODUCE A CHART THAT LOOKS LIKE THIS, YOU HAVE TO SELECT THE CHART AND SWITCH ROW/COLUMN.
13. Save your Excel workbook.
14. Document either in Excel or in a Word document the step you undertook in producing the Spreadsheet. Answer the questions provided to your by your teacher.
15. Save your document.
16. BACK-UP! BACK-UP! BACK-UP! Do not forget to keep a back-up files. Either upload in your google drive or dropbox; and/or just e-mail the file to yourself so that you have a copy in your email; and/or save in a USB drive.
17. YOU SHOULD NOW HAVE THE FINAL EXCEL SPREADSHEET ASSESSMENT THAT YOU HAVE TO SUBMIT ON WEEK 7. Attach the Word Document containing the answers to the questions and steps you undertook in producing the spreadsheet.