MTH302 Business Mathematics & Statistics Handouts May 2022 PDF

Business Mathematics & Statistics MTH 302 Virtual University of Pakistan Knowledge beyond the boundaries Business Mathematics & Statistics (MTH 302) VU...

Business Mathematics & Statistics MTH 302 Virtual University of Pakistan Knowledge beyond the boundaries Business Mathematics & Statistics (MTH 302) VU TABLE OF CONTENTS : Lesson 1 :COURSE OVERVIEW........................................................................................................3 Lesson 2 :APPLICATION OF BASIC MATHEMATICS.....................................................................12 Lesson 3 :APPLICATION OF BASIC MATHEMATICS.....................................................................22 Lesson 4 :APPLICATION OF BASIC MATHEMATICS.....................................................................29 Lesson 5 :APPLICATION OF BASIC MATHEMATICS.....................................................................39 Lesson 6 :APPLICATION OF BASIC MATHEMATICS.....................................................................47 Lesson 7 :APPLICATION OF BASIC MATHEMATICS.....................................................................57 Lesson 8 :COMPOUND INTEREST..................................................................................................66 Lesson 9 :COMPOUND INTEREST..................................................................................................72 Lesson 10:MATRICES......................................................................................................................75 Lesson 11: MATRICES.....................................................................................................................80 Lesson 12 :RATIO AND PROPORTION...........................................................................................90 Lesson 13 :MATHEMATICS OF MERCHANDISING........................................................................95 Lesson 14 :MATHEMATICS OF MERCHANDISING......................................................................101 Lesson 15 :MATHEMATICS OF MERCHANDISING......................................................................107 Lesson 16 :MATHEMATICS OF MERCHANDISING......................................................................115 Lesson 17 :MATHEMATICS FINANCIAL MATHEMATICS............................................................119 Lesson 18 :MATHEMATICS FINANCIAL MATHEMATICS............................................................124 Lesson 19 :PERFORM BREAK-EVEN ANALYSIS.........................................................................128 Lesson 20 :PERFORM BREAK-EVEN ANALYSIS.........................................................................136 Lesson 21 :PERFORM LINEAR COST-VOLUME PROFIT AND BREAK-EVEN ANALYSIS........140 Lesson 22 :PERFORM LINEAR COST-VOLUME PROFIT AND BREAK-EVEN ANALYSIS........143 Lesson 23 :STATISTICAL DATA REPRESENTATION..................................................................150 Lesson 24 :STATISTICAL REPRESENTATION.............................................................................155 Lesson 25 :STATISTICAL REPRESENTATION.............................................................................163 Lesson 26 :STATISTICAL REPRESENTATION.............................................................................171 Lesson 27 :STATISTICAL REPRESENTATION.............................................................................179 Lesson 28 :MEASURES OF DISPERSION....................................................................................189 Lesson 29 :MEASURES OF DISPERSION....................................................................................197 Lesson 30 :MEASURE OF DISPERASION....................................................................................206 Lesson 31 :LINE FITTING...............................................................................................................212 Lesson 32 :TIME SERIES AND.......................................................................................................226 Lesson 33 :TIME SERIES AND EXPONENTIAL SMOOTHING.....................................................239 Lesson 34 :FACTORIALS...............................................................................................................245 Lesson 35 :COMBINATIONS..........................................................................................................254 Lesson 36 :ELEMENTARY PROBABILITY.....................................................................................261 Lesson 37:PATTERNS OF PROBABILITY: BINOMIAL, POISSON AND NORMAL DISTRIBUTIONS.........................................................................................................263 Lesson 38:PATTERNS OF PROBABILITY: BINOMIAL, POISSON AND NORMAL DISTRIBUTIONS.........................................................................................................267 Lesson 39:PATTERNS OF PROBABILITY: BINOMIAL, POISSON AND NORMAL DISTRIBUTIONS.........................................................................................................277 Lesson 40:PATTERNS OF PROBABILITY: BINOMIAL, POISSON AND NORMAL DISTRIBUTIONS.........................................................................................................283 Lesson 41: ESTIMATING FROM SAMPLES: INFERENCE...........................................................294 Lesson 42 :ESTIMATING FROM SAMPLE : INFERENCE.............................................................300 Lesson 43 :HYPOTHESIS TESTING: CHI-SQUARE DISTRIBUTION...........................................304 Lesson 44 :HYPOTHESIS TESTING : CHI-SQUARE DISTRIBUTION..........................................307 Lesson 45 :PLANNING PRODUCTION LEVELS: LINEAR PROGRAMMING...............................314 2 © Copyright Virtual University of Pakistan 1-Course Overview VU MTH 302 LECTURE 1 COURSE OVERVIEW COURSE TITLE The title of this course is “BUSINESS MATHEMATICS AND STATISTICS”. Instructor’s Resume The instructor of the course is Dr. Zahir Fikri who holds a Ph.D. in Electric Power Systems Engineering from the Royal Institute of Technology, Stockholm, Sweden. The title of Dr. Fikri’s thesis was “Statistical Load Forecasting for Distribution Network Planning”. Objective The purpose of the course is to provide the student with a mathematical basis for personal and business financial decisions through eight instructional modules. The course stresses business applications using arithmetic, algebra, and ratio-proportion and graphing. Applications include payroll, cost-volume-profit analysis and merchandising mathematics. The course also includes Statistical Representation of Data, Correlation, Time Series and Exponential Smoothing, Elementary Probability and Probability Distributions. This course stresses logical reasoning and problem solving skills. Access to Microsoft Excel software is required for the course. Course Outcomes Successful completion of this course will enable the student to: 1. Apply arithmetic and algebraic skills to everyday business problems. 2. Use ratio, proportion and percent in the solution of business problems. 3. Solve business problems involving commercial discount, markup and markdown. 4. Solve systems of linear equations graphically and algebraically and apply to cost volume- profit analysis. 5. Apply Statistical Representation of Data, Correlation, Time Series and Exponential Smoothing methods in business decision making 6. Use elementary probability theory and knowledge about probability distributions in developing profitable business strategies. Unit Outcomes Resources/Tests/Assignments Successful completion of the following units will enable the student to apply mathematical methods to business problems solving. Required Student Resources (Including textbooks and workbooks) Text: Selected books on Business Mathematics and Statistics. Optional Resources Handouts supplied by the professor. Instructor’s Slides Online or CD based learning materials. Prerequisites The students are not required to have any mathematical skills. Basic knowledge of Microsoft Excel will be an advantage but not a requirement. Evaluation In order to successfully complete this course, the student is required to meet the following evaluation criteria: Full participation is expected for this course All assignments must be completed by the closing date. Overall grade will be based on VU existing Grading Rules. All requirements must be met in order to pass the course. COURSE MODULES The following are the main modules of this course: Module 1 Overview (Lecture 1) Perform arithmetic operations in their proper order (Lecture 2) 3 © Copyright Virtual University of Pakistan 1-Course Overview VU Convert fractions their percent and decimal equivalents. (Lecture 2) Solve for any one of percent, portion or base, given the other two quantities. (Lecture 2) Using Microsoft Excel (Lecture 2) Calculate the gross earnings of employees paid a salary, an hourly wage or commissions. (Lecture 3) Calculate the simple average or weighted average given a set of values. (Lecture 4) Perform basic calculations of the percentages, averages, commission, brokerage and discount (Lecture 5) Simple and compound interest (Lecture 6) Average due date, interest on drawings and calendar (Lecture 6) Module 2 Exponents and radicals (Lecture 7) Solve linear equations in one variable (Lecture 7) Rearrange formulas to solve for any of its contained variables (Lecture 7) Solve problems involving a series of compounding percent changes (Lecture 8) Calculate returns from investments (Lecture 8) Calculate a single percent change equivalent to a series of percent changes (Lecture 8) Matrices ( Lecture 9) Ratios and Proportions ( Lecture10) Set up and manipulate ratios ( Lecture11) Allocate an amount on a prorata basis using proportions ( Lecture11) Assignment Module 1-2 Module 3 Discounts ( Lectures 12) Mathematics of Merchandising ( Lectures 13-16) Module 4 Applications of Linear Equations ( Lecture 17-18) Break-even Analysis ( Lecture 19-22) Assignment Module 3-4 Mid-Term Examination Module 5 Statistical data ( Lectures 23) Measures of central tendency ( Lectures 24-25) Measures of dispersion and skewness ( Lectures 26-27) Module 6 Correlation ( Lectures 28-29) Line Fitting (Lectures 30-31) Time Series and Exponential Smoothing ( Lectures 31-33) Assignment Module 5-6 Module 7 Factorials ( Lecture 34) Permutations and Combinations ( Lecture 34) Elementary Probability ( Lectures 35-36) Patterns of probability: Binomial, Poisson and Normal Distributions ( Lecture 37-40) Module 8 Estimating from Samples: Inference ( Lectures 41-42) Hypothesis testing : Chi-Square Distribution ( Lectures 43-44) Planning Production Levels: Linear Programming (Lecture 45) Assignment Module 7-8 End-Term Examination Note: The course modules are subject to change. MARKING SCHEME As per VU Rules 4 © Copyright Virtual University of Pakistan 1-Course Overview VU DESCRIPTION OF TOPICS LECTURE RECOMMENDED NO. MAIN TOPIC TOPICS READING 1 1.0 Module Applications of Overviewew (Lecture 1) Reference 1 1 Basic Mathematics ( Lectures 1-6) 2 Reference 2, Course Overview Module Lecture 2 Arithmetic Operations & 1 Tool: Microsoft Using Microsoft Excel Excel 3 Reference 2, Module Calculate Gross Earnings Lecture 3 1 Using Microsoft Excel Tool: Microsoft Excel 4 Reference 2, Calculating simple or Lecture 4 Module weighted averages Tool: Microsoft 1 Using Microsoft Excel Excel Reference 6 5 Basic calculations of Reference 2, percentages, averages, Lecture 5 Module commission, brokerage and Reference 3, Ch 3 1 discount using Tool: Microsoft Microsoft Excel Excel 6 Reference 2, Simple and compound Lecture 6 Module interest Reference 3, Ch 3 1 Average due date, interest on drawings and calendar Tool: Microsoft Excel 7 Exponents and radicals Reference 2, Simplify algebraic 2.0 Lecture 7 expressions Module Applications of Reference 3, Ch 2 2 Solve linear equations in Basic Algebra Tool: Microsoft one variable ( Lectures 7-9) Excel Rearrange formulas to solve for any of its contained variables 8 Calculate returns from investments Reference 2, Problems involving a series Lecture 8 of Reference 3, Ch 3 compounding percent changes Single percent change Tool: Microsoft equivalent Excel to a series of percent changes 5 © Copyright Virtual University of Pakistan 1-Course Overview VU 9 Reference 2, Lecture 9 Reference 3, Ch 4 Matrices Tool: Microsoft Excel 10 Set up and manipulate ratios. Reference 2, 3.0 Set up and solve Lecture 10 Applications proportions. Reference 3, Ch 3 Module of Ratio and Express percent differences 2 Proportion using proportions. ( Lectures 10- Allocate an amount on a Tool: Microsoft 11) prorata basis using Excel proportions. 11 Reference 2, Set up and manipulate Lecture 11 Module ratios. Reference 3, Ch 3 2 Allocate an amount on a Tool: Microsoft prorata basis using proportions Excel 12 4.0 Calculate the net price of an Reference 2, Merchandising item after single or multiple trade Lecture 12 Module and Financial discounts. Reference 3, Ch 3 3 Mathematics Calculate an equivalent ( Lectures 12- single discount rate given a series Tool: Microsoft 16) of discounts. Excel 13 Reference 2, Lecture 13 Solve merchandising pricing Module Reference 3, Ch 3 problems involving markup and 3 Tool: Microsoft markdown. Excel 14 Reference 2, Lecture 14 Reference 3, Ch 3 Module Reference 5, Ch Financial Mathematics Part 1 3 16 Tool: Microsoft Excel 15 Reference 2, Lecture 15 Reference 3, Ch 3 Reference 5, Ch Module Financial Mathematics Part 2 16 3 Tool: Microsoft Excel 16 Reference 2, Module Lecture 16 Financial Mathematics Part 3 3 Reference 3, Ch 3 Reference 5, Ch 6 © Copyright Virtual University of Pakistan 1-Course Overview VU 16 Tool: Microsoft Excel 17 Reference 2, Lecture 17 5.0 Break-Even Reference 3, Ch 3 Module Analysis Graph a linear equation in two Reference 5, Ch 4 ( Lectures 17- variables. 16 & 18 22) Tool: Microsoft Excel 18 Reference 2, Lecture 18 Solve two linear equations with two Reference 3, Ch 2 Module unknowns Reference 5, Ch 1 4 Tool: Microsoft Excel 19 Perform linear cost-volume Reference 2, Module profit and break-even analysis. Lecture 19 4 Using a break-even chart Tool: Microsoft Excel 20 Perform linear cost-volume profit and break-even analysis. Reference 2, Module Using the algebraic Lecture 20 4 approach of solving the cost and Tool: Microsoft revenue functions Excel 21 Perform linear cost-volume Reference 2, profit and break-even analysis. Module Lecture 21 Using the contribution 4 Tool: Microsoft margin approach Excel 22 Perform linear cost-volume Reference 2, profit and break-even analysis. Module Lecture 22 Using Microsoft Excel 4 Tool: Microsoft Assignment Module 3-4 Excel Mid-Term Examination 6. Statistical 23 Reference 2, Representation Lecture 23 Module of Data Statistical Data Reference 5, Ch 5 ( Lectures 23- 5 27) Tool: Microsoft Excel 24 Reference 2, Lecture 24 Reference 4, Ch Statistical Representation Module 3 Measures of Central Tendency 5 Reference 5, Ch Part 1 6 Tool: Microsoft Excel 25 Reference 2, Module Statistical Representation Lecture 25 5 Measures of Central Reference 4, Ch 7 © Copyright Virtual University of Pakistan 1-Course Overview VU Tendency 3 Part 2 Reference 5, Ch 6 Tool: Microsoft Excel 26 Reference 2, Lecture 26 Reference 4, Ch Measures of Dispersion and Module 4 Skewness 5 Reference 5, Ch Part 1 6 Tool: Microsoft Excel 27 Reference 2, Lecture 27 Reference 4, Ch 4 Measures of Dispersion and Reference 5, Ch Module Skewness 6 5 Part 2 Tool: Microsoft Excel 7. Correlation, 28 Reference 2, Time Series Lecture 28 and Reference 5, Ch Module Exponential Correlation 13 6 Smoothing Part 1 ( Lectures 28- Tool: Microsoft 33) Excel 29 Reference 2, Lecture 29 Reference 5, Ch Correlation 13 Part 2 Tool: Microsoft Excel 30 Reference 2, Lecture 30 Reference 5, Ch Line Fitting 14 Part 1 Tool: Microsoft Excel 31 Reference 2, Line Fitting Lecture 31 Part 2 Tool: Microsoft Excel 32 Reference 2, Time Series and Lecture 32 Exponential Smoothing Reference 5, Ch Part 1 15 Tool: Microsoft 8 © Copyright Virtual University of Pakistan 1-Course Overview VU Excel 33 Reference 2, Lecture 33 Time Series and Reference 5, Ch Exponential Smoothing 15 Part 2 Assignment Module 5-6 Tool: Microsoft Excel 34 Reference 2, Lecture 34 7. Elementary Reference 3, Ch Probability Factorials Module 2 ( Lectures 34- Permutations and 7 38) Combinations Tool: Microsoft Excel 35 Reference 2, Lecture 35 Reference 5, Ch Elementary Probability Module 8 Part 1 7 Tool: Microsoft Excel 36 Reference 2, Lecture 36 Reference 5, Ch Elementary Probability Module 8 Part 2 7 Tool: Microsoft Excel 37 Reference 2, Patterns of probability: Lecture 39 Binomial, Poisson and Normal Reference 5, Ch Module Distributions 9 7 Part 1 Tool: Microsoft Excel 38 Reference 2, Lecture 40 Patterns of probability: Reference 5, Ch Module Binomial, Poisson and Normal 9 7 Distributions Tool: Microsoft Part 2 Excel 39 Reference 2, Lecture 41 Patterns of probability: Reference 5, Ch Module Binomial, Poisson and Normal 9 7 Distributions Part 3 Tool: Microsoft Excel 9 © Copyright Virtual University of Pakistan 1-Course Overview VU 40 Reference 2, Lecture 41 Patterns of probability: Reference 5, Ch Module Binomial, Poisson and Normal 9 7 Distributions Part 4 Tool: Microsoft Excel 8. Probability 41 Reference 2, Distributions Lecture 42 ( Lectures 39- Estimating from Samples: Reference 5, Ch Module 44) Inference 10 8 9. Linear Part 1 Programming Tool: Microsoft (Lecture 45) Excel 42 Reference 2, Lecture 43 Estimating from Samples: Reference 5, Ch Module Inference 10 8 Part 2 Tool: Microsoft Excel 43 Reference 2, Lecture 44 Reference 5, Ch Module Hypothesis testing : Chi- 11 8 Square Distribution Part 1 Tool: Microsoft Excel 44 Reference 2, Lecture 45 Hypothesis testing : Chi- Module Reference 5, Ch Square Distribution Part 2 8 11 Tool: Microsoft Excel 45 Reference 2, Production Planning: Lecture 45 Linear Programming Module Reference 5, Ch Assignment Module 7-8 8 18 End Term Examination Tool: Microsoft Excel Methodology There will be 45 lectures each of 50 minutes duration as indicated above. The lectures will be delivered in a mixture of Urdu and English. The lectures will be heavily supported by slide presentations. The slides for a lecture will be made available on the VU website for the course a few days before the actual lecture is televised. This will allow students to carry out preparatory reading before the lecture. The course will be provided its own page on the VU’s web site. This will be used to provide lecture and other supporting material from the course to the students. The page will have a link to a web-based discussion and bulletin board for the students. Teaching assistants will be assigned by VU to provide various forms of assistance such as grading, answering questions posted by students and preparation of slides. 10 © Copyright Virtual University of Pakistan 1-Course Overview VU Text and Reference Material The course is based on material from different sources. Topics for reading will be indicated on course web site and in professor’s handouts, also to be posted on the course web site. A list of reference books will also be posted and updated on the course web site. The following material will be used by the students as reference: Reference 1: Course Outline 2: Instructor’s Power Point Slides 3: Business Mathematics & Statistics by Prof. Miraj Din Mirza 4: Elements of statistics & Probability by Shahid Jamal 5: Quantitative Approaches in Business studies by Clare Morris 6: Microsoft Excel Help File Schedule of Lectures Given above is the tentative schedule of topics to be covered. Minor changes may occur but these will be announced well in advance. 11 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU LECTURE 2 Applications of Basic Mathematics Part 1 OBJECTIVES The objectives of the lecture are to learn about: Different course modules Basic Arithmetic Operations Starting Microsoft (MS) Excel Using MS Excel to carry out arithmetic operations COURSE MODULES This course comprises 8 modules as under: Modules 1-4: Mathematics Modules 5-8: Statistics Details of modules are given in handout for lecture 01. BASIC ARITHMETIC OPERATIONS Five arithmetic operations provide the foundation for all mathematical operations. These are: Addition Subtraction Multiplication Division Exponents Example- Addition 12 + 5 = 17 Example- Subtraction 12 - 5 = 7 Example- Multiplication 12 x 5 = 60 Example- Exponent (4)^2 = 16 (4)^1/2 = 2 (4)^-1/2 = 1/(4)^1/2 = ½ = 0.5 MICROSOFT EXCEL IN BUSINESS MATHEMATICS & STATISTICS Microsoft Corporation’s Spreadsheet software Excel is widely used in business mathematics and. statistical applications. This course is based on wide applications of EXCEL 2002. It is recommended that you install EXCEL 2002 XP software on your computer. If your computer has Windows 2000 and EXCEL 2000 even that version of EXCEL can be used as the applications we intend to learn can be done using the earlier version of EXCEL. Those of you who are still working with Windows 98 and have EXCEL 97 installed are encouraged to migrate to newer version of EXCEL software. Starting EXCEL 2000 XP EXCEL 2000 XP can be started by going through the following steps: Click Start on your computer Click All Programs Click Microsoft Excel 12 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU The following slides show the operations: The EXCEL window opens and a blank worksheet becomes available as shown below: 13 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU The slide shows a Workbook by the name book1 with three sheets: Sheet1, Sheet2 and Sheet3. The Excel Window has Column numbers starting from A and row numbers starting from 1. the intersection of a row and column is called a Cell. The first cell is A1 which is the intersection of column A and row 1. All cells in a Sheet are referenced by a combination of Column name and row number. Example 1: B15 means cell in column B and row 15. Example 2: A cell in row 12 and column C has reference C12. A Range defines all cells starting from the leftmost corner where the range starts to the rightmost corner in the last row. The Range is specified by the starting cell, a colon and the ending cell. Example 3: A Range which starts from A1 and ends at D15 is referenced by A1:D15 and has all the cells in columns A to D up to and including row 15. A value can be entered into a cell by clicking that cell. The mouse pointer moves to the selected cell. Simply enter the value followed by the Enter key. The mouse pointer moves to the cell below. If you make a mistake while entering the value select the cell again (by clicking it). Enter the new value. The old value is replaced by the new value. If only one or more digits are to be changed then select the cell. Then double click the mouse. The blinking cursor appears. Either move the arrow key to move to the 14 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU digit to be changed or move the cursor to the desired position. Enter the new value and delete the undesired value by using the Del key. I suggest that you learn the basic operations of entering, deleting and changing data in a worksheet. About calculation operators in Excel In Excel there are four different types of operators: Arithmetic operators Comparison operators Text concatenation operator Reference operators The following descriptions are reproduced from Excel’s Help file for your ready reference. In the present lecture you are directly concerned with arithmetic operators. However, it is important to learn that the comparison operators are used where calculations are made on the basis of comparisons. The text concatenation operator is used to combine two text strings. The reference operators include “:” and “,” or ; as the case maybe. We shall learn the use of these operators in different worksheets. You should look through the Excel Help file to see examples of these functions. Selected material from Excel Help File relating to arithmetic operations is given in in a separate file. The Excel arithmetic operators are as follows: Addition. Symbol: + (Example: =5+4 Result: 9) Subtraction. Symbol: - (Example: =5-4 Result: 1) Multiplication. Symbol: * (Example: =5*4 Result: 20) Division. Symbol: / (Example: =12/4 Result: 3) Percent. Symbol: % (Example: =20% Result: 0.2) Exponentiation: ^ (Example: =5^2 Result: 25) Excel Formulas for Addition All calculations in Excel are made through formulas which are written in cells where result is required. Let us do addition of two numbers 5 and 10. We wish to calculate the addition of two numbers 10 and 5. Let us see how we can add these two numbers in Excel. 1. Open a blank worksheet. 2. Click on a cell where you would like to enter the number 10. Say cell A15. 3. Enter 10 in cell A15. 4. Click cell where you would like to enter the number 5. Say cell B15. 5. Click cell where you would like to get the sum of 10 and 5. Say cell C15. 6. Start the formula. Write equal sign = in cell C15. 7. After =, write “(“ (left bracket) in cell C15. 8. Move mouse and left click on value 10 which is in cell A15. In cell C15, the cell reference A15 is written. 9. Write “+” after “A15” in cell C15. 10. Move mouse and left click on value 5 which is in cell B15. In cell C15, the cell reference B15 is written. 11. Write “) “ (right bracket) in cell C15. 12. Press Enter key The answer 15 is shown in cell C15. If you click on cell C15, the formula “=A15+B15” is displayed the formula bar to the right of fx in the Toolbar. The main steps along with the entries are shown in the slide below. The worksheet MTH302-lec-02 contains the actual entries. 15 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU The next slide shows addition of 6 numbers 5, 10, 15, 20, 30 and 40. The entries were made in row 34. The values were entered as follows: Cell A34: 5 Cell B34: 10 Cell C34: 15 Cell D34: 20 Cell E34: 30 Cell F34: 40 The formula was written in cell G34. The formula was: =5+10+15+20+30+40 The answer was 120. You can use an Excel function SUM along with the cell range A34:F34 to calculate the sum of the above numbers. The formula in such a case will be: =SUM(A34:F34) You enter “=” followed by SUM, followed by “(“. Click on the cell with value 5(reference: A34). Drag the mouse to cell with value 40(reference: F34) and drop the mouse. Enter “)” and then press the Enter key. 16 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU In the above two examples you learnt how formulas for addition are written in Excel. Excel Formula for Subtraction Excel formulas for subtraction are similar to those of addition but with the minus sign. Let us go through the steps for subtracting 15 from 25. Enter values in row 50 as follows: Cell A50: 25 Cell B50: 15 Write the formula in cell C50 as follows: =A50-B50 To write this formula, click cell C50, where you want the result. Enter “=”. Click on cell with value 25 (reference:A50). Enter “-“(minus sign). Click on cell with value 15 (reference B50). Press enter key. If you enter 15 first and 25 later, then the question will be to find result of subtraction 15-25. 17 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU Excel Formula for Multiplication Excel formula for multiplication is also similar to the formula for addition. Only the sign of multiplication will be used. The Excel multiplication operator is *. Let us look at the multiplication of two numbers 25 and 15. The entries will be made in row 60. Enter values as under: Cell A50: 25 Cell B50: 15 The formula for multiplication is: =A50*B50 Click on cell C50 to write the formula in that cell. Enter “=”. Click on cell with number 25 (reference: A50). Enter “*”. Click on cell with number 15 (reference: B50). Press Enter key. The answer is 375 in cell C50. 18 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU Excel Formula for Division The formula for division is similar to that of multiplication with the difference that the division sign “/” will be used. Let us divide 240 by 15using Excel formula for division. Let us enter numbers in row 75 as follows: Cell A75: 240 Cell B75: 15 The formula for division will be written in cell C75 as under: =A75/B75 The steps are as follows: Click the cell A75. Enter 240 in cell A75. Click cell B75. Enter 15. Click cell C75. Enter “=”. Click on cell with value 240 (reference: A75). Enter “/”. Click cell with number 15 (reference: B75). Press enter key. The answer 16 will be displayed in cell C75. Excel Formula for Percent The formula for converting percent to fraction uses the symbol %. To convert 20% to fraction the formula is as under: =20% If you enter 20 in cell A99, you can write formula for conversion to fraction by doing the following: Enter 2o in cell A99. In cell B99 enter “=”. Click on cell A99. Enter”%”. Press Enter key. The answer 0.2 is given in cell B99. 19 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU Excel Formula for Exponentiation The symbol for exponentiation is ^. The formula for calculating exponents is similar to multiplication with the difference that the carat symbol ^ will be used. Let us calculate 16 raised to the power 2 by Excel formula for exponentiation. The values will be entered in row 85. The steps are: Select Cell A85. Enter 16 in this cell. Select cell B85 Enter 2 in this cell. Select cell C85. Enter”=”. Select cell with value 16 (reference:A85). Enter “^”. Select number 2 (reference: B85) Press Enter key. The result 256 is displayed in cell C85. 20 © Copyright Virtual University of Pakistan 2-Applications of Basic Mathematics Part 1 VU Recommended Homework Download worksheet MTH302-lec-02.xls from the course web site. Change values to see change in results. Set up new worksheets for each Excel operator with different values. Set up worksheets with combinations of operations. 21 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU LECTURE 3 Applications of Basic Mathematics Part 2 OBJECTIVES The objectives of the lecture are to learn about: Evaluations Calculate Gross Earnings Using Microsoft Excel Evaluation In order to successfully complete this course, the student is required to meet the evaluation criteria: Evaluation Criterion 1 Full participation is expected for this course Evaluation Criterion 2 All assignments must be completed by the closing date Evaluation Criterion 3 Overall grade will be based on VU existing Grading Rules Evaluation Criterion 4 All requirements must be met in order to pass the course Grading There will be a term exam and one final exam; there will also be 4 assignments. The final exam will be comprehensive. These will contribute the following percentages to the final grade: Mid Term Exam 35% Final 50% 4 Assignments 15% Collaboration The students are encouraged to develop collaboration in studying this course. You are advised to carry out discussions with other students on different topics. It will be in your own interest to prepare your own solutions to Assignments. You are advised to make your original original submissions as copying other students’ assignments will have negative impact on your studies. ETHICS Be advised that as good students your motto should be: No copying No cheating No short cuts 22 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU Methodology There will be 45 lectures each of 50 minutes duration. The lectures will be delivered in a mixture of Urdu and Englis. The lectures will be heavily supported by slide presentations. The slides available on the VU website before the actual lecture is televised. Students are encouraged to carry out preparatory reading before the lecture. This course has its own page on the VU’s web site. There are lecture slides as well as other supporting material available on the web site. Links to a web-based discussion and bulletin board will also been provided. Teaching assistants will be assigned by VU to provide various forms of assistance such as grading, answering questions posted by students and preparation of slides Text and Reference Material This course is based on material from different sources. Topics for reading will be indicated on course web site and in professor’s handouts. A list of reference books to be posted and updated on course web site. You are encouraged to regularly visit the course web site for latest guidelines for text and reference material. Types of Employees There may be three types of employees in a company: Regular employees drawing a monthly salary Part time employees paid on hourly basis Payments on per piece basis To be able to understand how calculations of gross earnings are done, it is important to understand what gross earnings include. GROSS EARNINGS/SALARY Gross salary includes the following: Basic salary Allowances Gross salary may include: Basic salary House Rent Conveyance allowance Utilities allowance Accordance to the taxation rules if allowances are 50% of basic salary, the amount is treated as tax free. Any allowances that exceed this amount are considered taxable both for the employee as well as the company. Example 1 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 5,000 Rs. What is the taxable income of employee? Is any add back to the income of the company? % Allowances = (5000/10000) x 100 =50% Hence allowances are not taxable. 23 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU Total taxable income = 10,000 Rs. Add back to the income of the company = 0 Example 2 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 7,000 Rs. What is the taxable income of employee? Is any add back to the income of the company? % Allowances = (7000/10000) x 100 =70% Allowed non-taxable allowances = 50% = 0.5 x 10000 = 5,000 Rs. Taxable allowances = 70% – 50% = 7000 - 5000 = 2,000 Rs. Hence 2000 Rs. of allowances are taxable. Total taxable income = 10,000 + 2000 = 12,000 Rs. Add back to the income of the company = 20% allowances = 2,000 Rs. Structure of Allowances The common structure of allowances is as under: House Rent = 45 % Conveyance allowance = 2.5 % Utilities allowance = 2.5 % Example 3 The salary of an employee is as follows: Basic salary = 10,000 Rs. What is the amount of allowances if House Rent = 45 %, Conveyance allowance = 2.5 % and Utilities allowance = 2.5 %? House rent allowances = 0.45 x 10000 = 4,500 Rs. Conveyance allowance = 0.025 x 10000 = 250 Rs. Utilities allowance = 0.025 x 10000 = 250 Rs. Thus total allowances are 4500+250+250 = 5000Rs Provident Fund According to local laws, a company can establish a Provident Trust Fund for the benefit of the employees. By law, 1/11th of Basic Salary per month is deducted by the company from the gross earnings of the employee. An equal amount, i.e 1/11th of basic salary per month, is contributed by the company to the Provident Fund to the account of the employee. Thus there is an investment of 2/11th of basic salary on behalf of the employee in Provident Fund. The company can invest the savings in Provident Fund in Government Approved securities such as defense saving Certificates. Interest earned on investments in Provident Fund is credited to the account of the employees in proportion to their share in the Provident Fund. Example 4 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 5,000 Rs. What is the amount of deduction on account of contribution to the Provident Trust Fund? What is the contribution of the company? What is the total saving of the employee per month on account of Provident Trust Fund? Employee contribution to Provident Fund = 1/11 x 10000 = 909.1 Rs. Company contribution to Provident Fund = 1/11 x 10000 = 909.1 Rs. Total savings of employee in Provident Fund = 909.1 + 909.1 = 1,818.2 Rs. Gratuity Fund According to local laws, a company can establish a Gratuity Trust Fund for the benefit of the employees. By law, 1/11th of Basic Salary per month is contributed by 24 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU the company to the Gratuity Fund to the account of the employee. Thus there is a saving of 1/11th of basic salary on behalf of the employee in Gratuity Fund. The company can invest the savings in Gratuity Fund in Government Approved securities such as defence saving Certificates. Interest earned on investments in Gratuity Fund is credited to the account of the employees in proportion to their share in the Gratuity Fund. Example 5 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 5,000 Rs. What is the contribution of the company on account of gratuity to the Gratuity Trust Fund? Company contribution to Gratuity Fund = Total savings of employee in Gratuity Fund = 1/11 x 10000 = 909.1 Rs. Leaves All companies have a clear leaves policy. The number of leaves allowed varies from company to company. Typical leaves allowed may be as under: Casual Leave = 18 Days per year Earned Leave = 18 Days per year Sick Leave = 12 Days per year Example 6 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 5,000 Rs. What is the cost on account of casual, earned and sick leaves per year if normal working days per month is 22? What is the total cost of leaves as percent of gross salary? Gross salary = 10000 + 5000 = 15,000 Rs. Cost of casual leaves per year = {18 / (22 x 12)} x 15000 x 12 = 12,272.7 Rs. Cost of earned leaves per year = {18 / (22 x 12)} x 15000 x 12= 12,272.7 Rs Cost of Sick leaves per year = {12 / (22 x 12) x 15000 x 12 = 8,181.8 Rs Total cost of leaves per year = 12272.7 + 12272.7 + 8181.8 = 32,727.3 Rs. Total cost of leaves as percent of gross salary = (32727.3/(12 x 15000))x 100 = 18.2% Social Charges Social charges comprise leaves, group insurance and medical. Typical medical/group insurance is about 5% of gross salary. Other social benefits may include contribution to employee’s children’s education, club membership, leave fare assistance etc. Such benefits may be about 5.8%. Leaves are 18.2% of gross salary (as calculated in above example) Total social charges therefore may be = 18.2 + 5 + 5.8 = 29% of gross salary. Other companies may have more social benefits. The 29% social charges are quite common. Example 7 The salary of an employee is as follows: Basic salary = 10,000 Rs. Allowances = 5,000 Rs. What is the cost of the company on account of leaves (18.2%), group insurance/medical (5%) and other social benefits (5.8%)? Leaves cost = 0.182 x 15000 = 2,730 Rs. Group insurance/medical = 0.05 x 15000 = 750 Rs. Other social benefits = 0.058 x 15000 = 870 Rs. Total social charges = 2730 + 750 + 870 = 4,350 Rs. 25 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU SUMMARY Summary of different components of salary is as follows: Basic salary Allowances 50 % of basic salary Gratuity 9.09 % of basic salary Provident Fund 9.09 % of basic salary Social Charges 29 % Gross remuneration is pay or salary, typically monetary payment for services rendered, as in an employment. It includes. 1. Basic Salary 2. House rent allowance 3. Conveyance allowance 4. Utilities 5. Provident fund 6. Gratuity fund 7. Leaves 8. Group insurance (medical etc) 9. Miscellaneous social charges. Benefits can also include more factors and are not limited to the above list. The purpose of the benefits is to increase the economic security of employees Example 8 The salary of an employee is as follows: Basic salary = 6,000 Rs. 26 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU Converting fraction to percent Calculate percent by multiplying fraction by 100. and put the percent sign (%) Percent = Fraction X 100 Example 9 Convert 0.1 to percent. 0.1 X 100 = 10% Common Fraction Common fraction is a fraction having an integer as a numerator and an integer as a denominator. For example ½, 10/100 are common fractions. Converting percent into Common Fraction Example 11 20%= 20/100= 0.2 Decimal fraction. Any number written in the form: an integer followed by a decimal point followed by a (possibly infinite) string of digits. For example 2.5, 3.9 etc. Converting percent into decimal fraction Example 20% = 0.2 27 © Copyright Virtual University of Pakistan 3-Applications of Basic Mathematics Part 2 VU Percent 20% or 20/100=0.2 Percentage Percentage is formed by multiplying a number called the base by a percent, called the rate. Thus Percentage = Base x Rate Example 13 What percentage is 20% x of 120? Here, rate = 20% = 20/100 = 0.2 Base = 120 Percentage = 20/100 x 120 Or 0.2 X 120 = 24 Example 14 What Percentage is 6 % of 40? Percentage = Rate X Base = 0.06 X 40 = 2.4 Base Base = Percentage/Rate Example 15 Find base if Rate = 24.0 % = 0.24 Percentage = 96 Base= 96/0.24=400 28 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU LECTURE 4 Applications of Basic Mathematics Part 3 OBJECTIVES The objectives of the lecture are to learn about: Review Lecture 3 Calculating simple or weighted averages Using Microsoft Excel Gross Remuneration The following slide shows worksheet calculation of Gross remuneration on the basis of 6000 Rs, basic salary. As explained earlier, house rent is 45% of basic salary. Conveyance and Utilities Allowance are both 2.5% of basic salary. Both Gratuity and Provident fund are 1/11th of basic salary. The arithmetic formulas are as follows: Excel formulas are within brackets. Basic salary = 6000 Rs. House rent = 0.45 x 6000 = 2700 Rs. (Excel formula: =$B$93*0.45) Conveyance Allowance = 0.025 x 6000 = 150 Rs. (Excel formula: =$B$93*0.025) Utilities allowance = 0.025 x 6000 = 150 Rs. (Excel formula: =$B$93*0.025) Gross salary = 6000 + 2700 + 150 + 150 = 9000 Rs. (Excel formula: =SUM(B93:B96) Gratuity = 1/11 x 6000 = 545 (Excel formula: =ROUND((1/11)*$B$93,0) In the Excel formulas the $ sign is used before the row and column reference to fix the location of the cell. Since house rent, CA, utilities, gratuity and provident fund are calculated with respect to basic salary so by using $B$93, we fixes the location of cell B93. This feature can be used for quick and correct calculation of all allowances and benefits. Now let us see cell by cell calculation. 29 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU 30 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU In Gratuity and provident calculations the function ROUND is used to round off values to desired number of decimals. In our case we used the value after the semicolon to indicate that no decimal is required. If you want 1 decimal use the value 1. for 2 decimals use 2 as the second parameter to the ROUND function. The first parameter is the expression for calculation 1/11*$B$93. 31 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU In the calculation for social charges the formula is B93*(29/100). Here 29/100 means 29% social charges. The $ sign was not used here to explain another feature of excel. If the formula in cell D93 is copied to cell E93 (say), the cell reference B93 in formula changes to C93. $B$93 would be needed to fix the value of basic salary in cell E93. AVERAGE Average (Arithmetic Mean) = Sum /N Sum= Sum of all data values 32 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU N= number of data values EXAMPLE 1 Data: 10, 7, 9, 27, 2 Sum: = 10+7+9+27+2 = 55 There are 5 data values Average = 55/5 = 11 ADDING NUMBERS USING MICROSOFT EXCEL 1. Add numbers in a cell 2. Add all contiguous numbers in a row or column 3. Add noncontiguous numbers 4. Add numbers based on one condition 5. Add numbers based on multiple conditions 6. Add numbers based on criteria stored in a separate range 7. Add numbers based on multiple conditions with the Conditional Sum Wizard 1. Add numbers in a cell Type =5+10 in a cell Result 15. See Example 2 2. Add all contiguous numbers in a row or column using AutoSum If data values are in contiguous cells of a column, click a cell next to last data value in the same column (If data values are in contiguous cells of a row then click a cell at right side of last data value) Click AutoSum symbol, Σ , in tool bar Press ENTER This will add all the data values. See Example 33 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU 3. Add noncontiguous numbers Use the SUM function See Example 4. Add numbers based on one condition Use the SUMIF function to create a total value for one range, based on a value in another range. 34 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU 5. Add numbers based on multiple conditions Use the IF and SUM functions to do this task 6. Add numbers based on criteria stored in a separate range Use the DSUM function to do this task DSUM Adds the numbers in a column of a list or database that match conditions you specify. Syntax DSUM(database,field,criteria) Database is the range of cells that makes up the list or database. Field indicates which column is used in the function. 35 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU Criteria is the range of cells that contains the conditions you specify. DSUM EXAMPLE =DSUM(A4:E10;"Profit“;A1:F2) The total profit from apple trees with a height between 10 and 16 (75) AVERAGE USING MICROSOFT EXCEL AVERAGE Returns the average (arithmetic mean) of the arguments. Syntax AVERAGE(number1,number2,...) Number1, number2,... are 1 to 30 numeric arguments for which you want the average. Calculate the average of numbers in a contiguous row or column Calculate the average of numbers not in contiguous row or column 36 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU WEIGHTED AVERAGE: Weighted average is one type of arithmetic mean of a set of data, in which some elements of the set carry more importance (weight) than others. 37 © Copyright Virtual University of Pakistan 4-Applications of Basic Mathematics Part 3 VU If {x1, x2, x3,........xn} is a set of n number of data and {w1, w2, w3,...wn} are corresponding weights of the data then Weighted average = (x1)(w1) + (x2)(w2) + (x3)(w3) +...........+ (xn)(wn) Be careful about one thing that the weights should be in fraction. Grades are often computed using a weighted average. Suppose the weightage of homework is 10%, quizzes 20%, and tests 70%. Here weights of homework, quizzes, tests are already in fraction i-e10% = 0.1, 20% = 0.2, 70% = 0.7 respectively. If Ahmad has a homework grade of 92, a quiz grade of 68, and a test grade of 81then Ahmad's overall grade = (0.10)(92) + (0.20)(68) + (0.70)(81) = 79.5 Let us see another example Labor hours per Grade of Labor Hourly wages (Rs) unit of labor Skilled 6 300 Semiskilled 3 200 Unskilled 1 100 Here weights (Labor hours per unit of labor) are not in fraction. So first we convert them to fraction. Total labor hours =6 + 3 + 1 =10 Grade of Labor hours per unit labor of labor (in fraction) Skilled 6/10 = 0.6 Semiskilled 3/10 = 0.3 Unskilled 1/10 = 0.1 Weighted average = (0.6)(300) + (0.3)(200) + (0.1)(100) = 250 Rs per hour 38 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU LECTURE 5 Applications of Basic Mathematics Part 4 OBJECTIVES The objectives of the lecture are to learn about: Review of Lecture 4 Basic calculations of percentages, salaries and investments using Microsoft Excel PERCENTAGE CHANGE Monday’s Sales were Rs.1000 and grew to Rs. 2500 the next day. Find the percent change. METHOD Change = Final value – initial value Percentage change = (Change / initial value) x 100% CALCULATION Initial value =1000 Final value = 2500 Change = 1500 % Change = (1500/1000) x 100 = 150% The calculations using Excel are given below. First the entries of data were made as follows: Cell C4 = 1000 Cell C5 = 2500 In cell C6 the formula for increase was: =C5 – C4 The result was 1500. In cell C7 the formula for percentage change was: = C6/C4*100 The result 150 is shown in the next slide. 39 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU EXAMPLE 1 How many Percent is Next Day’s sale with reference to Monday’s Sale? Monday’s sale= 1000 Next day’s sale= 2500 Next day’s sale as % = 2500/1000 x 100 = 250 % = Two and a half times EXAMPLE 2 40 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU In the making of dried fruit, 15kg. of fresh fruit shrinks to 3 kg of dried fruit. Find the percentage change. Calculation Original fruit = 15 kg Final fruit = 3 kg Change = 3-15 = -12 % change = - 12/15 x 100 = - 80 % Size was reduced by 80% Calculations in Excel were done as follows: 41 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU Data entry Cell D19: 15 Cell D20: 3 Formulas Formula for change in Cell D21: = D20 – D19 Formula for %change in Cell D22: = D21/D19*100 Results Cell D21 = -12 kg Cell D22 = -80 % EXAMPLE 3 After mixing with water the weight of cotton increased from 3 kg to 15 kg. Find the percentage change. CALCULATION Original weight = 3 kg Final weight = 15 kg Change = 15-3= 12 % change = 12/3 x 100 = 400 % Weight increased by 400% Calculations in Excel were done as follows: Data entry Cell D26: 3 Cell D27: 15 Formulas 42 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU Formula for change in Cell D28: = D27 – D26 Formula for %change in Cell D29: = D28/D26*100 Results Cell D28 = 12 kg Cell D29 = 400 % EXAMPLE 4 A union signed a three year collective agreement that provided for wage increases of 3%, 2%, and 1% in successive years An employee is currently earning 5000 rupees per month What will be the salary per month at the end of the term of the contract? Calculation = 5000(1 + 3%)(1 + 2%)(1 + 1%) = 5000 x 1.03 x 1.02 x 1.01 = 5306 Rs. Calculations using Excel are shown in the following slides. 43 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU Calculations in Excel were done as follows: Data entry 44 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU Cell C35: 5000 Cell C36: 3 Cell C38: 2 Cell C40: 1 Formulas Formula for salary in year 2 in Cell C37: =ROUND(C35*(1+C36/100);0) Formula for salary year 3 in Cell C39: =ROUND( C37*(1+C38/100);0) Formula for salary at the end of year 3 in Cell C41: =ROUND(C39*(1+C39/100);0) Results Cell C37 = 5150 Rs. Cell C39 = 5253 Rs. Cell C41= 5306 Rs. EXAMPLE 5 An investment has been made for a period of 4 years. Rates of return for each year are 4%, 8%, -10% and 9% respectively. If you invested Rs. 100,000 at the beginning of the term, how much will you have at the end of the last year? 45 © Copyright Virtual University of Pakistan 5-Applications of Basic Mathematics Part 4 VU Calculations in Excel were done as follows: Data entry Cell C46: 100000 Cell C47: 4 Cell C49: 8 Cell C51: -10 Cell C53: 9 Formulas Formula for value in year 2 in Cell C48: = ROUND(C46*(1+C47/100);0) Formula for value in year 3 in Cell C50: = ROUND(C48*(1+C49/100);0) Formula for value in year 4 in Cell C52: = ROUND(C50*(1+C51/100);0) Formula for salary end of year 4 in Cell C54: = ROUND(C52*(1+C53/100);0) Results Cell C48 = 104000 Rs. Cell C50 = 112320 Rs. Cell C52 = 101088 Rs. Cell C54 = 110186Rs. 46 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU LECTURE 6 Applications of Basic Mathematics Part 5 OBJECTIVES The objectives of the lecture are to learn about: Review Lecture 5 Discount Simple and compound interest Average due date, interest on drawings and calendar REVISION LECTURE 5 A chartered bank is lowering the interest rate on its loans from 9% to 7%. What will be the percent decrease in the interest rate on a given balance? A chartered bank is increasing the interest rate on its loans from 7% to 9% What will be the percent increase in the interest rate on a given balance? As we learnt in lecture 5, the calculation will be as follows: Decrease in interest rate = 7-9 = -2 % % decrease = -2/9 x 100 = -22.2 % Increase in interest rate = 9-7 = 2 % % increase = 2/7 x 100 = 28.6 % The calculations in Excel are shown in the following slides: DECREASE IN RATE Data entry Cell F4 = 9 Cell F5 = 7 Formulas Formula for decrease in Cell F6: = =F5-F4 Formula for % decrease in Cell F7: =F6/F4*100 Results Cell F6 = -2 Cell F7 = -22.2 INCREASE IN RATE Data entry Cell F14 = 7 Cell F15 = 9 Formulas Formula for increase in Cell F16: =F15-F14 Formula for % increase in Cell F17: =F16/F14*100 Results Cell F16 = 2 Cell F17= 28.6 47 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU The Definition of a Stock Plain and simple, stock is a share in the ownership of a company. Stock represents a claim on the company's assets and earnings. As you acquire more stock, your ownership stake in the company becomes greater. Whether you say shares, equity, or stock, it all means the same thing. 48 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU Stock yield With stocks, yield can refer to the rate of income generated from a stock in the form of regular dividends. This is often represented in percentage form, calculated as the annual dividend payments divided by the stock's current share price. Earnings per share (EPS) The EPS is the total profits of a company divided by the number of shares. A company with $1 billion in earnings and 200 million shares would have earnings of $5 per share. Price-earnings ratio A valuation ratio of a company's current share price compared to its per-share earnings. Calculated as: For example, if a company is currently trading at $43 a share and earnings over the last 12 months were $1.95 per share, the P/E ratio for the stock would be 22.05 ($43/$1.95). Outstanding shares Stock currently held by investors, including restricted shares owned by the company's officers and insiders, as well as those held by the public. Shares that have been repurchased by the company are not considered outstanding stock. Net current asset value per share(NCAVPS) NCAVPS is calculated by taking a company's current assets and subtracting the total liabilities, and then dividing the result by the total number of shares outstanding. Current Assets The value of all assets that are reasonably expected to be converted into cash within one year in the normal course of business. Current assets include cash, accounts receivable, inventory, marketable securities, prepaid expenses and other liquid assets that can be readily converted to cash. Liabilities A company's legal debts or obligations that arise during the course of business operations. Market value The price at which investors buy or sell a share of stock at a given time Face value Original cost of a share of stock which is shown on the certificate. Also referred to as "par value." Face value is usually a very small amount that bears no relationship to its market price. 49 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU Dividend Usually, a company distributes a part of the profit it earns as dividend. For example: A company may have earned a profit of Rs 1 crore in 2003-04. It keeps half that amount within the company. This will be utilised on buying new machinery or more raw materials or even to reduce its borrowing from the bank. It distributes the other half as dividend. Assume that the capital of this company is divided into 10,000 shares. That would mean half the profit -- ie Rs 50 lakh (Rs 5 million) -- would be divided by 10,000 shares; each share would earn Rs 500. The dividend would then be Rs 500 per share. If you own 100 shares of the company, you will get a cheque of Rs 50,000 (100 shares x Rs 500) from the company. Sometimes, the dividend is given as a percentage -- i e the company says it has declared a dividend of 50 percent. It's important to remember that this dividend is a percentage of the share's face value. This means, if the face value of your share is Rs 10, a 50 percent dividend will mean a dividend of Rs 5 per share BUYING SHARES If you buy 100 shares at Rs. 62.50 per share with a 2% commission, calculate your total cost. Calculation 100 * Rs. 62.50 = Rs. 6,250 0.02 * Rs. 6,250 = 125 Total = Rs. 6,375 RETURN ON INVESTMENT Suppose you bought 100 shares at Rs. 52.25 and sold them after 1 year at Rs. 68. With a 1% commission rate of buying and selling the stock and 10 % dividend per share is due on these shares. Face value of each share is 10Rs. What is your return on investment? Bought 100 shares at Rs. 52.25 = 5,225.00 Commission at 1% = 52.25 Total Costs =5,225 + 52.25 = 5,277.25 Sold 100 shares at Rs. 68 = 6,800.00 Commission at 1% = - 68.00 Total Sale = 6,800 – 68 = 6,732.00 Gain Net receipts = 6,732.00 Total cost = - 5,277.25 Net Gain = 6,732 – 5,277.25 =1,454.75 Dividends (100*10/10) = 100.00 Total Gain = 1,454.75 + 100 = 1,554.75 Return on investment = 1,554.75/5,277.25*100 = 29.46 % The calculations using Excel were made as follows: BOUGHT Data entry Cell B21: 100 Cell B22: 52.25 Formulas 50 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU Formula for Cost of 100 shares at Rs. 52.25 in Cell B23: =B21*B22 Formula for Commission at 1% in Cell B24: =B23*0.01 Formula for Total Costs in Cell B25: =B23+B24 Results Cell B23 = 5225 Cell B24 = 52.25 Cell B25 = 5277.25 SOLD Data entry Cell B28: 68 Formulas Formula for sale of 100 shares at Rs. 68 in Cell B29: =B21*B28 Formula for Commission at 1% in Cell B30: =B29*0.01 Formula for Total Sale in Cell B31: =B29-B30 Results Cell B29 = 6800 Cell B30 = 68 Cell B31 = 6732 51 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU GAIN Formulas Formula for Net receipts in Cell B34: =B31 Formula for Total cost in Cell B35: =B25 Formula for Net Gain in Cell B36: =B31-B25 Formula for % Gain in Cell B37: =B36/B35*100 Results Cell B34 = 6732 Cell B35 = 5277.25 Cell B36 = 1454.75 Cell B37 = 27.57 DISCOUNT Discount is Rebate or reduction in price. Discount is expressed as % of list price. Example List price = 2200 Discount Rate = 15% Discount? = 2200 x 0.15= 330 Calculation using Excel along with formula is given in the following slide: 52 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU NET COST PRICE Net Cost Price = List price - Discount Example List price = 4,500 Rs. Discount = 20 % Net cost price? Net cost price = 4,500 – 20 % of 4,500 = 4,500 – 0.2 x4,500 =4,500 – 900 = 3,600 Rs. Calculation using Excel along with formula is given in the following slide: SIMPLE INTEREST 53 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU P = Principal R = Rate of interest percent per annum T = Time in years I = Simple interest then I = P. R. T / 100 Thus total amount A to be paid at the end of T years = P + I Example P = Rs. 500 T = 4 years R =11% Find simple interest I = P x T x R /100 = 500 x 4 x 11/100 = Rs. 220 Calculation using Excel along with formula is given in the following slide: COMPOUND INTEREST Compound Interest also attracts interest. Example P = 800 Interest year 1= 0.1 x 800= 80 New P = 800 + 80 = 880 Interest on 880 = 0.1 X 880 = 88 New P = 880 + 88 = 968 Calculation using Excel along with formula is given in the following slide: 54 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU CoCompound Interest Formula S = Money accrued after n years also called compound amount P = Principal r = Rate of interest n = Number of periods S = P(1 + r/100)^ n Compound interest = S - P Example Calculate compound interest earned on Rs. 750 invested at 12% per annum for 8 years. S= P(1+r/100)^8 = 750(1+12/100)^8 =1857 Rs Compound interest = 1857 – 750 = 1107 Rs 3646.5= 3000(1+0.05)^n 3646.5/3000 = (1+0.05)^n 55 © Copyright Virtual University of Pakistan 6-Applications of Basic Mathematics Part 5 VU Calculation using Excel along with formula is given in the following slide 56 © Copyright Virtual University of Pakistan 7-Applications of Basic Mathematics VU LECTURE 7 Applications of Basic Mathematics OBJECTIVES The objectives of the lecture are to learn about: Scope of Module 2 Review of lecture 6 Annuity Accumulated value Accumulation Factor Discount Factor Discounted value Algebraic operations Exponents Solving Linear equations Module 2 Module 2 covers the following lectures: Linear Equations (Lectures 7) Investments (Lectures 8) Matrices (Lecture 9) Ratios & Proportions and Index Numbers (Lecture 10) Annuity It some point in your life you may have had to make a series of fixed payments over a period of time - such as rent or car payments - or have received a series of payments over a period of time, such as bond coupons. These are called annuities. Annuities are essentially series of fixed payments required from you or paid to you at a specified frequency over the course of a fixed period of time. An annuity is a type of investment that can provide a steady stream of income over a long period of time. For this reason, annuities are typically used to build retirement income, although they can also be a tool to save for a child’s education, create a trust fund, or provide for a surviving spouse or heirs. The most common payment frequencies are yearly (once a year), semi-annually (twice a year), quarterly (four times a year) and monthly (once a month). Calculating the Future Value or accumulated value of an Annuity If you know how much you can invest per period for a certain time period, the future value of an ordinary annuity formula is useful for finding out how much you would have in the future by investing at your given interest rate. If you are making payments on a loan, the future value is useful for determining the total cost of the loan. Let's now run through Example 1. Consider the following annuity cash flow schedule: In order to calculate the future value of the annuity, we have to calculate the future value of each 57 © Copyright Virtual University of Pakistan 7-Applications of Basic Mathematics VU cash flow. Let's assume that you are receiving $1,000 every year for the next five years, and you invested each payment at 5%. The following diagram shows how much you would have at the end of the five-year period: Since we have to add the future value of each payment, you may have noticed that, if you have an annuity with many cash flows, it would take a long time to calculate all the future values and then add them together. Fortunately, mathematics provides a formula that serves as a short cut for finding the accumulated value of all cash flows received from an annuity: C=Payment per period or amount of annuity i = interest rate n = number of payments ((1 + i)n - 1) / i) is called accumulation factor for n periods. Accumulated value of n period = payment per period × accumulation factor for n periods If we were to use the above formula for Example 1 above, this is the result: =$1000*[5.53] =$5525.63 Note that the $0.01 difference between $5,525.64 and $5,525.63 is due to a rounding error in the first calculation. Each of the values of the first calculation must be rounded to the nearest penny - the more you have to round numbers in a calculation the more likely rounding errors will occur. So, the above formula not only provides a short-cut to finding FV of an ordinary annuity but also gives a more accurate result. Calculating the Present Value or discounted value of an Annuity If you would like to determine today's value of a series of future payments, you need to use the formula that calculates the present value of an ordinary annuity. For Example 2, we'll use the same annuity cash flow schedule as we did in Example 1. To obtain the total discounted value, we need to take the present value of each future payment and, as we did in Example 1, add the cash flows together. 58 © Copyright Virtual University of Pakistan 7-Applications of Basic Mathematics VU Again, calculating and adding all these values will take a considerable amount of time, especially if we expect many future payments. As such, there is a mathematical shortcut we can use for PV of ordinary annuity. C = Cash flow per period i = interest rate n = number of payments. (1 – (1 + i)-n ) / i is called discount factor for n periods. Thus Discounted value of n period = payment per period × discount factor for n period The formula provides us with the PV in a few easy steps. Here is the calculation of the annuity represented in the diagram for Example 2: = $1000*[4.33] = $4329.48 NOTATIONS The following notations are used in calculations of Annuity: R = Amount of annuity N = Number of payments I = Interest rater per conversion period S = Accumulated value A = Discounted or present worth of an annuity ACCUMULATED VALUE The accumulated value S of an annuity is the total payments made including the interest. The formula for Accumulated Value S is as follows: S = r ((1+i)^n – 1)/i Accumulation factor for n payments = ((1 + i)^n – 1) / i It may be seen that: Accumulated value = Payment per period x Accumulation factor for n payments