Question #1 (5 marks) Worksheet 1. Project Estimation a) Enter the following table in Excel Project Estimate Yard Masters Prepared for: Prepared by: Date Prepared: D. Mayo Student Name date Tasks Hours Cost Weed treatment Aerate Prepare planting bed Plant shrubs Plant perennials Tasks Subtotal: Rate 8 $ 11 $ 15$ 4 $ 12] $ 55.00 85.00 20.00 40.00 35.00 Materials Quantity Cost Weed treatment Aerator rental Ornamental shrubs Perennial plants Materials Subtotal: Price 8 $ 1/ $ 4 $ 12 $ 25.65 75.00 39.95 2.99 Total Estimate: Cost of Most Expensive Task: Cost of Least Expensive Task: Cost of Most Expensive Material: Cost of Least Expensive Material: b) Complete the remainder of the table using formulas and functions. c) Use shading and borders as shown. Question #2 (5 marks) Worksheet 2. Newmark Car Company a) Enter the following table in Excel. Newmark Car Company Earnings by Prof. Mayo Above Average Earnings Last First Crow Dan Dean Rod Doe Jane Martin Mary May Jill Shannon George Smith John Min: Salary Sales Commission Earnings $79,000 $500,500 $55,000 $140,000 $75,000 $450,000 $85,000 $530,800 $65,000 $345,000 $39,000 $174,900 $60,000 $200,000 Max: Total: Average: b) Complete the rest of the table using formulas and functions, note the following: 1. Commission is 1.5% of Sales. Commission is a dollar amount, not a percent. 2. Earnings are Salary plus Commission. 3. The summary section should be completed for the columns Salary, Sales, Commission, and Earnings. 4. In the Above Average Earnings column put a True or a False (IF statement) Use borders and double borders as shown. c) Question 13 (10 marks) Worksheet 3. Adult Bar Company Payroll Using your knowledge of spreadsheet software using formulas and functions where appropriate, make a spreadsheet for the following company payroll Payroll refers to how much you as the business owner need to pay your employees for their labour. You have fifteen employees on the payroll. See the table below Managers are paid $20.00 per hour For the other employees you will decide their minimum wage based on their position, age, and hours worked. See guideline below. Wait state and shows don't A normal work week is 40 hours. Anything over 40 hours is overtime Overtime hours data rate of 1.5 times their normal hourly wage. Determine how much each employee is owed based on the table below for one week Total all the as paid to employees to get a grand total of how much money you will spend on payroll for this pay period Use borders and shading Your spreadsheet mishtirok something like this Employee Age Position Wage Hours Pay A As 25 Manager 60 B. Boyd Manager 56 C. Carmen 23 Wait staff 33 D. Donald Wat stall 45 E. Ericson 21 Wait staff 19 F. Falcone 19 Wait staff 28 G. Gomes 24 Wait staff 36 H. Homes 22 Wait staff 48 Llotona 21 Wait staff 35 J. Jones Wait staff 48 K. Katt 19 Wait statt 35 L. Lemon 18 Busboy 35 M. Mariani 18 Busboy N. Nichols 17 Busboy 0. Ostram 17 Busboy 32 ONTARIO MINUMUM WAGE RATES Minimum Wage Rate Current Rates: Rates as of January 1, 2018 General Minimum Wage $14.00 per hour Student Minimum Wage $13.15 per hour Liquor Servers Minimum Wage 51220 per hour NOTES: Student minimum wage - This rate applies to students under the age of 18 who work 28 hours a week or less when school is in session, or work during a school break or summer holidays. If a student works more than 20 hours a week, then they are paid the general minimum wage Liquor servers minimum wage - This hourly rate applies to employees who serve liquor directly to customers or guests in licensed premises as a regular part of their work Question #1 (5 marks) Worksheet 1. Project Estimation a) Enter the following table in Excel Project Estimate Yard Masters Prepared for: Prepared by: Date Prepared: D. Mayo Student Name date Tasks Hours Cost Weed treatment Aerate Prepare planting bed Plant shrubs Plant perennials Tasks Subtotal: Rate 8 $ 11 $ 15$ 4 $ 12] $ 55.00 85.00 20.00 40.00 35.00 Materials Quantity Cost Weed treatment Aerator rental Ornamental shrubs Perennial plants Materials Subtotal: Price 8 $ 1/ $ 4 $ 12 $ 25.65 75.00 39.95 2.99 Total Estimate: Cost of Most Expensive Task: Cost of Least Expensive Task: Cost of Most Expensive Material: Cost of Least Expensive Material: b) Complete the remainder of the table using formulas and functions. c) Use shading and borders as shown. Question #2 (5 marks) Worksheet 2. Newmark Car Company a) Enter the following table in Excel. Newmark Car Company Earnings by Prof. Mayo Above Average Earnings Last First Crow Dan Dean Rod Doe Jane Martin Mary May Jill Shannon George Smith John Min: Salary Sales Commission Earnings $79,000 $500,500 $55,000 $140,000 $75,000 $450,000 $85,000 $530,800 $65,000 $345,000 $39,000 $174,900 $60,000 $200,000 Max: Total: Average: b) Complete the rest of the table using formulas and functions, note the following: 1. Commission is 1.5% of Sales. Commission is a dollar amount, not a percent. 2. Earnings are Salary plus Commission. 3. The summary section should be completed for the columns Salary, Sales, Commission, and Earnings. 4. In the Above Average Earnings column put a True or a False (IF statement) Use borders and double borders as shown. c) Question 13 (10 marks) Worksheet 3. Adult Bar Company Payroll Using your knowledge of spreadsheet software using formulas and functions where appropriate, make a spreadsheet for the following company payroll Payroll refers to how much you as the business owner need to pay your employees for their labour. You have fifteen employees on the payroll. See the table below Managers are paid $20.00 per hour For the other employees you will decide their minimum wage based on their position, age, and hours worked. See guideline below. Wait state and shows don't A normal work week is 40 hours. Anything over 40 hours is overtime Overtime hours data rate of 1.5 times their normal hourly wage. Determine how much each employee is owed based on the table below for one week Total all the as paid to employees to get a grand total of how much money you will spend on payroll for this pay period Use borders and shading Your spreadsheet mishtirok something like this Employee Age Position Wage Hours Pay A As 25 Manager 60 B. Boyd Manager 56 C. Carmen 23 Wait staff 33 D. Donald Wat stall 45 E. Ericson 21 Wait staff 19 F. Falcone 19 Wait staff 28 G. Gomes 24 Wait staff 36 H. Homes 22 Wait staff 48 Llotona 21 Wait staff 35 J. Jones Wait staff 48 K. Katt 19 Wait statt 35 L. Lemon 18 Busboy 35 M. Mariani 18 Busboy N. Nichols 17 Busboy 0. Ostram 17 Busboy 32 ONTARIO MINUMUM WAGE RATES Minimum Wage Rate Current Rates: Rates as of January 1, 2018 General Minimum Wage $14.00 per hour Student Minimum Wage $13.15 per hour Liquor Servers Minimum Wage 51220 per hour NOTES: Student minimum wage - This rate applies to students under the age of 18 who work 28 hours a week or less when school is in session, or work during a school break or summer holidays. If a student works more than 20 hours a week, then they are paid the general minimum wage Liquor servers minimum wage - This hourly rate applies to employees who serve liquor directly to customers or guests in licensed premises as a regular part of their work