Lesson 4: Ron’s gourmet hotdogs

Ron set up a hotdog truck and wants to know some key information about the hotdogs he sells: how much money he makes per hotdog, and per day. He is also getting lots of questions about the nutritional details of his hotdogs so he wants to reorganise his menu to present a number of light options (under 300 calories) grouped together.

In this spreadsheet task, you will use the skills you have learned to share this info with him.

Note: This task contains no new skills, but offers you opportunities to practice the skills you have learned in previous units. Try to complete the tasks without extra help, but if you need extra help please check the video tutorials from previous tasks.

Download the spreadsheet here.

Tasks

  1. Format the spreadsheet appropriately. This includes:
    • Borders around all cells in the ranges A1:J10, E11:J11 and J13.
    • Column headings in bold with a light grey background.
    • All cells containing money formatted as $ (columns C, D, E, I and J).
    • Add two rows at the top of your spreadsheet and insert “Ron’s gourmet hotdogs: Saturday sales” as your title. Merge and center this across the width of the entire table and make it 24pt bold font.
  1. Calculate the profit made for the Mexican hotdog in cell E2. Replicate this down the column for all of the other hotdogs.
  2. Any hotdogs Ron made but didn’t sell were wasted. Use a formula in column H to calculate how many hotdogs were wasted.
  3. Calculate the profit per hotdog in column I by multiplying the profit each by the number sold.
  4. Calculate the cost of waste by multiplying the ingredient cost by the wastage.
  5. In cell F11, calculate the total hotdogs made. Replicate this formula all the way across to cell J11 to calculate totals for each of those columns.
  6. In cell J13, calculate the total profit by deducting the total wastage from cell I11.
  7. Answer the questions in cells A12:A15 using formulas.
    • For average calorie content, average profit use =AVERAGE( )
    • For “Light” options? (<300 cal) use =COUNTIF(B2:B10,”<300″)
  1. Create a bar chart showing how many of each hotdog we sold.
  2. Create a pie chart showing how much of our profit each hotdog made.