Menghitung NPV, IRR, xNPV dan xIRR dng Excel

Menghitung NPV, IRR, xNPV dan xIRR dng Ms Excel

1. NPV (Net Present Value)
   NPV adalah selisih antara present value dari investasi dengan nilai sekarang dari penerimaan-penerimaan kas bersih di masa yang akan datang. Untuk menghitung nilai sekarang perlu ditentukan tingkat bunga yang relevan.
2. IRR (Internal Rate of Return)
   Metode IRR ini digunakan untuk mencari tingkat bunga yang menyamakan nilai sekarang
   dari arus kas yang diharapkan di masa datang, atau penerimaan kas, dengan mengeluarkan investasi awal. Caranya, dengan menghitung nilai sekarang dari arus kas
   suatu investasi dengan menggunakan suku bunga yang wajar, misalnya 10 %. kemudian
   di bandingkan dengan biaya investasi, jika nilai investasi lebih kecil, maka di coba lagi
   dengan penghitungan suku bunga yang lebih tinggi demikian seterusnya sampai biaya
   investasi menjadi sama besar. Apabila dengan suku bunga wajar tadi nilai investasi lebih
   besar, maka harus di coba lagi dengan suku bunga yang lebih rendah sampai
   mendapatkan nilai investasi yang sama besar dengan nilai sekarang.

File Excel contoh perhitungan dapat di download disini

Some NPV resources

• Net present value - wikipedia
  
• Internal rate of return - wikipedia
  
• Calculating Net Present Value (pdf file).

Presenting Data with Persuasive Charts

isuals can make or break a presentation, proposal or other business document. To do the job, your visuals should support your message-whether it's in a document or in a presentation.  

Sometimes the support will be more conceptual-key strategic factors, change in trends, geographic differences. Other times, the visuals will be quantitative or data-driven such as market share, revenue growth, and regional numbers.  

Presenting data

Data driven charts allow the audience to perceive your message at a glance rather than puzzling over a list or table of data. Furthermore, it is easier (and more persuasive) for the audience to see trends and comparisons on a chart than to calculate them from the raw data. With your chart as support, you can then speak to your conclusions, insights and recommendations-your message.

Four types of chart can cover most data visualization tasks-the pie (and its cousin the relative value chart), the vertical bar chart, the horizontal bar chart and the line chart.

The right chart depends on the message

As with any presentation or document, you must first understand the message you wish to convey. After that, you can choose the right data set and choose the right chart. The last step is to make your chart both visually compelling and quickly digestible by your audience.

In general, one of biggest mistakes in creating graphics for a presentation or document is using the wrong visual for your message.  This is especially true when presenting data-the wrong chart only serves to confuse the audience or reader.

From message to data to chart

If your message is about share or distribution of a total, a pie chart or relative value chart is appropriate. If you are comparing shares from different categories, then a stacked bar chart works better than multiple pie charts.

If your message is about a comparison of values, the bar chart is the most appropriate chart. Again, if you have multiple categories, such as years or product lines, a grouped bar chart or a pairing of bar charts work best.

If you message is about a trend over time, the line chart not only shows the values but also gives a visual feel for the rates of change. To show values from discrete time frames, such as sales total per quarter, a vertical bar chart may work better than a line.

Charts created for frequency distributions and correlations will use line and vertical bar charts.

Visual impact

You've got your message, your data and the right chart type. But how do you make it persuasive and memorable? 

First, consider incorporating images into your charts. Image Charts are not only eye-catching and memorable, but also help your audience grasp quantities and relative values quickly.

When not using an Image Chart, you can still follow some guidelines to make sure the chart supports your message as strongly as possible. Use color (and lack of color) to compare, contrast and highlight the part of the picture that aligns with your message.  Don't clutter your chart with too many gridlines, data labels and axis labels. Only show what is needed to draw the viewer to your message.

Hopefully, this overview has you thinking about how to better present your data. For more detailed guidelines, download the white paper here.

 

Supply Chain Management

An Introduction to Supply Chain Management

Ram Ganeshan
Terry P. Harrison

Department of Management Science and Information Systems
303 Beam Business Building
Penn State University
University Park, PA 16802 U.S.A.

Email: Ganeshan (rxg112@silmaril.smeal.psu.edu), Harrison (hbx@psu.edu)

---

A supply chain is a network of facilities and distribution options that performs the functions of procurement of materials, transformation of these materials into intermediate and finished products, and the distribution of these finished products to customers. Supply chains exist in both service and manufacturing organizations, although the complexity of the chain may vary greatly from industry to industry and firm to firm.

Below is an example of a very simple supply chain for a single product, where raw material is procured from vendors, transformed into finished goods in a single step, and then transported to distribution centers, and ultimately, customers. Realistic supply chains have multiple end products with shared components, facilities and capacities. The flow of materials is not always along an arborescent network, various modes of transportation may be considered, and the bill of materials for the end items may be both deep and large.

Traditionally, marketing, distribution, planning, manufacturing, and the purchasing organizations along the supply chain operated independently. These organizations have their own objectives and these are often conflicting. Marketing's objective of high customer service and maximum sales dollars conflict with manufacturing and distribution goals. Many manufacturing operations are designed to maximize throughput and lower costs with little consideration for the impact on inventory levels and distribution capabilities. Purchasing contracts are often negotiated with very little information beyond historical buying patterns. The result of these factors is that there is not a single, integrated plan for the organization---there were as many plans as businesses. Clearly, there is a need for a mechanism through which these different functions can be integrated together. Supply chain management is a strategy through which such an integration can be achieved.

Supply chain management is typically viewed to lie between fully vertically integrated firms, where the entire material flow is owned by a single firm, and those where each channel member operates independently. Therefore coordination between the various players in the chain is key in its effective management. Cooper and Ellram [1993] compare supply chain management to a well-balanced and well-practiced relay team. Such a team is more competitive when each player knows how to be positioned for the hand-off. The relationships are the strongest between players who directly pass the baton, but the entire team needs to make a coordinated effort to win the race.

Supply Chain Decisions

We classify the decisions for supply chain management into two broad categories -- strategic and operational. As the term implies, strategic decisions are made typically over a longer time horizon. These are closely linked to the corporate strategy (they sometimes {\it are} the corporate strategy), and guide supply chain policies from a design perspective. On the other hand, operational decisions are short term, and focus on activities over a day-to-day basis. The effort in these type of decisions is to effectively and efficiently manage the product flow in the "strategically" planned supply chain.

There are four major decision areas in supply chain management: 1) location, 2) production, 3) inventory, and 4) transportation (distribution), and there are both strategic and operational elements in each of these decision areas.

Location Decisions

The geographic placement of production facilities, stocking points, and sourcing points is the natural first step in creating a supply chain. The location of facilities involves a commitment of resources to a long-term plan. Once the size, number, and location of these are determined, so are the possible paths by which the product flows through to the final customer. These decisions are of great significance to a firm since they represent the basic strategy for accessing customer markets, and will have a considerable impact on revenue, cost, and level of service. These decisions should be determined by an optimization routine that considers production costs, taxes, duties and duty drawback, tariffs, local content, distribution costs, production limitations, etc. (See Arntzen, Brown, Harrison and Trafton [1995] for a thorough discussion of these aspects.) Although location decisions are primarily strategic, they also have implications on an operational level.

Production Decisions

The strategic decisions include what products to produce, and which plants to produce them in, allocation of suppliers to plants, plants to DC's, and DC's to customer markets. As before, these decisions have a big impact on the revenues, costs and customer service levels of the firm. These decisions assume the existence of the facilities, but determine the exact path(s) through which a product flows to and from these facilities. Another critical issue is the capacity of the manufacturing facilities--and this largely depends the degree of vertical integration within the firm. Operational decisions focus on detailed production scheduling. These decisions include the construction of the master production schedules, scheduling production on machines, and equipment maintenance. Other considerations include workload balancing, and quality control measures at a production facility.

Inventory Decisions

These refer to means by which inventories are managed. Inventories exist at every stage of the supply chain as either raw materials, semi-finished or finished goods. They can also be in-process between locations. Their primary purpose to buffer against any uncertainty that might exist in the supply chain. Since holding of inventories can cost anywhere between 20 to 40 percent of their value, their efficient management is critical in supply chain operations. It is strategic in the sense that top management sets goals. However, most researchers have approached the management of inventory from an operational perspective. These include deployment strategies (push versus pull), control policies --- the determination of the optimal levels of order quantities and reorder points, and setting safety stock levels, at each stocking location. These levels are critical, since they are primary determinants of customer service levels.

Transportation Decisions

The mode choice aspect of these decisions are the more strategic ones. These are closely linked to the inventory decisions, since the best choice of mode is often found by trading-off the cost of using the particular mode of transport with the indirect cost of inventory associated with that mode. While air shipments may be fast, reliable, and warrant lesser safety stocks, they are expensive. Meanwhile shipping by sea or rail may be much cheaper, but they necessitate holding relatively large amounts of inventory to buffer against the inherent uncertainty associated with them. Therefore customer service levels, and geographic location play vital roles in such decisions. Since transportation is more than 30 percent of the logistics costs, operating efficiently makes good economic sense. Shipment sizes (consolidated bulk shipments versus Lot-for-Lot), routing and scheduling of equipment are key in effective management of the firm's transport strategy.

Supply Chain Modeling Approaches

Clearly, each of the above two levels of decisions require a different perspective. The strategic decisions are, for the most part, global or "all encompassing" in that they try to integrate various aspects of the supply chain. Consequently, the models that describe these decisions are huge, and require a considerable amount of data. Often due to the enormity of data requirements, and the broad scope of decisions, these models provide approximate solutions to the decisions they describe. The operational decisions, meanwhile, address the day to day operation of the supply chain. Therefore the models that describe them are often very specific in nature. Due to their narrow perspective, these models often consider great detail and provide very good, if not optimal, solutions to the operational decisions.

To facilitate a concise review of the literature, and at the same time attempting to accommodate the above polarity in modeling, we divide the modeling approaches into three areas --- Network Design, ``Rough Cut" methods, and simulation based methods. The network design methods, for the most part, provide normative models for the more strategic decisions. These models typically cover the four major decision areas described earlier, and focus more on the design aspect of the supply chain; the establishment of the network and the associated flows on them. "Rough cut" methods, on the other hand, give guiding policies for the operational decisions. These models typically assume a "single site" (i.e., ignore the network) and add supply chain characteristics to it, such as explicitly considering the site's relation to the others in the network. Simulation methods is a method by which a comprehensive supply chain model can be analyzed, considering both strategic and operational elements. However, as with all simulation models, one can only evaluate the effectiveness of a pre-specified policy rather than develop new ones. It is the traditional question of "What If?" versus "What's Best?".

Network Design Methods

As the very name suggests, these methods determine the location of production, stocking, and sourcing facilities, and paths the product(s) take through them. Such methods tend to be large scale, and used generally at the inception of the supply chain. The earliest work in this area, although the term "supply chain" was not in vogue, was by Geoffrion and Graves [1974]. They introduce a multicommodity logistics network design model for optimizing annualized finished product flows from plants to the DC's to the final customers. Geoffrion and Powers [1993] later give a review of the evolution of distribution strategies over the past twenty years, describing how the descendants of the above model can accommodate more echelons and cross commodity detail.

Breitman and Lucas [1987] attempt to provide a framework for a comprehensive model of a production-distribution system, "PLANETS", that is used to decide what products to produce, where and how to produce it, which markets to pursue and what resources to use. Parts of this ambitious project were successfully implemented at General Motors.

Cohen and Lee [1985] develop a conceptual framework for manufacturing strategy analysis, where they describe a series of stochastic sub- models, that considers annualized product flows from raw material vendors via intermediate plants and distribution echelons to the final customers. They use heuristic methods to link and optimize these sub- models. They later give an integrated and readable exposition of their models and methods in Cohen and Lee [1988].

Cohen and Lee [1989] present a normative model for resource deployment in a global manufacturing and distribution network. Global after-tax profit (profit-local taxes) is maximized through the design of facility network and control of material flows within the network. The cost structure consists of variable and fixed costs for material procurement, production, distribution and transportation. They validate the model by applying it to analyze the global manufacturing strategies of a personal computer manufacturer.

Finally, Arntzen, Brown, Harrison, and Trafton [1995] provide the most comprehensive deterministic model for supply chain management. The objective function minimizes a combination of cost and time elements. Examples of cost elements include purchasing, manufacturing, pipeline inventory, transportation costs between various sites, duties, and taxes. Time elements include manufacturing lead times and transit times. Unique to this model was the explicit consideration of duty and their recovery as the product flowed through different countries. Implementation of this model at the Digital Equipment Corporation has produced spectacular results --- savings in the order of $100 million dollars.

Clearly, these network-design based methods add value to the firm in that they lay down the manufacturing and distribution strategies far into the future. It is imperative that firms at one time or another make such integrated decisions, encompassing production, location, inventory, and transportation, and such models are therefore indispensable. Although the above review shows considerable potential for these models as strategic determinants in the future, they are not without their shortcomings. Their very nature forces these problems to be of a very large scale. They are often difficult to solve to optimality. Furthermore, most of the models in this category are largely deterministic and static in nature. Additionally, those that consider stochastic elements are very restrictive in nature. In sum, there does not seem to yet be a comprehensive model that is representative of the true nature of material flows in the supply chain.

Rough Cut Methods

These models form the bulk of the supply chain literature, and typically deal with the more operational or tactical decisions. Most of the integrative research (from a supply chain context) in the literature seem to take on an inventory management perspective. In fact, the term "Supply Chain" first appears in the literature as an inventory management approach. The thrust of the rough cut models is the development of inventory control policies, considering several levels or echelons together. These models have come to be known as "multi-level" or "multi-echelon" inventory control models. For a review the reader is directed to Vollman et al. [1992].

Multi-echelon inventory theory has been very successfully used in industry. Cohen et al. [1990] describe "OPTIMIZER", one of the most complex models to date --- to manage IBM's spare parts inventory. They develop efficient algorithms and sophisticated data structures to achieve large scale systems integration.

Although current research in multi-echelon based supply chain inventory problems shows considerable promise in reducing inventories with increased customer service, the studies have several notable limitations. First, these studies largely ignore the production side of the supply chain. Their starting point in most cases is a finished goods stockpile, and policies are given to manage these effectively. Since production is a natural part of the supply chain, there seems to be a need with models that include the production component in them. Second, even on the distribution side, almost all published research assumes an arborescence structure, i. e. each site receives re-supply from only one higher level site but can distribute to several lower levels. Third, researchers have largely focused on the inventory system only. In logistics-system theory, transportation and inventory are primary components of the order fulfillment process in terms of cost and service levels. Therefore, companies must consider important interrelationships among transportation, inventory and customer service in determining their policies. Fourth, most of the models under the "inventory theoretic" paradigm are very restrictive in nature, i.e., mostly they restrict themselves to certain well known forms of demand or lead time or both, often quite contrary to what is observed.

The preceding sections are a selective overview of the key concepts in the supply chain literature. Following is a list of recommended reading for a quick introduction to the area.

Bibliography

1. Arntzen, B. C., G. G. Brown, T. P. Harrison, and L. Trafton. Global Supply Chain Management at Digital Equipment Corporation. Interfaces, Jan.-Feb., 1995.
2. Ballou, R. H. 1992. Business Logistics Management, Prentice Hall, Englewood Cliffs, NJ, Third Edition.
3. Breitman, R. L., and J. M. Lucas. 1987. PLANETS: A Modeling System for Business Planning. Interfaces, 17, Jan.-Feb., 94-106.
4. Cohen, M. A. and H. L. Lee. 1985. Manufacturing Strategy Concepts and Methods, in Kleindorfer, P. R. Ed., The Management of Productivity and Technology in Manufacturing, 153- 188.
5. Cohen, M. A. and H. L. Lee. 1988. Strategic Analysis of Integrated Production-Distribution Systems: Models and Methods. Operations Research, 36, 2, 216-228.
6. Cohen, M. A. and H. L. Lee. 1989. Resource Deployment Analysis of Global Manufacturing and Distribution Networks. Journal of Manufacturing and Operations Management, 81-104.
7. Cooper, M. C., and L. M. Ellram. 1993. Characteristics of Supply Chain Management and the Implications for Purchasing and Logistics Strategy. The International Journal of Logistics Management, 4, 2, 13-24.
8. Deuermeyer, B. and L. B. Schwarz. 1981. A Model for the Analysis of System Service Level in Warehouse/ Retailer Distribution Systems: The Identical Retailer Case, in: L. B. Schwarz (ed.), Studies in Management Sciences, Vol. 16--Multi-Level Production / Inventory Control Systems, North-Holland, Amsterdam, 163-193.
9. Geoffrion, A., and G. Graves. 1974. Multicommodity Distribution System Design by Benders Decomposition. Management Science, 29, 5, 822-844.
10. Geoffrion, A., and R. Powers. 1993. 20 Years of strategic Distribution System Design: An Evolutionary Perspective, Interfaces. (forthcoming)
11. Houlihan, J. B. 1985. International Supply Chain Management. International Journal of Physical Distribution and Materials Management, 15, 1, 22-38.
12. Lee, H. L., and C. Billington. 1992. Supply Chain Management: Pitfalls and Opportunities. Sloan Management Review, 33, Spring, 65-73.
13. Lee, H. L., and C. Billington. 1993. Material Management in Decentralized Supply Chains. Operations Research, 41, 5, 835-847.
14. Masters, J. M. 1993. Determination of Near-Optimal Stock Levels for Multi-Echelon Distribution Inventories. Journal of Business Logistics, 14, 2, 165-195.
15. Schwarz, L. B. 1981. Introduction in: L. B. Schwarz (ed.), Studies in Management Sciences, Vol. 16--Multi-Level Production / Inventory Control Systems, North-Holland, Amsterdam, 163-193.
16. Stenross, F. M., and G. J. Sweet. 1991. Implementing an Integrated Supply Chain in Annual Conference Proceedings, Oak Brook, Ill: Council of Logistics Management, Vol. 2, 341-351.
17. Vollman, T. E., W. L. Berry, and D. C. Whybark. 1992. Manufacturing Planning and Control Systems, Irwin, Homewood, IL.

    ---

    Refers to: Production planning, inventory management, distribution and transportation, mathematical programming
    Referenced by:
    Contributors: Ram Ganeshan (rxg112@silmaril.smeal.psu.edu), Terry Harrison (hbx@psu.edu)
    Status: Work that is updated on a regular basis
    
    

    ---

    
    
    Last Update: 22 May 1995
    Terry P. Harrison, hbx@psu.edu

Kamus 2.03, Free English-Indonesian and vice versa dictionary ...

Kamus 2.03 merupakan program terjemah Inggris-Indonesia dan sebaliknya. Pada versi ini ada beberapa perbaikan dan tambahan dari versi sebelumnya.

Update Kamus v2.03 :
[*] Perbaikan pencarian jika ada penambahan spasi.
[+] Penambahan informasi (hint), termasuk hasil frase ( sehingga kalimat/kata yang panjang bisa terlihat )
[+] Penambahan opsi apakah otomatis menghapus selain huruf abjad.
[*] Installer tidak akan menghapus database tambahan.


Jika sudah menggunakan program Kamus versi 2.0 sampai 2.02, mungkin anda hanya ingin download updatenya saja ( Kamus2.exe dan help file ), yang relatif kecil ukurannya. Silahkan klik Update Kamus to 2.03

Untuk Download lengkap dengan installernya klik Kamus 2.03 (2.76 MB)

Perhitungan dengan Ms Excel 2007 bisa salah !

Saya membaca artikel di IlmuKomputer.com dengan judul Hati-hati Aplikasi Ms. Excel 2007 Bisa Salah Melakukan Perhitungan Perkalian dan Pembagian yang isinya kira-kira begini: Tapi hati?hati menggunakan aplikasi perkantoran yang satu ini, ada beberapa angka yang salah jika dikalikan atau dibagi, hasilnya sangat berbeda jika dihitung menggunakan calculator, ataupun openoffice.org calc, wah … kog bisa ?, oke kita buktikan sekarang : 1. Buka aplikasi Ms. Excel 2007 2. Tulis angka berikut ini : 154.2 3. Tulis angka selanjutnya yaitu : 1700 4. Kalikan angka 154.2 dengan 1700 maka didapat hasil 262140 Gambar diatas menggunakan rumus =A1*B1 5. Kemudian hasilnya yaitu 262140 bagi dengan angka 4 maka didapat hasil 100000 Penasaran saya cek dengan Ms Office 2007 ini hasilnya

		D		E	F	G
5					D6*E6	F6/4
6		154.20		1,700.00	262,140.00	100,000.00
7
8					262,140.00	65,535.00

Jadi kadang-kadang bisa benar tapi fatalnya kesalahan pembagian satu angka ini bisa berdampak serius jika dipergunakan untuk perhitungan yang menyangkut masalah uang atau masalah sensitif lainnya ... Saran Cek lagi hasil perhitungan spreadsheet anda !!!! I read the article in IlmuKomputer.com with the title Hati-hati Aplikasi Ms. Excel 2007 Bisa Salah Melakukan Perhitungan Perkalian dan Pembagian (Be Careful of the Application Ms. Excel 2007 could be wrong ) that the contents roughly: be careful using this office application, there is something wrong when you make multiplication and/or division of some figures. Try this:

1. Input figure : 154.2
   

1. Multiply by : 1700
   
2. The result will display : 262,140.00
   
3. Then divide the result with 4 and it will show 100,000.00
   
4. Try with direct entry figure : 262,140.00 the divide with 4 and the result will show 65,535.00
   

So it could sometimes be true but fatal him the distribution mistake of one figure could have a serious impact if being utilised for the calculation that was related to the problem of money or the other sensitive problem

Click Here for your Free Traffic!

PowerPoint Tutorial

Here is an excellent 69-page PowerPoint Tutorial, named Using Microsoft Powerpoint 2003. Using step-by-step instructions and screen shots, it teaches you how to use Powerpoint 2003 to its fullest, using much of its functionality. It shows you how to perform the following important tasks: formatting bullets, creating master views, working with clip art and images, creating charts and graphs, working with the drawing tools, using the color palettes, and making transitions and animation effects. This PowerPoint training guide and tutorial is 1.75mb in size and is in pdf format. It is located in the University's ITS Guides page.

Contents of Using Microsoft Powerpoint 2003 Tutorial:

- Introduction
- Getting started with PowerPoint
- The New Presentation Task Pane
- AutoContent Wizard
- Navigation
- The Views
- Design Templates
- Creating a new Slide
- Adding additional Slides
- Text formatting: Bullets
- Master Views
- Clip Art
- Inserting, scaling, moving and deleting objects
- Images
- Organization Charts
- Diagrams
- Charts and Graphs
- Comments
- Working with Drawing Tools
- Layout
- Using the Color Palettes
- Multi-media elements
- Slide Shows
- Transitions, simple Text Animation Effects and Timings
- Applying Animation Effects to text and objects
- Customizing Slide Shows
- Page / Slide Format
- Printing
- Saving Presentations
- Creating a Slide Show File
- Getting Help for PowerPoint

Printer friendly

Excel 2003 Tutorials from Kansas University

Here are 4 illustrated Excel 2003 Tutorials from Kansas University that demonstrate how use use many important features of Excel. These tutorials take you step-by-step through each task being taught using screen shots and easy-to-follow instructions.

Excel Introduction Tutorial

This introductory Excel tutorial will get you going with using Excel to perform fundamental tasks, such as creating, saving, and opening new Excel workbooks and worksheets; selecting, copying, and moving data; constructing formulas; formatting worksheets; and setting up worksheets for printing. (22 pages, 424kb, pdf format)

After using this Excel tutorial, you will be able to:
- Create, open, and save Excel workbooks
- Select, copy, and move data
- Create formulas using relative and absolute references
- Format worksheets
- Use Page Setup to set up worksheets for printing

Contents of Excel Introduction Tutorial:

Introduction
Objectives
Definitions
Creating and Opening Excel Workbooks
Inside an Excel Worksheet
Creating Formulas
Editing & Deleting Formulas
Copying Formulas and Values
Changing the Workbook or Worksheet Appearance
Getting Additional Help


Excel Data Management Tutorial
This Excel 2003 tutorial describes you how to use Excel to work with lists, data forms, filtering, and subtotals features, along with managing windows and link to data and formulas. (18 pages, 267kb, pdf format)

After using this Excel tutorial, you should be able to:
- Sort a data list
- Filter a data list according to criteria
- Extract data list information
- Use the subtotal function
- Adjust the spreadsheet windows

Contents of Excel Data Management Tutorial:
Introduction
Objectives
Using Excel lists
Creating a List
Sorting
To perform a Simple Sort
To Sort by Multiple Columns
Data Forms
Adding Data Using the Data Form
Finding Records Using Criteria
Filtering Data
AutoFilter
Advanced Filters
Special Features for Filtered Lists
Totals and Subtotals
Total Row
Subtotals
Managing Windows
Multiple Windows
Splitting Windows
Freezing Panes
Linking Data
Getting Additional Help


Excel Tutorial on Functions and Data Analysis Tools
This Excel 2003 tutorial will show you how to use Excel's functions and data analysis tools. Topics explained include functions such as Average and Sum, and data analysis tools such as pivot tables and conditional summations. (19 pages, 371kb, pdf format)

After reading and applying this Excel tutorial, you will be able to:
- Define terms related to using functions
- Create and edit a function
- Identify add-in functions
- Perform data analysis using Data Analysis Tools
- Create a formula with the Conditional Sum wizard
- Learn to troubleshoot a formula

Contents of Excel Tutorial on Functions and Data Analysis Tools:
Introduction
Objectives
Definitions
Inserting Functions
Sample Functions
Add-In Commands and Functions
Loading an Add-In
Unloading an Add-In
Data Analysis Tools
Histogram
Conditional Sum
Analyzing Data with the PivotTable
Array Formulas
Getting Additional Help


Excel Charting Tutorial
Learn how to use Excel to create and edit charts, modify chart options, and format chart objects, as well as use trendlines, forecasts, and error bars to present data graphically. (17 pages, 361kb, pdf format)

You will be able to do the following after going through this Excel tutorial:
- Create and edit charts
- Modify chart options
- Format chart objects
- Apply trendlines and error bars

Contents of Excel Charting Tutorial:
Introduction
Objectives
Definitions
Creating a Chart
Using the Chart Wizard
Modifying Charts
Formatting Chart Objects
Trendlines
Pie Charts
Creating a Pie Chart
Printing Charts
Printing an Embedded Chart on a Separate Page
Getting Additional Help

Microsoft Excel Ti[ps and Tricks

Microsoft Excel Tip Categories

Please select a category from the list below:

	Excel General
	Excel Text, Editing and Format
	Excel Date and Time
	Excel Formula and Function
	Excel Data
	Excel Macros and VBA

---

Excel General

• Excel Keyboard Shortcuts [50 tips]
  
• Excel General [15 tips]
  
• Excel Customizing [19 tips]
  
• Excel Tools [3 tips]
  
• Excel Worksheet, Workbook [42 tips]
  
• Excel Range Name [7 tips]
  
• Excel Information [7 tips]
  
• Excel Security - Protection [19 tips]
  
• Excel Printing [55 tips]
  
• Excel Chart [3 tips]
  
• Excel 2003 [3 tips]
  

Excel Text, Editing and Format

• Excel Text [13 tips]
  
• Excel Editing [71 tips]
  
• Excel Format [55 tips]
  
• Excel Style [6 tips]
  

Excel Date and Time

• Excel Dates [8 tips]
  
• Excel Time [10 tips]
  

Excel Formula and Function

• Keyboard Formula Shortcuts [5 tips]
  
• Working with Formulas [33 tips]
  
• Links between Worksheet and Workbooks [7 tips]
  
• Summing [31 tips]
  
• Counting [20 tips]
  
• Excel Array Formulas [16 tips]
  
• Custom Functions [25 tips]
  
• Excel Financial Formulas [7 tips]
  
• Excel Date & Time Formulas [76 tips]
  
• Lookup Formulas [57 tips]
  
• Database Formulas [1 tips]
  
• Excel Text Formulas [50 tips]
  
• Logical Formulas [6 tips]
  
• Information Formulas [3 tips]
  
• Other Q&A Formulas [15 tips]
  

Excel Data

• Excel Data [6 tips]
  
• Excel Importing Text Files [8 tips]
  
• Excel Sorting [11 tips]
  
• Excel Filter [17 tips]
  
• Excel Subtotals [26 tips]
  
• Excel Grouping and Outlining [5 tips]
  
• Excel Consolidating [18 tips]
  
• Excel Pivot Tables [62 tips]
  
• Table - Excel 2007 [12 tips]
  

Excel Macros and VBA

• Keyboard & Other Shortcuts in VBA [3 tips]
  
• General Topics in VBA [40 tips]
  
• Files, Workbook, and Worksheets in VBA [25 tips]
  
• Cells, Ranges, Rows, and Columns in VBA [45 tips]
  
• Menus, Toolbars, Status bar in VBA [11 tips]
  
• User Forms, Input boxes in VBA [7 tips]
  
• Events in VBA [5 tips]
  
• Formating in VBA [6 tips]
  
• Date & Time in VBA [11 tips]
  
• Printing in VBA [29 tips]
  
• Mail - Send and Receive in VBA [6 tips]
  
• Custom Functions in VBA [8 tips]
  
• Import and Export in VBA [11 tips]
  
• Database in VBA [3 tips]
  
• Protecting in VBA [2 tips]
  
• Links in VBA [3 tips]
  
• Applications - Word, Outlook in VBA [6 tips]
  
• Add-In in VBA [9 tips]
  
• Modules, Class Modules in VBA [10 tips]
  
• Using Loops [1 tips]

Ms Excel- Save & Open Files

Saving and Opening Workbook Files

A workbook file is saved with the File|Save command or with the file save tool (). If you are working with a new file, Windows will open the normal Save dialog box enabling you to specify a folder and a name for the file. Windows will add the Excel extension XLS to the filename. If the file had been previously named, a save command will save the file with no dialog box.

 

You may experience problems saving an Excel file on a floppy disk. This can be avoided by working with a file saved on the hard drive. You can then use Windows Explorer or MyComputer to transfer the completed file to a floppy disc.

 

A file may be opened with the File|Open command or with the file open tool(). Windows opens a dialog box to enable you to specify the folder from which the file is to be opened.

 

Menyimpan dan Membuka File Workbook

File Workbook disimpan dengan menggunakan perintah File|Save atau ( )/tool simpan. Jika kita sedang mengerjakan file baru, maka Windos akan membuka kotak Save dialog yang membuat kita dapat menentukan folder atau directory dan nama file yang akan kita simpan. Windows akan menambahkan ekstensi nama file XLS pada nama file. Jika kita telah pernah menyimpan, maka perintah simpan akan menyimpan file tanpa menampilkan kota dialog.

Jangan menggunakan floppy disk atau USB sebagai media bekerja karena sering rusak. Cara menghindarinya adalah menyimpan pekerjaan kita ke hard drive dulu. Lalu gunakan Windows Explorer atau MyComputer untuk mentransfer file yang telah dikerjakan ke floppy disc atau USB.

 

Sebuah file dapat dibuka dengan menggunakan perintah File|Open atau tool open file (). Windows opens a dialog box to enable you to specify the folder from which the file is to be opened.

 

Ms Excel - Formatting Entries

Formatting

Formatting is anything that changes the appearance of an entry: alignment, number of decimals used to display a number, font typeface, font style (bold, italic, or underlined), font size, font colour, borders around the cell, a coloured background for the cell, etc. In addition, we may change the width of a column and the height of a row, and text may be centred over a number of cells. You may format cells one-by-one or, by first selecting a number of cells, all the cells in a range may be formatted at one time.

Clearly the Formatting toolbar provides many tools to change the appearance of a cell. If you let the pointer linger over a tool icon, a tooltip will appear telling you the purpose of the tool. You should use this method to identify the tools to change the font typeface, size and style (bold, italic and underlined). In the same way, find the three tools to change alignment.

Each of the tools which add borders, change the font colour and add a coloured background (pattern), has a small V beside it to indicate that the tool leads to a dialog box. Note that the border tool provides only black borders, below we see how to get coloured ones. Recall that the tools are a subset of the menu commands.

Other tools on the Formatting toolbar may be used to change how numeric values are displayed. Locate the Increase decimals and the Decrease decimals tool, and the tools to change the display to currency, as a value with thousand separators, or as a percentage.

6 Getting Started with Excel

The menu command Format|Cells opens up the dialog box shown in Figure 6. From the Number tab one can set the format for numbers. The default setting is called General. You may wish to experiment with the Fixed and the Currency setting. If you click on the Fraction item you will see that it is possible to have fractional values displayed as quarters, eighths, etc.

Figure 6

The command Format|Column may be used to alter the width of a column. The default width is 8.38 units. A group of columns may be altered by selecting them in the column heading row before opening the Format|Column dialog. One of the most useful items on this dialog is the AutoFit Selection which makes the columns just the right width to hold their contents. If a column is given a width that is too small to hold a numeric value, the value will be displayed as ########. The solution is to (a) widen the cell and/or (b) display the value with fewer decimals if possible.

To experiment with formatting, make the worksheet shown in Figure 7. The vertical text was typed in normally and the cells were formatted with Format|Cells and opening the Alignment tab. The columns with this text were formatted for AutoFit. Using Tools|Options, and opening the View tab, the gridlines were removed from this worksheet.

Training completed
	Cash register	Customer inquiry	Inventory check
Damian	x	x
Jo		x	x
Justin		x
Nichola	x	x
Simon		x	x
Tim	x	x	x

Ms Excel - Cell References and Entries

Cell References

To refer to a specific cell we use a cell reference. This is a combination of the column heading and the row number. The cell at the top left, which is at the intersection of column A and row 1, has a cell address of A1. The cell below is A2 while the cell to the right is B1. This method of naming cells using the column letter is called the A1 method. To reference a cell on another sheet of the same workbook, we use the form Sheet2!B4 – note the exclamation mark. To reference a cell in another workbook we can use the form 'C:\My Documents\[Book2.xls]Sheet1'!$A$1. We discuss the dollar signs in a cell reference later in this chapter.

Cell Entries

A cell may contain data or a formula. A data entry may be: a number, text (sometimes called labels) or a date. Formulas begin with an equal sign (=). We discuss formulas in detail in the next unit.

You may wish to experiment by making the worksheet shown in Figure 4. Type the entries in cell A1, C1, G1 and in row 2. For example, with A1 as the active cell, type the word Text and press the R key to complete the entry. We will discuss other ways of completing a entry later. Click on C1 and enter the next piece of data.

A

B

C

D

E

F

G

1

Text

This is an example of a long text entry

123456789

2

123

1.5

1.55

1 1/2

1.23457E+14

Figure 4

A number of observations may be made about how entries are initially treated. Later we

will discuss how formatting may be used to change the appearance.

1) Text is left aligned by default. By this we mean that on a newly opened worksheet any text typed into a cell will be placed to the left within the cell. The alignment may be changed by formatting the cell.

2) By default, numbers are left aligned.

3) Text can overflow into empty adjacent cells as shown by the entry in C2. If you type anything in D2, only part of C2's entry will be visible in the cell but all of it will be visible in the Formula bar.

4) When a large number (not too large) is entered, the column automatically widens to accommodate all the digits.

5) Very large numbers (the value typed into G2 was 123456789123456) are converted to exponential (sometimes called scientific) notation. The displayed value 1.23457E+14 means the same as 1.234567 × 10¹⁴. In the next unit we discuss Excel’s precision.

6) A whole number can have no more with than 15 digits. You are unlikely to be working with numbers that large. Objects we often call numbers are really just a string of digits. For example, phone numbers and account numbers are not real numbers in that we never perform arithmetic operations on them. In these cases it is better to enter the value by first typing a single quote (it is found on the key next to R). This quote mark will not be displayed in the cell but will cause the entry to be treated as text.

7) By default, Excel does not display what it considers to be insignificant digits. Thus if you type 1.50 in B2, the value 1.5 will be displayed in the cell and in the formula bar. Again, formatting can be used to change this.

8) Fractions may be entered as in D2. Note there is a space between the 1 and the 1/2. If you wish to enter a fraction (for example, 1/2) without a whole number, you must enter it with a preceding 0 followed by a space (e.g. 0 1/2). The zero will not be displayed when the entry is completed. In all cases, the values displayed in the Formula bar will be in decimal form (e.g. 1.5, or 0.5).

9) To evaluate a number in fractional notations, enter it as a formula. Thus to display the result of 1½ + 3¾ enter = 1+1/2+3+3/4. See the next unit for more on formulas.

10) Improper fractions are converted on entry. Thus 1 4/8 will be converted to 1 1/2. In certain cases (when the denominator is 2, 4, 8, 16, 10 or 100) we may format the cell to overcome this.

11) If you enter something like 4/12 without a leading zero, Excel will take this to mean a date – either 4 December or 12 April depending on your Regional Setting. We will not be discussing dates in this supplement.

We indicated above that you need to let Excel know when you have finished entering something in a cell and recommended the use of the R key. This is not the only way. Any of the navigation keys (B, L , R , T) and T may be used. Alternatively, you may click the Tin the Formula bar – this is displayed only while a cell entry is being made or edited. Do not get into the bad habit of clicking the mouse on another cell to complete an entry. While this will work when you are entering data it will cause you grief when working with formulas.

Ms Excel - Toolbars

Toolbars

As with all Windows applications, the toolbars provide quick access to the more frequently used menu commands. That is to say, the commands available from toolbars are a subset of the menu commands. In Excel2000 the Standard and the Formatting toolbars are docked together on one row by default. This means that only part of each toolbar is visible. To see the hidden part, click on the double arrow. If you prefer to have the toolbars on separate rows, open the Customize dialog box as explained above and clear the check mark from the box labelled Standard and Formatting toolbars share one row.

The amount of space allocated to each toolbar when they share a row may be changed. With the mouse, click on the vertical bar to the right of the first double arrow and drag it to one side.

Microsoft Excel Menu Commands

Menu commands

The menus in Excel 2000 are similar to menus in other Windows applications. However, as with all the Microsoft Office 2000 products there is a small difference from earlier versions: when you open a menu item an abbreviated version is displayed (Figure 2.)

Figure 2

Getting Started with Excel 3

Figure 3

The menu shows the commonly used and the most recently used commands. The entire menu is displayed if you click on the down arrow at the bottom of the menu. If your version of Excel is so configured, the full menu will also appear after a short delay. To configure Excel, either use the command Tools|Customize or right click on the menu bar and select the Customize item. In either case, the Customize dialog box is opened. If you move to the Option tag, the dialog box resembles Figure 3. To have all the commands displayed, clear the check mark from the Menus show recently used commands first box. Beaware that this action will affect other Microsoft Office 2000 application on your PC.

Menu commands may be accessed by clicking on the required item. Alternatively, you may hold down the A key and press the key corresponding to the underscored letter in the menu item. Thus the File menu is opened with A+F. A third method is available for other commands. If you open the File menu you will see to the right of the Save item the shortcut CTRL+S. This means that the key commination C+S will save the current file – there is no need to open the File menu for this to work. The shortcuts for copy and paste (C+C and C+V) are very useful to know.

Quantitative Approaches in Business Studies

Quantitative Approaches in Business Studies Student resources

Click here for a multitude of helpful resources, including multiple choice, true/false and practice questions to test and further increase your knowledge. You will also find weblinks, Excel datasets, Excel worksheets and an Excel supplement.

• Chapter 1: Tools of the trade: basic . . .
• Chapter 2: Spreadsheets and other . . .
• Chapter 3: Obtaining the figures: data . . .
• Chapter 4: Making sense of the figures:
• Chapter 5: Presenting the figures: . . .
• Chapter 6: Summarising the figures: . . .
• Chapter 7: Measuring changes: index . . .
• Chapter 8: A firm foundation: . . .
• Chapter 9: Patterns of probability: . . .
• Chapter 10: Estimating from samples: . . .
• Chapter 11: Checking a theory: . . .
• Chapter 12: Making it better: . . .
• Chapter 13: Looking for connections: . . .
• Chapter 14: Spotting the relationship: . . .
• Chapter 15: Multiple regression
• Chapter 16: Planning an inventory . . .
• Chapter 17: Forecasting: time-series, . . .
• Chapter 18: Allowing for interest: . . .
• Chapter 19: Planning production levels:
• Chapter 20: Planning a project: network
• Chapter 21: Cutting down on queues: . . .
• Chapter 22: Quantitative methods in the
• Weblinks
• Excel supplement
• Excel datasets
• Excel worksheets

Copyright © 1995-2008, Pearson Education, Inc. Legal and Privacy Terms