RSS Feed

Posts Tagged ‘spreadsheets’

  1. The Power of Spreadsheets

    14 August 2010 by shartley

    Sal's Sunglasses Spreadsheet

    Following my student teacher’s lesson on break-even analysis I taught Year 11 Business Studies students how to use spreadsheets to play around with the numbers involved.  In a classroom with computers they  carefully,  step by step, followed what I was setting up in a spreadsheet (seen via a data projector):

    • Sal’s Sunglasses
    • Selling price of sunglasses  $10.00
    • Cost of purchasing sunglasses $4.50
    • Cost of purchasing sunglass case $0.50
    • Total cost per unit ?
    • Lease of retail premises $2,000
    • Wages (for proprietor) $800
    • Service fee to retail centre (security, cleaning, electricity) $200
    • Total fixed costs ?

    Through Q & A with the students formulas were chosen for the ? calculations.

    Then I showed how to name a cell and use [F5] to Go To the named cell.  (Consequently there were cells named with all sorts of strange and rude names.)  Officially we had P for price, VC for variable costs (total cost per unit) and FC for fixed costs.

    Then we constructed a table, and I demonstrated how to use series fill for the various quantities of sunglasses that could be sold.  Again students contributed suggestions for formulas to use.  Just one student knew enough about spreadsheets to race ahead (but he hadn’t known about naming cells).

    • Possible quantity of units sold (Q)
    • Price of goods sold (P)
    • Total Revenue (TR)
    • Fixed costs (FC)
    • Variable costs (VC x Q)
    • Total costs (TC)
    • Profit (TR – TC)

    Using the figures supplied the students discovered that the break-even point was when 600 sunglasses were sold.  “That’s a lot”, many of them exclaimed.  “So what should the owner do?” I responded.  Most students said to raise the selling price.  I told them to [F5] “P” and change the price to $15 instead of $10.

    Now up to this point there was a reasonable level of enjoyment from learning to do something different but at this point, when they saw the whole table and thus the break-even point change with the alteration of one cell, they were blown away.  They had grasped the power of spreadsheets.

    Last of all they learnt to construct a graph and label it without relying on automatic selections.  They were determined to make the graph accurate and were able to identify when it wasn’t.  The determination came from understanding there were genuinely good reasons for making a break-even graph.  Many of the students left excited about how they could use break-even analysis in their Business Plan Assessment (due Monday).

    This lesson was a real kick for me and the students.

    Teaching is fun.

Skip to toolbar