advanced analysis using oo calc

8/3/2019 Advanced Analysis Using OO Calc

1/15

Advanced analysis using OpenOffice.org CalcOpening/importing filesCreating subtotalsUsing outline functionalityMultiple OperationsUsing the data pilotEditing a data pilot table

Filtering a data pilot tableRefreshing a data pilot tableEditing a data pilot table 2ScenariosCreating scenariosModifying and deleting scenarios

Opening/importing files

Besides typing data in manually, there are several ways to get data into a spreadsheet. Calc will let you import data fromother systems.

If you select open from the file menu and click the files of type dropdown you will see that OpenOffice is compatible withan huge number of programs and file formats.

If you scroll down a bit, you'll find a whole area that deals with spreadsheets. Often you can just open up a file andOpenOffice will convert it for you then you can immediately begin to use the file.

If the source system can't produce a document compatible with OpenOffice you can often export the data to a delimitedtext file. This is a good thing and a bad thing. It's good because it will allow you to import data from almost any system intoCalc. It's a bad thing because you'll have to do a few extra steps to properly import the data.

To demonstrate how to import text files, we'll import a text file containing sales transactions from the Fictitious GardenCenter sales database into Calc.

First, we'll select the file > open command from the menu, then open the salestransaction.csv file.

Calc will display the text import screen. The purpose of this screen is to let you control how the data in the file is mappedto cells in the spreadsheet.

Text files usually contain data fields separated by characters called delimiters. The delimiter is usually a comma or a tabsurrounded by either single or double quotes.

Our file is delimited by double quotes and a comma. We'll make sure the comma box is checked, and the text delimiter isset to double quotes. Calc will preview the import at the bottom of the screen.

One of the columns contains a date, Calc will let us specify that the column is a date and specify the format of the date.Right click on the column and specify a date in Month/Day/Year format.

The import looks fine, so we'll click the OK button

Creating subtotals

The sales details sheet has lots of information in it. The reason for importing the data was to use Calc's analytical tools tobetter understand sales patterns and trends at the garden center.

1


2/15

Creating subtotals

One of the easiest and most useful tools Calc offers is the subtotals feature. As its name suggests, subtotals will allow youto break data down into logical groups and calculate subtotals for each of those groups.

The subtotals feature also leverages Calc's outline functionality so you can analyze summary information and quickly drilldown to the details upon which the summaries are built.

Let's take a look at how to build a subtotal report.

First, select the cells you want to subtotal. Then select the subtotals function from the data menu.

Next, select a group by column. Calc will create a subtotal for each distinct value in the column. In this example letsgroup by category. This will group all of the values together for each category in the list and create a subtotal.

2


3/15

The next step is to tell Calc how to calculate the sub total. In this case well sum all of the entries in the Amount columntogether for each group. To do this, check the Amount field, then select sum.

Let's add a few more subtotal calculations, lets create a sum of total cost and a sum of quantity.

Click the OK button and Calc will subtotal our spreadsheet grouped by category, with a subcategory calculation for eachof the items we specified - amount, total cost, and quantity.

3


4/15

Using outline functionality

Notice how Calc inserted outline controls around our spreadsheet. There are also numbers in the outline area. These

numbers represent levels of grouping in the subtotaled worksheet.

4


5/15

Clicking on the number 1 shows the highest level of subtotal calculation, the grand total.

Level 2 shows us the next level down. The category subtotals.

And finally level 3 shows us the individual sales transactions.

The plus and minus icons in the outline area will let you collapse and expand the groupings as you wish.

If you want to turn off the outline feature, select outline from the data menu then select remove.

Multiple operations

Suppose Fictitious Garden Center is considering growing a new line of trees but theyre not sure whether it makes sensefrom 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, itwould be easier to decide if they should grow the trees. And if they do grow the trees this spreadsheet will help determinehow the products should be priced.

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

5


6/15

The hard way is to manually enter all of the values and formulas for each cell. The easy way is to use Calcs multipleoperations 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 injust 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 otherfixed 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 f ixed cost.

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

Now that you have your basic formula and the values, you have completed the first step. But, you only see profitability forone 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 aquantity 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, well use Calc to fill the values in for us. To do this youll need to enter thefirst two values then select them both. Next, click the handle at the lower right corner of the selected cell and drag to thecell where you want the data entry to stop. Calc is smart enough to look at the pattern that exists between the first 2 cellsand 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 youre finished with the rows, you'll want to substitute the quantity variable with the columns that reflect thequantities.

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

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

Multiple operations

Suppose Fictitious Garden Center is considering growing a new line of trees but theyre not sure whether it makes sensefrom a financial standpoint. To decide whether they should do this or not they want to do a profitability and pricinganalysis.

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

6


7/15

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

The hard way is to manually enter all of the values and formulas for each cell. The easy way is to use Calcs multipleoperations 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 otherfixed 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 f ixed cost.

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

Now that you have your basic formula and the values, you have completed the first step. But, you only see profitability forone 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 aquantity 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, well use Calc to fill the values in for us. To do this youll need to enter thefirst two values then select them both. Next, click the handle at the lower right corner of the selected cell and drag to thecell 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 substitutethe values listed in the rows for the sales price variable.

When youre finished with the rows, you'll want to substitute the quantity variable with the columns that reflect thequantities.

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

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

Editing the data pilot table

So far we have mostly focused on how the data can be grouped and organized, but there is more information to bediscovered by changing the way the data is totaled.

In our latest example, each intersection of a row and column represents total sales. If you remember, we created this byplacing the amount button in the data field area and choosing to sum the values.

7


8/15

What if we wanted to see more than just the total sales? What if we wanted to show the number of items sold as well? Wecould do this by creating another summary to count the items.

Select the item field and drag it to the data field area. Notice that the calculation defaults to sum thats not what we wantin this case. We need to change the summary method from sum to count. To do this, double click on the item button.

The next screen you see lets you tell Calc how to compute a total. There are several options (in addition to sum) includingaverage, count, standard deviation, min, max and others.

In our case we want to count the occurrences of an item so we'll select count.

If we were to hit the OK button now, our data pilot table would show both the total sales, and the number of items sold foreach combination of sales rep and promotion.

The final thing to cover in this section is the concept of adding multiple fields to a row or column. To keep things simple,we'll remove the calculation we just created so that the data pilot table only shows sales amount by promotion by rep.

Suppose we wanted to further break down promotion by category. Just drag the category button into the row field area,just below the promotion button.

8


9/15

Where we used to have just one row per promotion we now see a row for each promotion plus additional rows for eachcategory that exists in a promotion.

Our table now represents sales by promotion, broken down by category, broken down by sales rep.

If you've never worked with a tool like this before this might seem a bit confusing, but it's easy to see how powerful thedata pilot tool is, and how quickly you can generate sophisticated tables to answer your questions.

By the way, when you have more than 2 or more fields as row fields you can drill from one field to the details of anotherfield.

Just double-click on an item in the fi rst row field and the details of the second field will be hidden for that item. Double clickit again to make the details reappear.

You can hide the details for all items and expand and collapse them as you wish.

To enable this feature, expand the data pilot interface by clicking on the more button then make sure the enable drill todetails button is checked.

9


10/15

Notice the total columns and total rows check boxes. Checking these boxes will let you show or hide totals for the rows orcolumns.

We'll leave the totals, but to simplify things for the next module we'll remove the category field.

We'll create the data pilot in a new worksheet. And finally we get to click the OK button.

Filtering a data pilot table

Notice the filter button near the top of the data pilot table. This lets you filter the data upon which the data pilot table isbased.

This filter works just like the standard filter feature we saw earlier. Let's say we want to only show data for plantscategorized as conifer trees. Select category as the filter field name, a condition of equals and select conifer trees fromvalue list box.

Instead of showing data for all plants the data pilot table only calculates totals for conifer trees.

Earlier in this tutorial we covered how to use the standard filter screens in detail. Filtering the data pilot table works thesame way.

If you want to remove the filter from a datapilot table click the filter button, change the field name to none and click OK.

Refreshing a data pilot table

10


11/15

Data pilot tables are summaries of detail data that exists somewhere else in the spreadsheet. If the underlying data uponwhich the datapilot is based changes, you'll usually want to refresh the data pilot tables. Otherwise the data pilot tableswont reflect the changes that were made to the underlying data.

To refresh a data pilot table, click on a cell, select the data pilot option from the data menu, then select refresh.

Editing a data pilot tableIf you want to edit a data pilot table click on a cell, choose the data pilot option from the data menu then select start

This will display the data pilot definition screen where you can modify the table as shown in the previous module. Whenyou are done, click the OK button to apply your changes or click the cancel button to leave the data pilot table in itsoriginal state.

To delete a data pilot table, click on a cell, select the data pilot option from the data menu then select delete.

Scenarios

Anyone involved in business needs to deal with uncertainty. No one knows what the future will bring, or how external factors willaffect a company. You can however, create models in a spreadsheet that contain different scenarios that may happen in thefuture. You can then estimate the effects of these different scenarios and create contingency plans you to mitigate badsituations or improve good situations.

Fictitious Garden Center is preparing a profitability forecast spreadsheet.

11


12/15

This spreadsheet will predict quantity sold, costs, revenue, and profits based on three different scenarios (the most likelyscenario, worst case, and best case scenarios.)

To see how each scenario affects profitability, select a scenario from the drop down menu. Each time you select a scenario fromthe list, the projected performance table will automatically show the values for the newly selected scenario.

Creating Scenarios

A scenario generally consists of 2 parts. The first part is a calculation (or series of calculations) that show the outcome of variousscenarios. In this case the calculations generate estimated sales, costs, and profits.

The second part is a set of scenario variables. In this case, change in quantity sold, change in price, and change in cost. Thesevariables are used in the calculations to generate forecasted values.

Any change to these scenario variables will change the forecasted values in the spreadsheet.

Because the focus of this module is on how to create and use scenarios and not on creating calculations; we'll open a sample thatalready contains the necessary calculations. The sample spreadsheet is named scenariospreadsheet.ooc.

12


13/15

In this scenario there are 3 things that change; the quantity sold, the sales price and the cost to produce the items that are sold. Tocreate a scenario, select these 3 cells, then select the scenarios... option from the tools menu.

Name the scenario "Most Likely". It's a good idea to enter a comment describing the scenario in case you forget what the scenariorepresents later.

13


14/15

When you're finished, click the OK button.

Calc will generate a dropdown list containing your scenario, outlined by a gray frame.

Next, complete the same steps to create the worst case scenario.

Select the cells, select scenarios from the tools menu then enter a name and description.

Then click OK.

Repeat the same steps again for the best case scenario.

Now that you have placeholders for the three different scenarios you can populate each of the scenarios with the appropriate values.

To build the most likely scenario, select Most Likely from the scenario dropdown list. Assume that change in qty. sold is 8%. . Enter8 percent in the change in quantity cell.

Next, assume that the most likely change in price is 3%. Enter .03 in the change in price cell.

Finally, assume that the most likely change in cost is 2%. Enter .02 in the change in Cost cell.

Now that the definition of the most likely scenario is complete, it's t ime to define the worst case scenario.

Select worst case scenario from the dropdown list and change the values.

The projected worst case scenario would be where quantity sold is down 10%, prices drop 20% to liquidate inventory, and costsincrease 10%.

Finally, complete the same steps to add values to the best case scenario.

For this scenario sales volume could grow 15%, Costs would be reduced by 7% due to volume pricing and other efficiencies, pricescould increase 5% because the Garden Center would not have to discount as deeply.

Now that you have completed the scenario definitions, you can switch between scenarios at the click of a button.

14


15/15

Modifying and Deleting Scenarios

To modify an existing scenario, click the navigator icon, then click the scenario icon.

Right click the scenario you want to change then select properties from the popup menu. A screen will appear that willlet you change the name, description and frame color for the scenario as well as a few other options.

If you want to delete a scenario, right click the scenario you want to delete, then select the delete option from the menu.

Summary

Weve covered a lot of material in a short time in these tutorials. These tutorials have covered the most important piecesof OpenOffice, The types of things you will use over and over again. Now you should know things about Calc you didntknow before

Source: http://www.learnopenoffice.org/

15

advanced analysis using oo calc

Documents