1 / 75

Excel 2002 Lab 7

Excel 2002 Lab 7. Using Financial Analysis, Lookup Functions, and Visual Basic for Applications (VBA). Objectives. Draw a technical diagram. Create an organization chart. Create a Web query. Calculate SLN and DDB depreciation. Objectives. Use a profitability index.

nayda-mckinney
Download Presentation

Excel 2002 Lab 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel 2002Lab 7 Using Financial Analysis, Lookup Functions, and Visual Basic for Applications (VBA)

  2. Objectives • Draw a technical diagram. • Create an organization chart. • Create a Web query. • Calculate SLN and DDB depreciation.

  3. Objectives • Use a profitability index. • Use lookup and reference functions. • Create Visual Basic scripts. • Move data from a form to another worksheet.

  4. Concepts Overview • Organization Chart An organization chart is a type of diagram used to show the personnel structure in a company or organization. • Web Query A Web query performs an Internet search, then places the results of the search, such as sales, data, stock prices, or data from companies, into an Excel worksheet.

  5. Concepts Overview • Depreciation Function Depreciation functions calculate the amount of time it will take for a fixed asset to be "used up." • Lookup Function Lookup functions permit you to use worksheet tables as sources of information for formulas and calculations in other parts of the worksheet.

  6. Concepts Overview • Reference Function Reference functions can search an entire table for a value or cell position that most closely matches the criteria set in the arguments, and return that value to a formula in another part of the worksheet.

  7. Concepts Overview • Visual Basic Control Statement Visual Basic control statements used in worksheets provide for repeating (looping), decision making, branching, or exiting or pausing a program.

  8. Outline • Preparing a Visual Plan • Creating a Technical Diagram • Creating an Organization Chart • Analyzing Financial Proposals • Creating a Web Query

  9. Outline cont. • Calculating Depreciation • Using SLN to Calculate Depreciation • Reusing Depreciation Scenarios as a Template • Calculating Profitability • Using the Profitability Index

  10. Outline cont. • Using LOOKUP and Reference Functions • Creating a VLOOKUP Table • Using MATCH and INDEX Reference Functions • Using the IF Function to Enhance the Form • Testing the Functions in the Form

  11. Outline cont. • Using Visual Basic Scripts for Applications • Designing a Form to Collect Information • Creating a Command Button to Copy Data Between Worksheets • Adding Code to the Command Button • Creating Visual Basic Script to Calculate a Tally • Creating Visual Basic Script to Print a Form • Creating Visual Basic Script to Clear Data • Protecting the Form and Worksheet

  12. Outline cont. • Concept Summary • Lab Review • Lab Exercises

  13. Preparing a Visual Plan • First, sketch a diagram on paper • Next, use Excel to create the diagram

  14. Creating a Technical Diagram

  15. Making Changes • New items appear on a new layer • Change order to position item on the correct layer

  16. Evan - CEO Evan – Café You – Manager Your Assistant – Anna Café Staff Evelyn – Training Consultants Creating an Organization Chart

  17. Concept 1Organization Chart • A type of diagram • Used to show personnel structure • Also used to show the workflow of a project

  18. Organization ChartDrawing Sections • Inserting hierarchical shapes • Subordinate, coworker, assistant • Choosing a layout • standard, both hanging, left hanging, right hanging • Selecting a level • Level, assistants, connecting lines

  19. Insert Shape Drop-Down Menu Subordinate Coworker Assistant Layout Drop-Down Menu Standard Both Hanging Left Hanging Right Hanging Organization Chart Toolbar Options

  20. Layout Drop-Down Menu (cont.) Fit Org. Chart to Contents Expand Org. Chart Scale Org. Chart Automatic Layout Select Drop-Down Menu Level Branch All Assistants All Connecting Lines Organization Chart Toolbar- More Options • Other • AutoFormat • Text Wrapping

  21. Top hierarchy Second-level hierarchy Organization Chart

  22. Café staff row You and your assistant Adding Subordinates

  23. Modifying the Organization Chart • As you add more layers, labels become smaller • Scale the chart • Increase text size • Add 3-D for emphasis

  24. Analyzing Financial Proposals

  25. Concept 2Web Query • Performs an Internet search • Places result in an Excel worksheet • Can use Excel functions and tools to work with the data • Imported data can be set to retain HTML formatting or RTF

  26. Web Query • Can look at a complex web site and select a table • Click on arrow in a yellow box • When you click on the arrow ,a black checkmark appears to indicate the data to import • Can click again to deselect any item

  27. Imported Results

  28. Calculating Depreciation

  29. Concept 3Depreciation Function • Calculates the time it takes for a fixed asset to be "used up" • Consider three factors when calculating: • Initial cost of asset • Length of time of useful life • Salvage value

  30. Depreciation Arguments in Excel

  31. Excel Depreciation Functions

  32. Vendor Comparison ChartDepreciation

  33. =Proposal2!$F$6 (This figures is copied from the Proposal2 worksheet =SLN($B$14,$C$14,D14) Using SLN to Calculate Depreciation

  34. =DDB($B19,$C$19,$D$19,1) Adding DDB Calculations

  35. Reusing Depreciation Scenarios as a Template • If information is standard, you can create a template • Can perform what-if analysis for other capital proposals & scenarios

  36. Calculating Profitability • Profitability Index • Allows you to see the relationships between items in your financial papers • Example: Analysis the length of time it would take to pay back an initial loan of $50k for the computers and corporate room • At what point will the cost of investing be balanced by the revenues? • PI=PVCF/Initial Investment

  37. Using the Profitability Index Purchase vs. Lease The choice is to purchase because the PI is 20 times over an acceptable ratio.

  38. Using LOOKUP and Reference Functions

  39. Concept 4Lookup Function • Uses worksheet tables as sources of information for formulas and calculations • VLOOKUP • HLOOKUP • Searches for a value and then returns a value in the cell you designate

  40. LOOKUP Functions Syntax • VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) • HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) What is the difference between VLOOKUP and HLOOKUP?

  41. LOOKUP Arguments

  42. MATCH and INDEX Functions • Closely related to the LOOKUP functions • MATCH & INDEX offer the flexibility of… • Starting anywhere in a table • Looking for a value anywhere that it is specified in the table • Both are reference functions

  43. Concept 5Reference Function • Searches an entire table for a value or cell that closely matches the criteria set in the arguments • Returns that value to a formula in another part of the worksheet • Can search anywhere in a data table

  44. Reference Function Examples =INDEX (array, row_num, column_num) =INDEX (reference, row_num, column_num, area_num) =MATCH (lookup_value, lookup_array, match_type)

  45. Using MATCH & INDEX When the name of the city is entered, both the state and the zip code are returned to the form.

  46. Using the IF Function to Enhance the Form • Using the IF function can serve to eliminate the #N/A cell entry • Create an IF statement to leave cells clear until they contain information in them

  47. Using Visual Basic Scripts for Applications • Visual Basic for Applications (VBA) can be used in Excel by: • Recording statements in a macro • or • Creating your own scripts

  48. Concept 6Visual Basic Control Statement • Used for: • Repeating • Decision making • Branching • Exiting or pausing a program • Can attach snippets of code called scripts to a button or menu item

  49. Visual Basic Control Statements • Sub procedures • Perform tasks that do not need to return a result • Example – printing a worksheet, formatting a cell • Function procedures • Perform tasks that return a result

  50. Procedure Syntax

More Related