Suppose Fictitious Garden Center is considering growing a new line of trees but they’re not sure whether it makes sense from a financial standpoint. To decide whether they should do this or not they want to do a profitability and pricing analysis.

If there were a spreadsheet that listed the profit amount for several different combinations of quantities and price points, it would be easier to decide if they should grow the trees. And if they do grow the trees this spreadsheet will help determine how the products should be priced.

If you want to create a spreadsheet that contains lots of calculations like this there’s two ways to do it; the easy way and the hard way.

The hard way is to manually enter all of the values and formulas for each cell. The easy way is to use Calc’s multiple operations feature to enter the values for you.

Once you've learned how to use the multiple operations feature, you'll be able to create a detailed spreadsheet like this in just a couple minutes - maybe less.

The first thing you need to do is to create the profitability calculation and enter the required values.

To determine profitability, you need to know the fixed price, the per item cost, the sales price and quantity sold.

In order to grow the new trees the garden center will need to invest 7,500 for equipment, a lease on more land and other fixed expenses. They estimate each tree will cost them $9.00 to produce.

The profit calculation would be quantity times (sales price – per item cost) – the fixed cost.

You may have noticed that Calc will allow you to enter a column or row name surrounded by single quotes in place of the more common cell address. You could have entered the calculation as b6 * (b5 -b4) – b3. Using header names makes it easier to understand.

Now that you have your basic formula and the values, you have completed the first step. But, you only see profitability for one combination of quantity and sales price.

To be useful, you'll need to perform this calculation for several different sales prices and quantities.

List the quantities you would like in a new row starting at 500 and incrementing by 500 each time until you reach a quantity of 5000.

Next, create a sales price row starting at 20 dollars and incrementing by $5 each time until we reach a sales price of $50.

Rather than typing these values in manually, we’ll use Calc to fill the values in for us. To do this you’ll need to enter the first two values then select them both. Next, click the handle at the lower right corner of the selected cell and drag to the cell where you want the data entry to stop. Calc is smart enough to look at the pattern that exists between the first 2 cells and fill in the rest of the cells for you.

Now that the necessary values are entered, you need to apply the formula to each of the cells in the table then substitute the values listed in the rows for the sales price variable.

When you’re finished with the rows, you'll want to substitute the quantity variable with the columns that reflect the quantities.

To do this, select the table area, then select multiple operations from the data menu. Next, tell Calc where to find the formula on which to base the table, then, specify the variable you want to substitute with row values in the table - in this case the variable is sales price.

Finally, specify the column value (which in this case is quantity) and click OK.