Embedded Files
Keyboard shortcuts for excel
Keyboard shortcuts for excel
Excel tutorials online
Excel tutorials online
My store
My store
Create a spreadsheet for your imaginary store. It will include a price list, sales tax calculations (assume the 19% sales tax/VAT rate) as well as the number sold for each item for 5 days of the week. Calculate the total number sold for each item, the sales this generated, as well as the tax due on this amount. Make sure you use the SUM() function for the totals.
A sample structure of the spreadsheet can be found here:
The tax calculation is shown here:
The number sold calculation:
2. Calculate the daily sales for each day of the week, using the so-called absolute references to your price-incl.tax column (use the F4 key to "lock" the cells when using the "magic square" to auto-fill.:
3. Create the following charts based on the data you created in the previous steps. Make sure you highlight the labels as well as the data. When your labels are not adjacent to the data, select the labels first and then, while holding the CTRL key, select the corresponding data. Then, click "Insert recommended chart".
Your charts should look like the ones below, don't forget to add suitable labels and a title:
Calculating the costs and the profit
Calculating the costs and the profit
Watch the video below to learn how to estimate the unit costs, how to calculate the variable costs per item, the gross profit per item, how to estimate the fixed costs and finally, how to calculate net profit:
Below, you fill find screenshots that illustrate how to calculate the variable costs per item, gross profit per item, as well as sample fixed costs:
Total variable costs per item
Gross profit per item
Sample fixed costs
Gender Breakdown
Gender Breakdown
1. Open the gender breakdown spreadsheet where students are listed by grade and gender (you will find the file in the class Team's Files section). Based on this raw data, create a frequency table where you will calculate the number of boys and girls in each class as well as the totals for Middle School and High School. You will use the COUNTIF() function for this - see the screenshot below.
2. Create charts illustrating the gender breakdown in the individual classes, all of MS, all of HS, as well as all of MS and HS combined. See the screenshots below. Don't forget about suitable data labels as well as chart titles. For the first chart (clustered column chart) showing the individual break-downs for each class, you need to select the entire table, including all the vertical and horizontal labels, except for the totals.
For the first chart - a clustered column chart that features two series - boys and girls - you will want to select the data, labels, and the column headers, as well - see the screenshot on the right:
Source data for the clustered column chart. Create a rectangular selection, the cell that says "class" will be excluded automatically
Student Attendance
Student Attendance
Open the "Student attendance September 2019" spreadsheet. You can find it on MS Teams in the files for the team. This lists all students number of days they were present, absent, and tardy.
Do the totals for each category using the SUM() function.
Create a pie chart showing the proportion of absences and tardies compared to the days students were present. Don't forget to add suitable data labels as well as a chart title. See the screenshot on the right.
Calculate the maximum number of presence, absence and tardies in the month of September. For this, you will use the MAX() function. Make sure you don't include the totals in the range for your MAX() function.
Now open the "Attendance September-November 2019" file which contains data for the past three months. Save a copy of this file in your Onedrive.
Calculate the totals per student for the montsh of September to November.
Calculate the grand totals for each month (at the bottom of the spreadsheet).
Create a chart that will show how total attendance has changed throughout the months.
pizza payroll
pizza payroll
Open the Pizza Payroll spreadsheet and paste its contents into MS Excel (the desktop edition).
Fill your name in at the top and then do all the exercises listed at the bottom of the sheet, which are as follows:
Apply an appropriate currency format to data in column B.
Calculate the Amount Earned for each employee.
Calculate the Total Weekly Wages Bill.
Create a suitable chart that will show the both the rate of pay and hours worked for each employee. Don't forget to include suitable labels.
Print the worksheet with formulas showing (press CTRL+` to toggle between seeing the formulas and the calculated values). Make sure your sheet prints on one page only - adjust the column widths and in the print settings set the scaling to "Fit Sheet on One Page". (see the screenshot on the right)
Where to find the ` character on the keyboard:
Setting the Scaling:
calculating net salary and labor costs
calculating net salary and labor costs
Open the Net Salary and Cost of labor spreadsheet and paste its contents into MS Excel (the desktop edition).
Fill your name in at the top and then do all the exercises listed at the bottom of the sheet, which are as follows:
Apply an appropriate currency format to data in column C.
Fill out all the calculations (cells in orange) - use formulas where appropriate.
Print the worksheet with formulas showing (press CTRL+` to toggle between seeing the formulas and the calculated values). Make sure your sheet prints on one page only - adjust the column widths and in the print settings set the scaling to "Fit Sheet on One Page". (see the screenshot above)
Page updated
Google Sites
Report abuse