International Operation Simulation
APPENDIX : INTOPIA GRAPHICS USING EXCELINTRO.xls
This guide was prepared by Ms. Lisa Fascher and Ms. Carrie Niehoff of the INTOPIA staff.
NOTE: This introduction is not for sophisticated users of Excel.
INTOPIA Companies all receive a printed Quarterly output statement of at least seven pages. In effect, this represents a database, which can be a source of invaluable analysis. This databank is available for your manipulation in the form of an Excel file displaying decisions and outputs for each Quarter, a history cumulating in “Results Archive” in FORMIN.
In principle, you can make any selections from this data goldmine for detailed analysis, and not least, for graphic presentation. To illustrate what can be done we describe below a process for comparative analysis based on Market Research item 79 (MR79), and later we shall give examples from MR27 and the Income Statement of a Co. and Consolidated Financial Statements for all companies (MR74).
NOTE: THESE EXAMPLES ASSUME THAT YOUR COMPANY ORDERED MR ITEMS 79 AND 74, respectively, IN THE QUARTER.
Output is automatically converted to Excel. We will focus onsample uses of this data..
This guide is divided into three parts:
I. Competitive Analysis of Company and Industry Performance (MR 79)
II. Gross Margin and ROS of Co. 4 in Q4 (MR27)
III. Marketing Expenses and Gross Margin, All Companies (MR 74)
I. COMPARATIVE ANALYSIS OF COMPANY AND INDUSTRY PERFORMANCE
Scroll down the pages of your Co. output file in Excel until you reach Market Research Number 79 (this would typically be on the nextto last page. We will assume that you want all company and industry average data. For definition of terms used in MR79, see Guide, p.120? We are including ROI, ROS, and ROA. (You will find that MR79 carries three additional profitability ratios, but information overload is to be avoided in any single chart.) Follow this procedure:
1. In Excel, find and highlight the three lines of ROI, ROS, and ROA data. (Including the
column and row headings).
2. Go to the EDIT menu and select COPY.
3. Go to a blank area at the end of the currentworkbookor open a new workbook in
Excel.
4. Click on the area and go to EDIT menu, and select Paste. This will paste the data in an easily accessiblearea for analysis.
5. Go to Insert Chart or select the Chart Icon on the Toolbar. For more information on
creating charts please see the last page of this guide.
Charts illustrating the above example appear on the next two pages.
II. GROSS MARGIN AND NET OPERATING EARNINGS OF YOUR CO. IN Qn
Bring up your Income Statement in Qn. Let us assume that you are interested in relating the Gross Margins to the Net Operating Earnings of all your product-area lines of business as well as of the Company as a whole. Here is how you might create your chart:
1. Highlight the Total Gross Margin line all the way from the heading to the number on the line appearing in the Consolidated column.
2. Go to the EDIT menu and select COPY.
3. Go to a blank area at the end of the current workbookor open a new workbook in
Excel.
4. Click on the area and go to EDIT menu, and select Paste. This will paste the data in an easily accessible area for analysis.
5. Repeat steps 1-4 for the Net Operating Earnings Line.
6. Highlight all the information that you want to appear in the graph. Note: to highlight more than one line of data you can hold the control key while highlighting the second line.
7. Click on the Chart Wizard in the toolbar; if there is not a toolbar option then go to INSERT, CHART. (This will in effect equal the Chart Wizard)
8. The first step in the Chart Wizard is to select Standard Type, Column. Choose the first chart sub-type for the column and then hit NEXT.
9. You will now be asked for your data range, which is already entered because you highlighted the information before we opened the Chart Wizard. Choose for your data range to be in rows. Then hit NEXT.
10. Now you need to select the title tab and enter a title for your chart and any titles for the axes. If you want labels to appear on the chart select the data labels tab and choose your labels and then hit NEXT.
11. Last you need to decide if you would like the chart on a new sheet or as an object on an existing sheet. Choose one option or the other and then click FINISH.
A sample of this chart is reproduced on the next page.
III. MARKETING EXPENSES AND GROSS MARGIN, ALL COMPANIES (MR 74)
Consolidated Financial Statements for All Companies are available every Quarter as MR74. In addition, most Facilitators will distribute these statements on a no-charge basis every fourth Q - a realistic emulation of Company Annual Reports in the world of practice. Clearly, these statements provide a wealth of data for both cross-sectional and dynamic comparisons. Below we provide a simple example of graphic cross-sectional comparison.
Marketing Expenses
We are assuming you are interested in the comparative distribution of marketing expenses (viewed as Commercial and Administrative, Advertising, Shipping and Inventory Carrying Cost) of all companies, and how marketing expenses relate to the Gross Margin at the consolidated level of the companies.
There are technical problems in making all of these comparisons in a simple Excel chart. For this reason, we will generate two charts, one the distribution of marketing expenses, and the other the gross margin of the companies.
The procedure is as follows:
1. The first thing that you must do is to find and select the materials for company marketing expenses. Once they are located highlight them (headings and all).
2. Go to EDIT, COPY
3. Click on the area and go to EDIT menu, and select Paste. This will paste the data in an easily accessible area for analysis.
4. Highlight the data that you wish to include in your chart.
5. Once highlighted go to the Chart Wizard in the toolbar (or go to INSERT, Chart Wizard).
6. The four- step Chart Wizard should then appear.
7. Choose standard types, column; then choose the fifth chart sub-type (Stacked column with a 3-D visual effect). Then click NEXT.
8. Choose that your data be in rows, then click NEXT.
9. Type what you want the title of the chart to be and any names for the axes. Then hit NEXT.
10. Choose either a new sheet or object in a page, depending on where you want the chart. Then click FINISH. The chart appears at the end of III.
Total Gross Margin All Companies
1. First highlight the cells that have the total gross margin information.
2. Then go to EDIT, COPY
3. Scroll right or down to find an empty area to place your new information.
4. Once you have found a vacant area go to EDIT, PASTE.
5. When the information has been pasted then you need to type in the column headings from left to right (Co1, Co2, Co3, Co4). Headings are entered by simply clicking on the cell where the heading is to appear and typing the heading in.
6. Highlight the Total Gross Margin for All Companies including the headings.
7. Once you have everything highlighted go to the Chart Wizard in the toolbar (or find it by going to INSERT, CHART).
8. The four- step Chart Wizard will appear.
9. Step one choose standard types, column and select the first chart sub-type. (Clustered column compares values across categories.) Then select NEXT.
10. Choose the data be in columns. Select NEXT.
11. Now you need to type a title in for your chart. Select NEXT.
12. Lastly you must select whether you want the chart to be placed on a new sheet or as an object on an existing sheet. Choose one or the other and then select FINISH. The chart appears on the following page.
The easiest way to condense the information of the two charts into one, and retain an element of comparability, is to make a chart containing only Total Marketing Cost related to Total Gross Margin. This would include writing the SUM function into various cells, to total the marketing cost. To add two values in Excel, type in the cell where you would like the result =CELL1+CELL2. For example to add a value in cell number A1 to a value in cell number A2, you would type in the output cell =A1+A2. Using the enter key will give you the result.
Note that Excel has a useful HELP function. HELP can be found as a pull-down menu or you can also type a question in the space up above the toolbar.
GENERAL NOTE: Our examples have been taken from the Market Research output. Clearly, however, the entire Company output of any given quarter contains a wealth of other analytical, and GRAPHICS opportunities.
A NOTE TO THE FACILITATOR: As Facilitator your “View Results Archive” will contain all individual company outputs from all Qs. In addition, you automatically get MR 79 and MR74 as part of the Facilitator Output. Also, remember the special output options available to you in the FacPac program module.