1 / 17

CBIS102-2012

Functions. CBIS102-2012. Contents:. Test: amortization table (p21-22) What-if analysis Goal seek Interactive scroll bar Vlookup function (13-17) Pivot tables(p19-20) Text functions (p24-25) Filters Histograms. Test Revision. IF function Conditional formatting Anything else?.

ronan-tyson
Download Presentation

CBIS102-2012

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. Functions CBIS102-2012

  2. Contents: • Test: amortization table (p21-22) • What-if analysis • Goal seek • Interactive scroll bar • Vlookup function (13-17) • Pivot tables(p19-20) • Text functions (p24-25) • Filters • Histograms

  3. Test Revision • IF function • Conditional formatting • Anything else?

  4. Amortization Table • Test: • Open sheet 2 and prepare an amortization schedule for a debt of R 1 000 000 bearing interest at 12% pa, compounded monthly, if the debt is amortized by a series of monthly payments for 10 years, starting on the 1st of August 2017. See the next page for the layout and formulae: • Interest • Period • Amount • Payment

  5. Amortization: • What is the total amount of interest paid over this period? • What-if analysis: • What is the amount outstanding on the 1st April 2023, IF the loan amount increases to R 2000 000. • Revert to the original values, and use Goal seek to determine the new bond value, for a monthly payment of R 16 000? • Create an interactive toolbar to vary the number of years

  6. VLOOKUP/LOOKUP (page 13) • Download the excel file ‘parts-VL’ • Download the excel file ‘Mark-sheet’ • Average • Roundup • Named range, instead of I1:J5 • VLOOKUP • Draw a 3d-Column clustered chart showing the test scores for each student • Draw a spark-line bar chart showing the progress of each student

  7. Pivot Table (page 10-20) • “Slice and Dice” • What columns do you need as categories? • What data do you need? • Download ‘Insurance Pivot.xlsx’ • Determine the TOTAL insured value per region • Determine the TOTAL insured value per region according to the States

  8. Filters

  9. Histogram • File > Options > Add-Ins > Go • Select Toolpak

  10. Descriptive Statistics • Data Analysis > Descriptive Statistics

More Related