### Create a StudySoup account

#### Be part of our community, it's free to join!

Already have a StudySoup account? Login here

# Business-Mathematics-Statistics-MTH302

### View Full Document

## 9

## 0

## Popular in Course

## Popular in Accounting

This page Document was uploaded by an elite notetaker on Friday December 18, 2015. The Document belongs to a course at a university taught by a professor in Fall. Since its upload, it has received 9 views.

## Popular in Accounting

## Reviews for Business-Mathematics-Statistics-MTH302

### What is Karma?

#### Karma is the currency of StudySoup.

#### You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 12/18/15

Business Mathematics amp Statistics MTH 302 Business Mathematics amp 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 38 Lesson 6 APPLICATION OF BASIC MATHEMATICS 47 Lesson 7 APPLICATION OF BASIC MATHEMATICS 56 Lesson 8 COMPOUND INTEREST 64 Lesson 9 COMPOUND INTEREST 71 Lesson 10MATRICES 75 Lesson 11 MATRICES 80 Lesson 12 RATIO AND PROPORTION 89 Lesson 13 MATHEMATICS OF MERCHANDISING 93 Lesson 14 MATHEMATICS OF MERCHANDISING 97 Lesson 15 MATHEMATICS OF MERCHANDISING 102 Lesson 16 MATHEMATICS OF MERCHANDISING 111 Lesson 17 MATHEMATICS FINANCIAL MATHEMATICS 115 Lesson 18 MATHEMATICS FINANCIAL MATHEMATICS 117 Lesson 19 PERFORM BREAKEVEN ANALYSIS 124 Lesson 20 PERFORM BREAKEVEN ANALYSIS 132 Lesson 21 PERFORM LINEAR COSTVOLUME PROFIT AND BREAKEVEN ANALYSIS 135 Lesson 22 PERFORM LINEAR COSTVOLUME PROFIT AND BREAKEVEN ANALYSIS 138 Lesson 23 STATISTICAL DATA REPRESENTATION 145 Lesson 24 STATISTICAL REPRESENTATION 149 Lesson 25 STATISTICAL REPRESENTATION 154 Lesson 26 STATISTICAL REPRESENTATION 163 Lesson 27 STATISTICAL REPRESENTATION 171 Lesson 28 MEASURES OF DISPERSION 181 Lesson 29 MEASURES OF DISPERSION 186 Lesson 30 MEASURE OF DISPERASION 194 Lesson 31 LINE FITTING 201 Lesson 32 TIME SERIES AND 212 Lesson 33 TIME SERIES AND EXPONENTIAL SMOOTHING 224 Lesson 34 FACTORIALS 231 Lesson 35 COMBINATIONS 238 Lesson 36 ELEMENTARY PROBABILITY 243 Lesson 37PATTERNS OF PROBABILITY BINOMIAL POISSON AND NORMAL DISTRIBUTIONS 246 Lesson 38PATTERNS OF PROBABILITY BINOMIAL POISSON AND NORMAL DISTRIBUTIONS Lesson 41 Lesson 42 Lesson 43 Lesson 44 Lesson 45 251 Lesson 39PATTERNS OF PROBABILITY BINOMIAL POISSON AND NORMAL DISTRIBUTIONS 258 Lesson 40PATTERNS OF PROBABILITY BINOMIAL POISSON AND NORMAL DISTRIBUTIONS 262 ESTIMATING FROM SAMPLES INFERENCE 268 ESTIMATING FROM SAMPLE INFERENCE 274 HYPOTHESIS TESTING CHISQUARE DISTRIBUTION 276 HYPOTHESIS TESTING CHISQUARE DISTRIBUTION 279 PLANNING PRODUCTION LEVELS LINEAR PROGRAMMING 286 2 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 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 PhD 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 Obiective 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 ratioproportion and graphing Applications include payroll costvolumeprofit 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 ResourcesITestsIA siqnments Successful completion of the following units will enable the student to apply mathematical methods to business problems solving Required Student Resources lncludinq textbook nd 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 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU COURSE MODULES The following are the main modules of this course Module 1 0 Overview Lecture 1 0 Perform arithmetic operations in their proper order Lecture 2 0 Convert fractions their percent and decimal equivalents Lecture 2 o Solve for any one of percent portion or base given the other two quantities Lecture 2 0 Using Microsoft Excel Lecture 2 Calculate the gross earnings of employees paid a salary an hourly wage or commissions Lecture 3 0 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 0 Simple and compound interest Lecture 6 0 Average due date interest on drawings and calendar Lecture 6 Module 2 o Exponents and radicals Lecture 7 o Solve linear equations in one variable Lecture 7 o Rearrange formulas to solve for any of its contained variables Lecture 7 o Solve problems involving a series of compounding percent changes Lecture 8 0 Calculate returns from investments Lecture 8 0 Calculate a single percent change equivalent to a series of percent changes Lecture 8 o Matrices Lecture 9 o Ratios and Proportions Lecture10 0 Set up and manipulate ratios Lecture11 o Allocate an amount on a prorata basis using proportions Lecture11 0 Assignment Module 12 Module 3 0 Discounts Lectures 12 0 Mathematics of Merchandising Lectures 1316 Module 4 0 Applications of Linear Equations Lecture 1718 0 Breakeven Analysis Lecture 1922 0 Assignment Module 34 0 MidTerm Examination Module 5 0 Statistical data Lectures 23 0 Measures of central tendency Lectures 2425 0 Measures of dispersion and skewness Lectures 2627 Module 6 0 Correlation Lectures 2829 0 Line Fitting Lectures 3031 0 Time Series and Exponential Smoothing Lectures 3133 0 Assignment Module 56 Module 7 o Factorials Lecture 34 o Permutations and Combinations Lecture 34 0 Elementary Probability Lectures 3536 0 Patterns of probability Binomial Poisson and Normal Distributions Lecture 3740 Module 8 0 Estimating from Samples Inference Lectures 4142 0 Hypothesis testing ChiSquare Distribution Lectures 4344 0 Planning Production Levels Linear Programming Lecture 45 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 0 Assignment Module 78 0 EndTerm Examination Note The course modules are subject to change MRKING SCHEME As per VU Rules DESCRIPTION OF TOPICS LECTURE RECOMMENDED NO MAIN TOPIC TOPICS READING 1 10 Module Applications of o Overviewew Lecture 1 Reference 1 1 Basic Mathematics Lectures 16 Module 2 39 c urs overview c2e 2 1 o Arithmetic Operations amp TOO Microsoft 0 Using Microsoft Excel Exce39l 3 Reference 2 Module 0 Calculate Gross Earnings Lecture 3 1 0 Using Microsoft Excel Tool Microsoft Excel 4 Reference 2 Module 0 Calculating simple or Lecture 4 1 weighted averages Tool Microsoft 0 Using Microsoft Excel Excel Reference 6 5 Reference 2 o BaSIC calculations of Lecture 5 Module percentages averages commission Reference 3 Ch 3 1 brokerage and discount using Too Micros oft 0 Microsoft Excel Exce39l 6 Reference 2 0 Simple and compound Lecture 6 Module interest Reference 3 Ch 3 1 0 Average due date interest on drawings and calendar Tool Microsoft Excel 7 o Exponents and radicals 2 o o Simplify algebraic 51332 2 Module Applications of exPress39ons Reference 3 Ch 2 2 Basic Algebra SOlve near equat39ons m one Tool Microsoft Lectures 79 var39able Excel 0 Rearrange formulas to solve for any of its contained variables Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 30 Applications Module of Ratio and 2 Proportion Lectures 10 11 Module 2 40 Merchandising Module and Financial 3 Mathematics Lectures 12 16 Module 3 Module 3 Module 3 10 11 12 13 14 15 0 Calculate returns from investments 0 Problems involving a series of compounding percent changes 0 Single percent change equivalent to a series of percent changes 0 Matrices 0 Set up and manipulate ratios 0 Set up and solve proportions 0 Express percent differences using proportions o Allocate an amount on a prorata basis using proportions 0 Set up and manipulate ratios 0 Allocate an amount on a prorata basis using proportions 0 Calculate the net price of an item after single or multiple trade discounts 0 Calculate an equivalent single discount rate given a series of discounts o Solve merchandising pricing problems involving markup and markdown 0 Financial Mathematics Part 1 0 Financial Mathematics Part 2 Reference 2 Lecture 8 Reference 3 Ch 3 Tool Microsoft Excel Reference 2 Lecture 9 Reference 3 Ch 4 Tool Microsoft Excel Reference 2 Lecture 10 Reference 3 Ch 3 Tool Microsoft Excel Reference 2 Lecture 11 Reference 3 Ch 3 Tool Microsoft Excel Reference 2 Lecture 12 Reference 3 Ch 3 Tool Microsoft Excel Reference 2 Lecture 13 Reference 3 Ch 3 Tool Microsoft Excel Reference 2 Lecture 14 Reference 3 Ch 3 Reference 5 Ch 16 Tool Microsoft Excel Reference 2 Lecture 15 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Module 50 BreakEven Module Analysis 4 Lectures 17 22 Module Module Module Module Module 4 6 Statistical Module Representation 5 of Data Lectures 23 16 17 18 19 20 21 22 23 0 Financial Mathematics Part 3 0 Graph a linear equation in two variables 0 Solve two linear equations with two unknowns 0 Perform linear costvolume profit and breakeven analysis 0 Using a breakeven chart 0 Perform linear costvolume profit and breakeven analysis 0 Using the algebraic approach of solving the cost and revenue func ons 0 Perform linear costvolume profit and breakeven analysis 0 Using the contribution margin approach 0 Perform linear costvolume profit and breakeven analysis 0 Using Microsoft Excel 0 Assignment Module 34 0 MidTerm Examination 0 Statistical Data Reference 3 Ch 3 Reference 5 Ch 16 Tool Microsoft Excel Reference 2 Lecture 16 Reference 3 Ch 3 Reference 5 Ch 16 Tool Microsoft Excel Reference 2 Lecture 17 Reference 3 Ch 3 Reference 5 Ch 16 amp 18 Tool Microsoft Excel Reference 2 Lecture 18 Reference 3 Ch 2 Reference 5 Ch 1 Tool Microsoft Excel Reference 2 Lecture 19 Tool Microsoft Excel Reference 2 Lecture 20 Tool Microsoft Excel Reference 2 Lecture 21 Tool Microsoft Excel Reference 2 Lecture 22 Tool Microsoft Excel Reference 2 Lecture 23 Reference 5 Ch 5 Tool Microsoft Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 27 Module Module 5 Module 5 Module 5 7 Correlation Time Series and Module Exponential 6 Smoothing Lectures 28 33 24 25 26 27 28 29 30 0 Statistical Representation Measures of Central Tendency Part 1 0 Statistical Representation 0 Measures of Central Tendency Part 2 0 Measures of Dispersion and Skewness Part 1 0 Measures of Dispersion and Skewness Part 2 0 Correlation Part 1 0 Correlation Part 2 0 Line Fitting Part 1 Excel Reference 2 Lecture 24 Reference 4 Ch 3 Reference 5 Ch 6 Tool Microsoft Excel Reference 2 Lecture 25 Reference 4 Ch 3 Reference 5 Ch 6 Tool Microsoft Excel Reference 2 Lecture 26 Reference 4 Ch 4 Reference 5 Ch 6 Tool Microsoft Excel Reference 2 Lecture 27 Reference 4 Ch 4 Reference 5 Ch 6 Tool Microsoft Excel Reference 2 Lecture 28 Reference 5 Ch 13 Tool Microsoft Excel Reference 2 Lecture 29 Reference 5 Ch 13 Tool Microsoft Excel Reference 2 Lecture 30 Reference 5 Ch 14 Tool Microsoft Excel Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 7 Elementary Probability Lectures 34 38 Module 7 Module 7 Module 7 Module 7 Module 7 31 32 33 34 35 36 37 38 0 Line Fitting Part 2 Time Series and Exponential Smoothing Part 1 Time Series and Exponential Smoothing Part 2 0 Assignment Module 56 0 Factorials o Permutations and Combinations 0 Elementary Probability Part 1 0 Elementary Probability Part 2 0 Patterns of probability Binomial Poisson and Normal Distributions Part 1 0 Patterns of probability Binomial Poisson and Normal Distributions Part 2 Reference 2 Lecture 31 Tool Microsoft Excel Reference 2 Lecture 32 Reference 5 Ch 15 Tool Microsoft Excel Reference 2 Lecture 33 Reference 5 Ch 15 Tool Microsoft Excel Reference 2 Lecture 34 Reference 3 Ch 2 Tool Microsoft Excel Reference 2 Lecture 35 Reference 5 Ch 8 Tool Microsoft Excel Reference 2 Lecture 36 Reference 5 Ch 8 Tool Microsoft Excel Reference 2 Lecture 39 Reference 5 Ch 9 Tool Microsoft Excel Reference 2 Lecture 40 Reference 5 Ch 9 Tool Microsoft Excel Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Module 7 Module 7 8 Probability Distributions Lectures 39 Module 44 8 9 Linear Programming Lecture 45 Module 8 Module 8 Module 8 Module 8 Methodology 39 40 41 42 43 44 45 0 Patterns of probability Binomial Poisson and Normal Distributions Part 3 0 Patterns of probability Binomial Poisson and Normal Distributions Part 4 0 Estimating from Samples Inference Part 1 0 Estimating from Samples Inference Part 2 o Hypothesis testing Chi Square Distribution Part 1 o Hypothesis testing Chi Square Distribution Part 2 0 Production Planning Linear Programming 0 Assignment Module 78 0 End Term Examination Reference 2 Lecture 41 Reference 5 Ch 9 Tool Microsoft Excel Reference 2 Lecture 41 Reference 5 Ch 9 Tool Microsoft Excel Reference 2 Lecture 42 Reference 5 Ch 10 Tool Microsoft Excel Reference 2 Lecture 43 Reference 5 Ch 10 Tool Microsoft Excel Reference 2 Lecture 44 Reference 5 Ch 11 Tool Microsoft Excel Reference 2 Lecture 45 Reference 5 Ch 11 Tool Microsoft Excel Reference 2 Lecture 45 Reference 5 Ch 18 Tool Microsoft Excel 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 10 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU provide lecture and other supporting material from the course to the students The page will have a link to a webbased 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 Grading There will be a term exam and one final examination There will also be 4 assignments each covering two modules 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 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 Instructor s Power Point Slides Business Mathematics amp Statistics by Prof Miraj Din Mirza Elements of statistics amp Probability by Shahid Jamal Quantitative Approaches in Business studies by Clare Morris 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 Business Mathematics amp Statistics MTH 302 VU LECTURE 2 Applications of Basic Mathematics Part 1 OBJECTIVES The objectives of the lecture are to learn about 0 Different course modules 0 Basic Arithmetic Operations 0 Starting Microsoft MS Excel 0 Using MS Excel to carry out arithmetic operations COURSE MODULES This course comprises 8 modules as under 0 Modules 14 Mathematics 0 Modules 58 Statistics Details of modules are given in handout for lecture 01 BASIC ARITHME TIC OPERA TIONS Five arithmetic operations provide the foundation for all mathematical operations These are 0 Addition 0 Subtraction o Multiplication 0 Division 0 Exponents Example Addition 12 5 17 mple Subtraction 12 5 7 mple Multiplication 12 x 5 60 mple Exponent 4quot2 16 4quot12 2 4quot12 14quot12 12 05 MICROSOFT EXCEL IN BUSINESS MATHEMATICS amp STATISTICS Microsoft Corporation s Spreadsheet software Excel is widely used in business mathematics and statistical applications The latest version of this software is EXCEL 2002 XP 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 12 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Starting EXCEL 2000 XP EXCEL 2000 XP can be started by going through the following steps 1 Click Start on your computer 2 Click All Programs 3 Click Microsoft Excel The following slides show the operations 72 1 1H El New CIFHEE Dccumenl a span errica summt r Set Fragram assess and Defaults Winches Catalan I 39J quottquot l FLaz39gsle Elm quot Dr Eahir Filtri g Windows Update internal m 39II lrll r l ll Isaquot65 b i L necessaries s EImail Haj 5 H a mu L Li ll39icrussftlili39 ce Tools I F39I39lntl39a39le Il39izerTnetF39rIrIUng r A V g l E risisresefl Ward markup r I l g samba ReadarEEI i risisresefl Excel a in Internet Explorer l39icrJsefl Fluxess u 39 Vi quotL E titreseft PewerPein lr39itrlzlstlft Dutluzis il39ilcrJsJH Ward titreseft Ff l39ltF39EQE nruanhigu I Rm m nEEELanEE Msresefl Frm39izF39age aslehe Header ELI lF39licrJsJFI F39Iznlaim39F39Uint WEN Explurer Monk Esmess Remste Assetance E Windews Media Flare hilin I39al39lessenness All Pruner1 The EXCEL window opens and a blank worksheet becomes available as shown below Copyright Virtual University of Pakistan 13 Business Mathematics amp Statistics MTH 302 VU a Eile Edit matii mart an39nat Loni gala window nal r is u Stairi133 await f rarerft e f vttaial ruinimam r i Illi39iEi 11 a B E ml 16335315 hair 1 a a E 393 I E F E H IT HE 39li39fnirkhnk 1quot 1 A penawmltibnnl 2 EDIEBHEEDEEI i Lectui e i tnuntlnbemst iriihmetitQuarantine E LecturesM T 3 Micre workbooks E finial g D Eilank Wizuiudli 3 HEW Emmi twisting illmikimnlit 11 gihnusaworltbnnltn 339 E Ht Emmi ttmplah H E General iempilates 1539 I empilataz on my WEE Sites 1E n Templates on Iiiitrosoftacnrn 1 IE 1E 2 a I Add histtrorlt aoan E MEEI HIEUFIZ Excel Help quotquot39 Hi Show at startup H i it H Kalieett it SheetE ESheet I I i H i 39i l 39 riJ 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 which is a rectangle 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 digit to be 14 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU changed or move the cursor to the desired position Enter the new value and delete the undesired value by using the Del key 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 1 Arithmetic operators 2 Comparison operators 3 Text concatenation operator 4 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 1 Addition Symbol Example 54 Result 9 2 Subtraction Symbol Example 54 Result 1 3 Multiplication Symbol Example 54 Result 20 4 Division Symbol Example 124 Result 3 5 Percent Symbol Example 20 Result 02 6 Exponentiation quot Example 5quot2 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 ln 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 ln 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 A15B15 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 MTH302lec 02 contains the actual entries 15 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrnsu Excel rithmetic peretiuns Eile Edit ew insert Fgrmat Innls gate indnw elp Fidelge F39DF 539 X mess sin 139 st serial znvu tEamp vt La E s as 1L quot F C15 A15El E C D E F J H L 2 AD DI Tw u e E n d 5 2 Enter1 in sell 315 E 3 Write sign in Cell 2115 139 4 Place meuse en eell A15 8 type sign 9 1 Ge te sell 315 1D Press Enter key 11 Result Shewn in Cell 15 12 Cilielt en Cell 3115 13 Result Fermuls A15B15 is shewn in fermuls leer 14 10 15 1 H 11 r H Sheetl rf SheetE Jr Sheet3 f l1 l l l l 0an s eutn hapesr s a are 41 e evsveEs e u t Ready 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 51015203040 The answer was 120 You can use an Excel function SUM along with the cell range A34F34 to calculate the sum of the above numbers The formula in such a case will be SUMA34F34 You enter followed by SUM followed by Click on the cell with value 5reference A34 Drag the mouse to cell with value 40reference F34 and drop the mouse Enter and then press the Enter key 16 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrusuft Excel Arithmetic peratiuns Haj Elle Edit ew lnsert Fgrmat Tools gate indew Help Adobe PDF v 539 x 3333 Girl Ev fnrial vluvnggg gfpviv f La 3 93 TL quot 5 7 J33 139 5 A El I3 D E F I3 H l J I la L T 19 ADDI SI U M B E 51015203040 2 STEPS 21 quotI Enter 5 in eell 1134 quotI quotI Type in eell E34 er ferlnul lmr 22 2 Enter quotIIIJIin cell 334 quotI2 Click en eell C34 23 3 Enter quotI5 in eell 34 quotI3 Type in eell 4334 er ferlnuln lmr 24 4 Enter 20 in eell 034 quotI4 Cliek en eell I34 25 5 Enter 30 in eell E34 quotI5 Type in eell II334 er ferlnuln lmr 23 13 Enter 40 in eell F34 quotI3 Cliek en eell E34 2 3 Enter in eell II334 quotI Type in eell II334 er ferlnuln lmr 23 3 Cliek en eell 1134 quotI3 Cliek en eell F34 23 3 Type in eell G34 er fernnlln lmr quotI3 Press Enter 3 1 Cliek on cell 334 Reeult In eell G34 31 32 E32 ttltl 5quotI0quotI52l3l4l in a rew 5quotIlquotI52l3l4llquotl2l 33 31 5 10 15 20 30 40 120 a v H 1 r H Eheetlgquot SheetE af Sheet3 I I 1 l t l l Dtaw39 It emshapesv 4 a De Al 2 v v v e e L Ready 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 A50B50 To write this formula click cell 050 where you want the result Enter Click on cell with value 25 referenceA50 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 Business Mathematics amp Statistics MTH 302 VU I M39i erese39ft Exeel a rithme tieFl lperetiens l Elie Eli Eiew Drusth Femat Iesle gets Help de e PDF v E39 K Egg ewe E rmer w v g hi f39 3 LE El 1 l w es v 5 e5eese1 39A e39e l39Fnrmula arle39F s39HquotIf4 l L 5 SUETRACTlING TWO NUMBERS 25 ml 15 ee e1 ST EFEL 412 1 Enter 25 in cell MI 5 Twpe in cell 1251 er ferrnule her we 2 Enter 15 in eel see 6 Click en sell 5511 are 3 Write 1 in cell C251 1quot Press Enter key as 4 Elise en eel Ar ti Result in eel 1511 are Lat es w 25 15 1 quot 51 quot HI 1 r Irll x lieet39lSheetEShee133 r l iil 39ll Dieter lli g ameheme N a El 1 2 E3 v 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 I Microsoft Excel Arithmetic peratinrls IE Elle Edit ew lnsert Fgrmat sols gate window Help Fieler PDF v 539 X BEECH ush Ev i erial 7257g qgamp 7f La 2 as 7 TL quot a t CED v e eensee I A E e Formula Bar E F s H I J K LT 51 LTIPLYING TWLL N BER anquot 1 52 53 54 sTEPs 55 1 Enter 25 in cell 5 Type in cell 325121 er f39errnuls bar 55 2 Enter 15 in cell Cilielt en sell 351 5 393 Write in eell 1 Press Enter key 55 4 Cilielt en eell A5121 Result In eell 325121 59 g 25 1 5 E1 52 53 5x1 lflql r H Sheetl Sheet2 SheetS f l4 l 39 l l Drawquot ts eetDShapesr In llC339 Al 2 E a v viv E l3 L 7 Ready 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 A50BSO 18 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 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 Excel Formula for Division The formula for division is similar to that of multiplication with the difference that the division sign I will be used Hicrusuft Excel Arithmetic pera uns Elle Edit Eiew insert Fgrmat Innls Qatar indnw elp Adobe PDF v 539 X Digger Urnw Eva eerieI vzevnggettf vavt La 2 9 i E 5 T ere v e erererel A E C Formula Bar E F G H J K T 35 DIVIDING A NUMBER 240 BY 15 E STEPB ES 1 Enter 240 in eell ATE 5 Type f in cell CITE erf ermule leer ES 2 Enter 15 in cell 3T5 Cilielt en cell 375 TI 3 Write in cell SITE 7 Preee Enter key 71 4 Ellielt en eell A75 Reeult In eell CITE 72 73 74 e 240 15 H 4 Ir hi5heet15heet25heet3f lil ll Dtaw it eutn hapesr DC Al 33 3E ghi39 39a E 393 L T 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 A75B75 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 20 in cell A99 ln cell B99 enter Click on cell A99 Enter Press Enter key The answer 02 is given in cell B99 19 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrnsu Excel Arithmetic pera uns Elle Edit Eiew insert Fgrmat Innls gate Window Help 5 D l g v 3 v QETELEL q mmv jv Mal 10BIH E 33 3 iii 393quot E1IIII v 15 A E I n E F 3 H en NVERTING PER ENT FRA vTlAN 91 92 93 STEP 3 91 391 Enter EU in cell 95 2 Write in cell BE 3 Click on cell 9 4 Press Enter key 98 5 Write 09quot Result cell 99 20 02 Excel Formula for Exponentiation The symbol for exponentiation is A The formula for calculating exponents is similar to multiplication with the difference that the carat symbol quot 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 referenceA85 Enter Select number 2 reference B85 Press Enter key The result 256 is displayed in cell C85 20 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrnsn Excel Arithmetic pera uns Eile Edit Eiew insert Fgrmat leels gate indew Help Helene PDF v 539 X Digest cm Ev emal vzevng tthv v La El ea 7 39L n I CBS v 5 BEHElBEI A E e Formula ar E F e H I J K 7 r CAL ULATING TO THE FE re eTEPe El 1 Enter in eell 5 Type in eell erf ern iule leer 31 2 Enter in eell Click en eell 82 393 Write in cell Trquot Preee Enter key ES 4 Click en eell Fteeult In eell 84 as 16 BE 8 EB 89 BI 91 T H 4 Ir H t SheetlffSheetEe Sl eet3a39f 4l ll DLBWquot e eutu hapesv a a IZICEJ Al 22 E ev r v E Q L Recommended Homework Download worksheet MTH302 lec 02xls from the course web site 0 Change values to see change in results 0 Set up new worksheets for each Excel operator with different values 0 Set up worksheets with combinations of operations 21 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 3 Applications of Basic Mathematics Part 2 OBJECTIVES The objectives of the lecture are to learn about 0 Evaluations 0 Calculate Gross Earnings 0 Using Microsoft Excel Evaluation In order to successfully complete this course the student is required to meet the evaluation criteria 0 Evaluation Criterion 1 0 Full participation is expected for this course 0 Evaluation Criterion 2 o All assignments must be completed by the closing date 0 Evaluation Criterion 3 0 Overall grade will be based on VU existing Grading Rules 0 Evaluation Criterion 4 o 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 4Assignments 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 0 No copying o No cheating o No short cuts 22 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 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 webbased 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 PROBLEMS If you have any problems with understanding of the course please contact bizmathvu edu pk GROSS EARNINGS There may be three types of employees in a company 0 Regular employees drawing a monthly salary 0 Part time employees paid on hourly basis 0 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 remuneration can include the following 0 Salary 0 Provident Fund 0 Gratuity Fund 0 Social Charges SALARY Gross salary includes the following 0 Basic salary o Allowances o Provident Fund 0 Gratuity 0 Social Charges Gross salary includes 0 Basic salary 0 House Rent 0 Conveyance allowance 0 Utilities allowance 23 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 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 10000 Rs Allowances 5000 Rs What is the taxable income of employee Is any add back to the income of the company Allowances 500010000 x 100 50 Hence allowances are not taxable Total taxable income 10000 Rs Add back to the income of the company 0 Example 2 The salary of an employee is as follows Basic salary 10000 Rs Allowances 7000 Rs What is the taxable income of employee Is any add back to the income of the company Allowances 700010000 x 100 70 Allowed nontaxable allowances 50 05 x 10000 5000 Rs Taxable allowances 70 50 7000 5000 2000 Rs Hence 2000 Rs of allowances are taxable Total taxable income 10000 2000 12000 Rs Add back to the income of the company 20 allowances 2000 Rs Structure of Allowances The common structure of allowances is as under 0 House Rent 45 o Conveyance allowance 25 0 Utilities allowance 25 Example 3 The salary of an employee is as follows Basic salary 10000 Rs Allowances 5000 Rs What is the amount of allowances if House Rent 45 Conveyance allowance 25 and Utilities allowance 25 House rent allowances 045 x 10000 4500 Rs Conveyance allowance 0025 x 10000 250 Rs Utilities allowance 0025 x 10000 250 Rs Provident Fund According to local laws a com any can establish a Provident Trust Fund for the benefit of the employees By law 111 h of Basic Salary per month is deducted by the company from the gross earnings of the employee An equal amount ie 111th 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 211th 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 defence 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 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Example 4 The salary of an employee is as follows Basic salary 10000 Rs Allowances 5000 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 111 x 10000 9091 Rs Company contribution to Provident Fund 111 x 10000 9091 Rs Total savings of employee in Provident Fund 9091 9091 18182 Rs Gratuity Fund According to local laws a company can establish a Gratuity Trust Fund for the benefit of the employees By law 111th of Basic Salary per month is contributed by the company to the Gratuity Fund to the account of the employee Thus there is a saving of 111th 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 10000 Rs Allowances 5000 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 111 x 10000 9091 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 0 Casual Leave 18 Days 0 Earned Leave 18 Days 0 Sick Leave 12 Days Example 6 The salary of an employee is as follows Basic salary 10000 Rs Allowances 5000 Rs What is the cost on account of casual earned and sick leaves per year if normal working days per month is 22 What are leaves as percent of gross salary Gross salary 10000 5000 15000 Rs Casual leaves 1822 x 15000 122727 Rs Earned leaves 1822 x 15000 122727 Rs Sick leaves 1222 x 15000 81818 Rs Total cost of leaves per year 122727 122727 81818 327273 Rs Total leaves as percent of gross salary 32727312 x 15000x 100 182 Social Charqes Social charges comprise leaves group insurance and medical Typical medicalgroup insurance is about 5 of gross salary Other social benefits may include contribution to 25 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 employees children s education club membership leave fare assistance etc Such benefits may be about 58 Total social charges may therefore may be 182 5 58 29 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 10000 Rs Allowances 5000 Rs What is the cost of the company on account of leaves 182 group insurancemedical 5 and other social benefits58 Leaves cost 0182 x 15000 2730 Rs Group insurancemedical 005 x 15000 750 Rs Other social benefits 0058 x 15000 870 Rs Total social charges 2730 750 870 4350 Rs Gross Earninqs Summary of different components of salary is as follows Basic salary 100 Allowances 50 Gratuity 999 Provident Fund 999 Social Charges 29 Example 8 The salary of an employee is as follows Basic salary 6000 Rs The calculations are shown in the slide below Hicrosoft Excel H39IHEDIlecDE 31 Elle Edit iew insert Formal Tools gate window Help D g y Sfn gzv l l q lmoe Firial 1ov BIH E 1 g i iig quot3quot J15 v r A El G D E F G H 2 EXAMPLE RY 3 Eaeicealaly 55155 4 Houee Ftent Allowance 545 255151 5 Conveyance allowance 255 1551 E Utlitiee Allowance 255 1551 7 TotalAllowancee 155 55155 E Provident Fund 555 555 5 wn contribution 555 555 1 Gratuity fund 555 555 11 Earned Leave 15 daye 5554 12 Gaeual leave 15 daye 5554 13 Sick leave 12 daye 45515 14 Group Illes39iiIedical 59quot 35151 Eldiecoocial Ghargee 555quot 52 1E TotalAllowancee 35155 17r Groee ealaly 551515 18 Provident Fund 3 555 15 Gratuity Fund 555 El Leavee 15555 21 thereocial Ghargee 522 22 Total Social Ghargee 252455 23 Gl39oeerenumeration 5555 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 Percent from Frgction Calculate by multiplying fraction by 100 Percent Fraction X 100 Example 9 Convert 01 to 01 X100 10 Common Frgction Example 10 12 05 101000 1 Common Fraction 10100110 Converting into Common Fraction Example 11 20 20100 02 Percent Percent or Fraction Earnings 20 or 2010002 Base and Rate Percent of the Base Example 12 20 of 120 In 20 of 120 120 is Base 20 is Rate Percentage Percentage Base x Rate Example 13 20 x 120 20100 x 120 Or 02 X 120 24 Example 14 VU 27 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 What Percentage is 6 of 40 Percentage Rate X Base 006 X 40 24 Base Base PercentageRate Example 15 Rate 240 Percentage 96 Base 960 24400 Copyright Virtual University of Pakistan VU 28 Business Mathematics amp Statistics MTH 302 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 basic salary is 45 of basic salary Conveyance and Ultilities Allowance are both 25 of basic salary Both Gratuity and Provident fund are 111th of basic salary The arithmetic formulas are as follows Excel forluas are within brackets Basic salary 6000 Rs House rent 045 x 6000 2700 Rs Excel formula B93045 Conveyance Allowance 0025 x 6000 150 Rs Excel formula B930025 Utilities allowance 0025 x 6000 150 Rs Excel formula B930025 Gross salary 6000 2700 150 150 9000 Rs Excel formula SUM893BQ6 Gratuity 111 x 6000 545 Excel formula ROUND111B930 Hicmsuft Excel lldding umbemeamplesExl Eile Edit ew insert Fgrmal Innis Qatar indnw elp Adobe PDF ViEWFDFITIUiEIE iti v v Ev f a a a SLIM v J 3 B93045 E I I D a 92 93 Basic salary i 6000 1740 94 Huge Rent B93045 1130 as 150 95 Utilities 150 a Ttal salary 9000 as iizratuity 99 P Fund iElI In the Excel formulas the sign is used before the row and column reference to fix the location of the cell B93 fixes the location of cell 893 29 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU quotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquot quot39 Elle Edit iew insert Fgrn39iat Innis gate indnw Help Adnlge F39DF yiewl nrmulae area m Eva TL quot 539 T 39 SLIM r X J r El93l2l025 a a r n a REMUNER iTIiEN 92 93 Basic salary 6000 S harges 1740 a Huse Rent 2700 Remun 1130 as B930025 95 Utilities 150 an Ttal salary 9000 as Gratuity as P FIJI39ICI IUD quotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquot quot Elle Edit iew insert Fgrmat Innis gate indnw elp adage F39DF inew formulae 313 KM Ev f TL quot F SUM v X a 3 3 SLMtElQEinEiEj a E C D a REMUNER iTIHN a 93 Basic salary E000 S harges 1740 9x1 Huse Rent 2700 Remun 1130 as CA 150 95 Utilities 150 Ttal salary SUM393BQE aa Gratuity 99 P Fund 545 IIIIEI 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 111B93 30 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU quotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquot quot iriew formulae Eile Edit Eiew Lnsert Fgrmet Idols gate indow elp Fider F DF v rev Ev f 1L quot I T sum 7 x d a aeunerririnreaaoi a e e e 91 REMUNER iTIN as as Basic salary E000 S harges 1740 a Huse Rent 2700 Remun 1130 95 CA 150 95 Utilities 150 a Ttal salary 9000 Gratuity RUND1i11re93uii as P Fund 545 IEIEI In the calculation for social charges the formula is 89329100 Here 29100 means 29 social charges The sign was not used here If the formula was to be copied urther then sign would be needed to fix the value of basic salary Microsoft Excel Mding umhorijamplosEal Eile Edit Eiew insert Fgrmat Iools gate window elp Adobe PDF ViEW formulae v 51 x Etta a LE1 aha 1 n a SLIM v X J f3 BBBT29I1IZIIJ A B 91 REMUNERHTIN 92 asic salary 6000 SCharges 393291100 a1 Huse Rent 2700 Remun 1130 as Ctr 150 as Utilities 150 9r Ttal salary 9000 as Gratuity 545 99 P FUI Id 31 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU r Hicrusuft Excel itddirnLNumherLExamplesExt Elle Edit Eiew insert Fgrmat Innis Qatar window Help Adobe PDF ViEWFDrl39I39ILIiEIE 139 539 X D s v Ev i trial vl vBHEETa f ii v 3 La E El as a n a as v s a a I n E j 91 a 93 Basic salary 6000 1740 94 Huse Rent 2700 Remun 1130 150 as Utilities 150 9r Ttal salary 9000 as iiratuity as F Fund inn Average Arithmetic Mean Sum N Sum Total of numbers N Number of numbers EXAMPLE 1 Numbers10 7 9 27 2 Sum 1079272 55 Numbers 5 Average 555 11 ADDING NUMBERS USING MICROSOFT EXCEL Add numbers as you type them Add all numbers in a contiguous row or column Add numbers that are not in a contiguous row or column Add numbers based on one condition Add numbers based on multiple conditions Add numbers based on criteria stored in a separate range Add numbers based on multiple conditions with the Conditional Sum Wizard Add numbers Add numbers as you type them Type 510 in a cell Result 15 See Example 2 32 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrnsnft Excel Hunl E Eile Edit iersI lnsert Fgrmet eels Qete winders elp Fidege F39DF Hi 31 IE in v E 1 IE 13 7L quot39 If SLIM r r J r 51E e I El 3 D E F 3 i 2 NUM1NUM2 3 it 5 51I E F Add all numbers in a contiguous row or column Click a cell below the column of numbers or to the right of the row of numbers Click AutoSum Press ENTER See Example 2 Microsoft Excel Bunl Eile Edit EielriI Lnsert Fgrmet Innis gate indew Help Fidege F39DF if Evil Flriel lel e E 93 TL quot F EliE 1quot f3 A E I I D E F G H F e EDDING NUMBERS USING utSum 9 ll 11 12 1D 13 15 11 isl I I 33 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 Add numbers that are not in a contiguous row or column Use the SUM function See Example 3 quotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquotquot quot Eile Edit Eiew lnsert Fgrmet Iddls gate inddaI elp Fiddlge F39DF it e 2 v El 1 SLIM 1 Xx r SLJMIIA111A12 A I E C D E F G H F e ADDING NUMBERS USING AutSum e 10 i quotquotquoti 11 I i 12 105 14 I SLIP linumberlj numberE II I 15 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 Micrusuft Excel Adding umherstmelplesEx1 Eile Edit glenI insert Fgrrnet ladle gate inddw Help Fiddlge PDF at a a m z T TL quot 5quot SUM 1quot K 39J F SUMIFE 3M2quotBuchanan39EEF EdEJII a e e n 35 aleaneran lnvice 3r Buchanan 15000 a Buchanan 9000 39 Suvama 000 an Suvama 20000 5000 SUMIFM3A42 l quotBuchananquot33 B42 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Add numbers based on multiple conditions Use the IF and SUM functions to do this task See Example 4 777777777777777777777777777777777777777777777777777777777777777777777777777777 7 3 Elle Edit Eiew insert Fgrmal Inuls Qatar induw Help adage F39DF D g fal v v 3331 El anal T10 L3 a 93 B a 5 ELM 1 5 Sum ufimruicaa fur Buchanan 29000 I A E Formula Earl 3 1 33 Baleaneran Intrice 33 Buchanan 15000 33 Buchanan 9000 33 Tiuuama 000 33 Tiuuama 20000 31 Buchanan 5000 32 a 29000 3M I Sum of invuicaa fur Buchanan quot29000 I 33915 33915 Add numbers based on criteria stored in a separate range Use the DSUM function to do this task Study DSUM Example DSUM Adds the numbers in a column of a list or database that match conditions you specify Syntax DSUMdatabasefieldcriteria Database is the range of cells that makes up the list or database Field indicates which column is used in the function Criteria is the range of cells that contains the conditions you specify DSUM EXAMPLE DSUMA4E10quotProfit A1F2 The total profit from apple trees with a height between 10 and 16 75 AVERAGE USING MICROSOFT EXCEL Calculate the average of numbers in a contiguous row or column Calculate the average of numbers not in a contiguous row or column AVERAGE Returns the average arithmetic mean of the arguments Syntax AVERAGEnumber1number2 Number1 number2 are 1 to 30 numeric arguments for which you want the average 35 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 Micrusuft Excel AddinLHumher5ExamplesEH1 Eile Edit ew insert Fgrrnat IEIEIIS Eata induw Help FadInge PDF EH g Ev l AriEll La Ea a ail 1L 39 HI 3411 1 le A E I D 55 UER GE EE 5 Data 53 I 59 F39El 2 F2 a 1 1 M II I 5 TH Hicrnsuft Excel AddinLNumherLExmplesEx1 E Eile Edit ew Lnsert Fgrmat Inclls gate window Help Adnlge F39DF using autnsum at a v E v E i 1 a I T sum 1 x J 6 AVERAGEAFEEDampE3 A a e D E F as VERHGE F NUMBERS NT CNTIGUUS W Data F3 10 F9 7 an 9 a1 82 U 33 34 VER GEM71 0m33 as Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 39 Eile Edit ew insert Fgrmat Innls gate window elp Adnlge F39DF using autnsum UEEEE Eii trial 10B it a El 9 7h quot F A35 1quot i5 A E r n E F n3 RVERAGE F NUMBERS NT CNTIGUUS N Date m 10 T9 El 31 32 I 83 31 75 85 H I F WEIGHTED AVERAGE Av1 x weight 1 Av 2 x weight 2 Av N x weight n Weights in fractions 37 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 5 Applications of Basic Mathematics Part 4 OBJECTIVES The objectives of the lecture are to learn about 0 Review of Lecture 4 0 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 Changeinitial value x 100 CALCULATION Initial value 1000 Final value 2500 Change 1500 Change 15001000 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 C4C5 The result was 1500 In cell C7 the formula for percentage change was C6C4100 The result 150 is shown in the next slide Microsoft Excel Porno ntagoJI hango l Elle Edit Eiew insert Formal Tools Qatar indow Help Atler PDF v Eva Ari3 vEIZIqE 3 El use 1 39I H CE v f3 A El I3 I D E F G I 2 3 1 sfele Mndey 1000 5 sfele Next Day 5 Increase 1500 r We Increase 150 n 1 B 38 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 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 25001000 x 100 250 Two and a half times Hicrusuft Excel MWBDZlec handuut Eile Edit ew insert Fgrmalz eels gate indnw elp 159 at E w 9 v E 19 E 139 SUN 1 K 11quot fa D13fD121IZIIZI A E E D E F e Hw many Percent is Next Dey e Sale 10 with r39efer39nce t Mndey e Sale 11 12 Mndey e sale 13 Next day39s Sale 2500 14 Next day39s sale as 15 We f Mndey e sale f t l m IE 39 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrnsnft Excel HTllii Elec handout 31 Eile Edit ew insert Fgrmat Innls gate D i 3115 95 PM iiirial vzn39fg Di 1quot 1339 1 A E C D E e Hw many Percent ix Next Day e Sale 1D with refernce t Mndey s Sale 11 12 Mndey e Sale 13 Next day39s Sale 14 Next day39s Selle ex 15 in f Mndey39e sale 113 1000 2500 250 EXAMPLE 2 In the making of dried fruit 15kg of fruit shrinks to 3 kg Find the percent change Calculation Original fruit 15 kg Final fruit 3 kg Change 315 12 change 1215x 100 80 Size was reduced by 80 F 40 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrnsnft Excel Percentagejlhange ELI Eile Edit iew insert Fgrmat Innis gate indnw Help Fidn e F39DF a 111 z 1 a 1L SLIM 1 J D21ID191EIEI 131 E C D I E F G 11 CHANGE IN WEIGHT 111 riginal fruit 15 an Final fruit 21 Change in weight 5 change 23 211 Micrnsnft Excel Percentagejihange Eile Edit ew insert Fgrmat Innls Eata indnw Help Fidn e F39DF BEEN v Evc fnrial 111011 a E El 9 TLquot 539 E211 v 5 131 E C D E F I3 111 CHANGE IN WEIGHT 18 111 riginal fruit 15 211 Final fruit 21 Change in weight 12 22 change 1 111 3 25 Calculations in Excel were done as follows Data entm Cell D19 15 Cell D20 3 Formulas 41 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Formula for change in Cell D21 D19D20 Formula for change in Cell D22 D21ID19100 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 percent change CALCULATION Original weight 3 kg Final weight 15 kg Change 153 12 change 123 X 100 400 Weight increased by 400 Hicrnsu Excel Percentagejihange Hail Eile Edit ew insert Fgrn39iat Innls Data indnw Help Adah3 F DF D gal v EE j anal 3910 a El 9 1 5 13D v a h B C n E F 23 a IN TNEIGZHT 25 as f 2 Final f a Change in 29 change 400 i Calculations in Excel were done as follows Data entm Cell D26 3 Cell D27 15 Formulas Formula for change in Cell D28 D26D27 Formula for change in Cell D29 D28ID26100 Results 42 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 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 l 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 50001 31 21 1 5000x 103 x 102x 101 5306 Rs Calculations using Excel are shown in the following slides Hicrusuft Excel Percentage l1ange Eile Edit ew insert Fgrmalz Leela gate indaw elp Adnlge F39DF iln v PM 235 7L quot 539 SLIM r K J r RDLJNDIIC3511C35ll j i a El c D E F 32 39 33 S lL lRY IN YEAR 1 END 31 35 Salary year 1 5000 35 Increase year 1 Su l jSaIary year R UNDC351C BEl ea 100Ul 39 43 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrusuft Excel PercentageJIhange lgj Eile Edit ew insert Fermet leels gete indew Help Fidelge F39DF citEr r 3 3 TL quot FF 3 SLIM v x 33 r eeumnreaarr1 CAIIII IIIIIIIJHIIJ I n E F A e 33 S L RY IN YEHR 1 END 34 33 Salary year 1 5000 Re 33 Increaee year 1 W33 33 Salary year 5150 Re 33 Increase year 13 33 33 Salary year Re 33 Increase year 1313 Salary end 1quot year RUlilli f3911165 i 42 1 I RDUHDIInumter numdigit5I I 43 Hicrusuft Excel PercentageJEhenge GEE Eile Edit ew insert Fgrmet leels gete indew Help Adege F39DF 739 Eva Ariel rm B IE 39EEI El Q l T 42 v 53 I n E F A e 33 SHLHRY IN YEHR 1 END 34 33 Salary year 1 5000 Re 33 Increaee year 1 3 33 33 Salary year 5150 Re 33 Increaee year We 33 Salary year Re 33 Increaee year 1 We 33 Salary encl f year 5306 Re g I I 33913 44 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Calculations in Excel were done as follows Data entm Cell C35 5000 Cell C36 3 Cell C38 2 Cell C40 1 Formulas Formula for salary in year 2 in Cell C37 ROUNDC351C361000 Formula for salary year 3 in Cell C39 ROUNDC351C38I1000 Formula for salary end of year 3 in Cell 039 ROUND03510391000 Results Cell C37 5150 Rs Cell C39 5253 Rs Cell D22 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 100000 at the beginning of the term how much will you have at the end of the last year Hicrusuft Excel PercentageJ39Ihange Eile Edit Eiew insert Fgrmat Innls Qatar indnw Help Adana PDF 3 r v E a 3 SLIP391 v x J a RDLJNDIICAE1CAFI1IIIIIIJ IJ A El I3 15 lNVEsZTMENT THE END iQI 45 Inveatment year 391 100000 4 Increase year 1 4 fe Value in year RUNDC4E1C4TI 49 Increaee year 1000i 5 ite39alue in year 51 Iner eaae year 10 in 52 Value in year 1010 53 lnereaee year 4 In 51 ValgeentLyear 45 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrusuft Excel Percentage l1ange Hail Eile Edit View LnSErI Fgrmat Inels Qatar indew Help Adege P39DF D g l v v Ev lf Arial 4234 La El ea 39L quot F 54 v a RDUNDEC521C53I1IIIIIIJJII a E r D E F 15 lNVEri iTMENT THE END 11F 4 45 lnyeetment year 391 100000 4 Increase year 1 4 in a Value in year 104000 49 Increase year Va 5 Value in year 51 lnereaee year 10 in 52 Value in year 1010 53 lnereaee year 4 in a lyarue end year 4 1101eleg Calculations in Excel were done as follows Data entm 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 ROUNDC461C47I1000 Formula for value in year 3 in Cell C50 ROUNDC481C491000 Formula for value in year 4 in Cell C52 ROUNDC501C51I1000 Formula for salary end of year 4 in Cell 054 ROUND05210531000 Results Cell C48 104000 Rs Cell C50 112320 Rs Cell C52 101088 Rs Cell C54 110186Rs 46 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 6 Applications of Basic Mathematics Part 5 OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 5 0 Discount 0 Simple and compound interest 0 Average due date interest on drawings and calendar REVISION LECTURE 5 A chartered bank is lowering the interest rate on its loans m 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 m 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 79 2 decrease 29 x 100 222 Increase in interest rate 97 2 decrease 27 x 100 286 The calculations in Excel are shown in the following slides DECREASE IN RATE Data entm Cell F4 9 Cell F5 7 Formulas Formula for decrease in Cell F6 F5F4 Formula for decrease in Cell F7 F6IF4100 Results Cell F6 2 Cell F5 222 INCREASE IN RATE Data entm Cell F14 7 Cell F15 9 Formulas Formula for increase in Cell F16 F15F14 Formula for increase in Cell F17 F16IF14100 Results Cell F6 2 Cell F5 222 47 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU A IrIicrnanft Excel IIITHIIIIELecIIE handnut Eile Edit Eiew Insert Fgrmat Innls Qatar indnw Help nsaaa aa Sf r SErati aS mar warning E Trtt3 a L c n E r s H 2 DECREASE IN INTEREST RATE 3 4 irriginal Interest Rate 9 A 5 Revised Interest Rate 7 IIa a Decrease It r it Decrease a E Eile Edit Eiew Insert Fgrmat Innls Qatar intlnlal elp DE IS EET S a ES Ei a i Arial vianIQEEE T 3 EIEE v 5 A E I I i E F G H 12 INCREASE IN INTEREST RATE 13 a irriginal Interest Rate 7 n 15 Revised Interest Rate 9 A 15 Increase n 1 I24 Increase A 13 48 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU BUYING SHARES If you buy 100 shares at Rs 6250 per share with a 2 commission calculate your total cost Calculation 100 Rs 6250 Rs 6250 02 Rs 6250 125 Rs 6375 RETURN 0N INVESTMENT Suppose you bought 100 shares at Rs 5225 and sold them 1 year later at Rs 68 With a 1 commission rate buying selling the stock and a current Rs 10 dividend per share in effect what was your return on investment Bought 100 shares at Rs 5225 522500 Commission at 1 5225 Total Costs 527725 Sold 100 shares at Rs 68 680000 Commission at 1 6800 Total Costs 673200 Gain Net receipts 673200 Total cost 527725 Net Gain 145475 Dividends 1001 10000 Total Gain 145475 Return on investment 145475527725100 2757 The calculations using Excel were made as follows BOUGHT Data entm Cell 321 100 Cell 322 5225 Formulas Formula for Cost of 100 shares at Rs 5225 in Cell 323 321322 Formula for Commission at 1 in Cell 324 323001 Formula for Total Costs in Cell B25 323324 Results Cell 323 5225 Cell 324 5225 Cell 325 527725 49 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Mlll l Locll handout Eile Edit Eiew insert Format Idols gate window elp D g wd lt3 gE39ElEl ddl i39ia39 iiirial v 2o v I E as w r Baainssnnd A E e a taint tit Java 2 21 100 22 Rate 23 Cstf l shares atRs 24 Cmmissin at1 25 Ttal Csts 527725 25 SOLD Data entm Cell 328 68 Formulas Formula for sale of 100 shares at Rs 68 in Cell 329 321328 Formula for Commission at 1 in Cell B30 329001 Formula for Total Sale in Cell B31 329330 Results Cell 329 6800 Cell 330 68 Cell 331 6732 Microsoft Excel MilIiillZLocll handout Eile Edit ew insert Format Idols Qatar indow elp D i g fyd 39039 Ei i d ni Mal vz vIu E 33d3 Jana as v 3 Bai3i335nnn A B i 2 23 29 100 shares at Rs 8 600 3d Cmmissin at1 E 31 Ttal Sale 32 50 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU M Formulas Formula for Net receipts in Cell B34 B31 Formula for Total cost in Cell B35 B25 Formula for Net Gain in Cell B36 B31B25 Formula for Gain in Cell B37 BB6835100 Results Cell B34 6732 Cell B35 527725 Cell B36 145475 Cell B37 2757 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 015 330 Calculation using Excel along with formula is given in the following slide Microsoft Excel M39l39lIHDZLocll handout Eile Edit iew insert Format Iools Data window olp De l g i ttt um givgl q 1oo t39 e at 3333 iii Jami D E I F 3 Ftrial vl v f g E51 fx 9393 33 DISCUNT 39 III IIIIII ti III D All Discunt Rate ito 11 Discunt Frmula 339 iB40l100 42 43 NET COST PRICE Net Cost Price List price Discount Example List price 4500 Rs Discount 20 Net cost price 51 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Net cost price 4500 20 of 4500 4 500 02 x4500 4 500 900 3600 Rs Calculation using Excel along with formula is given in the following slide Microsoft Excel M39l39lIHDELooDE handout EJ Eile Edit Eiew insert Fgrmat Iools Qatar window Help Uses em if s m ewes neme Te Final rllilv HIE EEEE39Kgi EJQE quot 39 154 139 i3 A E C D E F 44 45 NET PRICE 45 d 4 List price 4500 e 39 Discth est 1 in 49 Net Price 5 5 52 39I Frmula Cell 349 B47B47 34l100 SIMPLE INTEREST P Principal R Rate percent per annum T Time in years I Simple interest then I P R T 100 Example P Rs 500 T 4 years R 11 Find interest I Px Tx R100 500x 4 x 11100 Rs 220 Calculation using Excel along with formula is given in the following slide 52 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrusnft Excel M39I39lIHIJELecDE handuut 31 Eile Edit ew insert Fgrmat Inuls gate window Help D d nl v r Elil Hal 339 1393 HIE agfn393439i i39ii39 FEE T Ail E I D E 55 SIMPLE INTEREST 5 5E Princinal P 500 59 Time perid T 4 Year an We 51 Interest 52 53 Fr mul in Cell 361 35B59BEUI1UU Ed COMPOUND INTEREST Compound Interest also attracts interest Example P 800 Interest year 1 01 x 800 80 New P 800 80 880 Interest on 880 01 X 880 88 NewP 880 88 968 Calculation using Excel along with formula is given in the following slide 53 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrusnft Excel MTHIIIIELecIIE handnut E1 Eile Edit Eiew insert Fgrrnat Innls Qatar window Help 139 E DIE51 git i g r girtiti d nl m o39 iiirial vllilr HIE EEEE Kgidg amp39i39 HFE 139 i5 A B C D E F G H e CMPUND INTEREST ET res Principal P 00 Rs EB Interest 10 rn Interest yearI 0 Rs Frmula B t369i100 F1 New P 0 Rs Frmuls BEBYU F2 Interest n U Frmula B71tBEQI1UU F3 New P Frmula B71B72 r4 Co mpound Interest Formula S Money accrued after n years P Principal r Rate n Number of years S P1 r100 n Example Calculate interest on Rs 750 invested at 12 per annum for 8 years 8 P1r100A8 750112100A8 1957 54 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Calculation using Excel along with formula is given in the following slide Microsoft Excel MilIEDZLec handout ELI Eile Edit Eiew insert Format lools gate window elp D l g w i 39 v gzv i q mnev TA Firial TIDTBIHE iron 1 e e la c D E F F CMPUND INTEREST USING FRMUL i TS to Principal P 750 RS 35 T634513 iii 3 En Interest Bi Ferid Years 32 Mney accrued 157 RS ES Em Frmula RUNDB91BUHUU BMU as L 55 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 7 Applications of Basic Mathematics OBJECTIVES The objectives of the lecture are to learn about 0 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 amp Proportions and Index Numbers Lecture 10 Annuity Let us look at an example to understand what is annuity Suppose that you want to buy electric equipment on installments The value of the equipment is Rs 4000 The company informs you that you must pay Rs 1000 at the time of purchase down payment 1000 The rest of the payments are to be made in 20 installments of 200 rupees each You are wondering about the total number and sequence of periodic payments The sequence of payments at equal interval of time is called Annuity The time between payments is called the Time Interval NOTATIONS The following notations are used in calculations of Annuity R Amount of annuity N Number of payments 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 payment made including the interest The formula for Accumulated Value S is as follows S r 1iquotn 1i It may be seen that Accumulated value Payment x Accumulation factor The discounted or present worth of an annuity is the value in today s rupee value As an example if we deposit 100 rupees and get 110 rupees 100 x 11 after one year the Present Worth or 110 rupees will be 100 Here 110 will be future value of 100 at the end of year 1 The amount 110 if invested again can be Rs 121 after year 2 The present value of Rs 121 at the end of year 2 will also be 100 Thus the total present worth of payments made in year 1 and 2 100110 210 will be 200 The Future Value of this present worth is 210 110x11 DISCOUNT FACTOR AND DISCOUNTED VA When future value is converted into present worth the rate at which the 56 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU calculations are made is called Discount factor In the previous example 10 was used to make the calculations This rate is called Discount Rate The present worth of future payments is called Discounted Value The above example may be restated as follows The future value of Annuity in year 1 and 2 is 100 and 110 respectively The Discount Factor is 10 The Accumulation Factor after year 1 is 10010100 11 The Accumulation Factor after year 2 will be 11011100121 The Accumulation Factor can also be calculated by treating the value at the end of year 1 as 1 plus interest on 1 After year 1 the Accumulation Factor will be 10111 Here we treated 10 of 1 as 01 Obviously the Discounted Value at the beginning of year 1 can be calculated as 10111 1 Here 11109 is the Discount Factor If you multiply the Future Value or Payment in year 2 11 by the Discount Factor 09 you get the discounted value 11 x 09 1 Thus we can write down the formula for Discounted Value as follows Discounted value Payment x Discount factor The formula can be written as follows A r 1 11iquotni EXAMPLE 1 ACCUMULATION FACTOR A Calculate Accumulation Factor and Accumulated value when Discount rate i 425 Number of periods n 18 Amount of Annuity R 10000 Rs Accumulation Factor AF 1 00425quot181 2624 Accumulated Value S 10000x 2624 260240 EXAMPLE g DISCOUNTED VALUE DV In the above example calculate the value of all payments at the beginning of term of anntu Value of all payments at the beginning of term of Annuity Payment x Discount Factor DF Formula for Discount Factor 111iquotni 1110045quot80045 6595 EXAMPLE 3 ACCUMULATED VALUE S In the above example calculate the Accumulated Value S ACCUMULATED VALUE 2000 x 1110055quot80055 2000 x1195 2390077 ALGEBRAJC OPERATIONS Algebraic Expression indicates the mathematical operations to be carried out on a combination of NUMBERS and VARIABLES The components of an algebraic expression are separated by Addition and Subtraction An example is the expression in the following slide Here the components 2xquot2 3x and 1 are separated by minus sign 57 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU algebraic 3 I a Dperatinn quot 4quotquotquot39quotquot39 Irtmi l Binomial T numial39 39 sit arm 2 TEl39l39l39E a quot i a Fail l 32 Li In algebraic expressions there are four types of terms 0 Monomial ie 1 term Example 3xquot2 o Binomial ie 2 terms Example 3xquot2xy o Trinomial ie 3 terms Example 3xquot2xy6yquot2 o Polynomial ie more than 1 term Binomial and trinomial examples are also polynomial Algebraic operations in an expression consist of one or more FACTORS separated by MULTIPLICATION or DIVISION sign Multiplication is assumed when two factors are written beside each other Example xy xy Division is assumed when one factor is written under an other Example 36xA2y60xyquot2 Algebraic nth 31 I 111 E 39 f i itquotii llEl39EltiDFlS Term each neigiin an Ergmssuzrt cunsig f 112 I11 iejquot thtE Hammad n i r a 9 awn EST ugnug ug Eguunggguguuuumu nrunnn nunuqnunl u nnzimuun a I r E nr insured when two fil hrs arew thn 1 factor 39E 39 i39h39l39l 39I 39I 39I II I39393939393939 i393939 ear1 other Factors can be further subdivided into NUMERICAL and LITERAL coefficients 58 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Algebraic M iii II t If25311 i 9139 quot 39 az w t 1quot 1i ii Dperatlnns39 I it 51 granquota 13 5H D Algebraic EHpFESSi l I if a n quotHit 39 Ei i I u Faun m a n ugijugun th EDENBung Jfartrmal E 111131 Tnnumul Pugmum al L I Hanan Dunn quot a quota 2 En M u a 1 11 a a I J 1 Iquot E 1 41 FAETELS i 39i 39 Numerical Literal Ziwfzf cjgni C qf i There are two steps for Division by a monomial 1 Identify factors in the numerator and denominator 2 Cancel factors in the numerator and denominator Example 36XA2y60xyquot2 36 can be factored as 3 x 12 60 can be factored as 5 x 12 xquot2y can be factored as Xxy xyquot2 can be factored as Xyy Thus the expression is converted to 3 x 12xxy 5 x 12xyy 12xxy in both numerator and denominator cancel each other The result is 3X5y Attentijj39 Factors FatTUE 39 EIH39 i i I i 4 39 E litI iquotEI3939I i39 HIE LI i39 a 39 J e 5 Mii m i trrterr39ntm39 mati I r E E rtemtnr 11 3 E rte rant 21m fur Another example of division by a monomial is 48aquot2 32ab8a Here the steps are 59 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 1 Divide each term in the numerator by the denominator 2 Cancel factors in the numerator and denominator 4398aquot28a 8X6aa8a 6a 32ab8a 4x8ab8a 4b The answer is 6a 4b mquot by a M l39 i l iii i i H5133 Examph 13 a 5 ti F 1 39I 1 rl H 1 Minute men J Mu I I m 39e Hume m to 1 i39 39 Hiequot 45I i ELji i lli lthLjiquot up II39 quot 39392quotEr i39 39 Factors l m EHHFHEFLHLJF r J Ill1 i E L39I i i E 2 Hal LilI iquot How to multiply polynomials Look at the example x2xquot2 3x 1 Here each term in the trinomial 2xquot2 3x 1 is multiplied by x X2Xquot2 X3X X1 2xquot3 3Xquot2 x Please note that product of two negatives is positive VU 60 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 3xquot6yquot3xquot22quot3 Exponent of a term means calculating some power of that term In the following example we are required to work out exponent of 3xquot6yquot3xquot22quot3 to the power of 2 The steps in this calculation are 1 Simplify inside the brackets first 2 Square each factor 3 Simplify In the first step the expression 3xquot6yquot3xquot22quot3 is first simplified to 3xquot4yquot3zquot3 In the next step we take squares The resulting expression is 3 A2XA42y3 2z3 2 9XA8yA6ZA6 61 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Step 1 Step 2 Step 3 mp image Sg39ztczre emf1ft cter mp the bmckets mt LINEAR EQUATION If there is an expression A 9 137 how do we calculate the value of A A 137 9 128 As you see the term 9 was shifted to the right of the equality To solve linear equations 1 Collect like terms 2 Divide both sides by numerical coefficient Step 1 X 34125 0025X X 0025X 34125 X10025 34125 0975X 34125 Step 2 X 341250975 350 62 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU if 39 39 afarEl l 25 H Dwarfs bath H squot sides by H915 63 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 8 Compound Interest Calculate returns from investments Annuities ExcelFunc ons OBJECTIVES The objectives of the lecture are to learn about 0 Review of lecture 7 0 Compound Interest 0 Calculate returns from investments 0 Annuities 0 Excel Functions CUMIPMT Returns the cumulative interest paid on a loan between startperiod and endperiod If this function is not available and returns the NAME error install and load the Analysis ToolPak addin The syntax is as follows CUMIPMTratenperpvstartperiodendperiodtype Rate interest rate Nper total number of payment periods Pv present value Startperiod first period in the calculation Endperiod last period in the calculation Type timing of the payment Type Timing 0 Payment at the end of the period zero 1 Payment at the beginning of the period CUMIPMTEXAMPLE Following is an example of CUMIPMT function In this example in the first case the objective is to find total interest paid in the second year of payments for periods 13 to 24 Please note there are 12 periods per year The second case is for the first payment penod In the first formula the Annual interest rate 9 is cell A2 not shown here The Years of the loan are given in cell A3 The Present value is in cell A4 For the Start period the value 13 was entered For the End period the value 24 has been specified The value of Type is 0 which means that the payment will be at the end of the period Please note that the annual interest is first divided by 12 to arrive at monthly interest Then the Years of the loan are multiplied by 12 to get total number of months in the Term of the loan The answer is 1113523 In the second formula which gives Interest paid in a single payment in the first month 1 was specified as the Start period For the End period also the value 1 was enteredThis is because only 1 period is under study All other inputs were the same The answer is 93750 64 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Data Description 9 Annual interest rate 30 Years of the loan 125000 Present value CUMPMTA212A312A413240Total interest paid in the second year of payments periods 13 through 24 1113523 CUMPMT A212A312A4110Interest paid in a single payment in the first month 93750 CUMPRINC The CUMPRINC function returns the cumulative principal paid on a loan between two penods The syntax is as under CUMPRINCratenperpvstartperiodendperiodtype Rate interest rate Nper total number of payment periods Pv present value Startperiod period in the calculation Payment Endperiod last period in the calculation Type timing of the payment 0 or 1 as above CUMPRINC EXAMPLE Following is an example of CUMPRINC function In this example in the first case the objective is to find the total principal paid in the second year of payments periods 13 through 24 Please note there are 12 periods per year The second case is for the principal paid in a single payment in the first month In the first formula the Interest rate per annum 9 is in cell A2 not shown here The Term in years 30 is given in cell A3 The Present value is in cell A4 For the Start period the value 13 was entered For the End period the value 24 has been specified The value of Type is 0 which means that the payment will be at the end of the period Please note that the interest is first divided by 12 to arrive at monthly interest Then the years of loan are multiplied by 12 to get total number of months in the term of the loan The answer is 9341071 In the second formula which gives the principal paid in a single payment in the first month 1 was specified as the start period For the end period also the value 1 was enteredThis is because only 1 period is under study All other inputs were the same The answer is 6827827 EXAMPLE Data Description 900 Interest rate per annum 30 Term in years 125000 Present value CUMPRINCA212A312A413240The total principal paid in the second year of payments periods 13 through 24 9341071 CUMPRINCA212A312A4110The principal paid in a single payment in the first month 6827827 EFFECT Returns the effective annual interest rate As you see there are only two inputs namely the nominal interest Nominalrate and the number of compounding periods per year Npery 65 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU EFFECTnominalratenpery Nominalrate nominal interest rate Npery number of compounding periods per year EFFECTEXAMPLE Here Nominalrate 525 in cell A2 Npery 4 in cell A3 The answer is 0053543 or 53543 You should round off the value to 2 decimals 535 525 Nominal interest rate 4 Number of compounding periods per year EFFECTA2A3 Effective interest rate with the terms above 0053543 or 53543 percent FV Returns the future value of an investment There are 5 inputs namely Rate the interest rate Nper number of periods Pmt payment per period Pv present value and Type FVratenperpmtpvtype Rate interest rate per period Nper total number of payment periods Pmt payment made each period Pv present value or the lumpsum amount Type number 0 or 1 due FVEXAMPLE 1 In the formula there are 5 inputs namely Rate 6 in cell A2 as the interest rate 10 as Nper number of periods in cell A3 200 notice the minus sign as Pmt payment per period in cell A4 500 notice the minus sign as Pv present value in cell A4 and 1 as Type in cell A6 The answer is 258140 mm MEBrimim EMWJJLE l 5 Annual inten rate 391 iii H miter ef Ili j 1 i5 eee An39nlut ef the payeralt 5 P ri ent Iiinlue quotl P malt ie tlue nttl39re beguiling ef the pielied F I39utl iE 1E A3 rst A5 13 Future value ef an inveetm E tWi 39i iE ebeve term 5 2531 4101 Ftufuretenperpmtpvtype FVEXAMPLE 2 66 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 In the formula there are 3 inputs namely Rate 12 in cell A2 as the interest rate 12 as Nper number of periods in cell A3 1000 notice the minus sign as Pmt payment per period in cell A4 Pv present value and Type are not specified Both are not required as we are calculating the Future value of the investment The answer is 1268250 EXAMPLE t ie nnual intereet rate 3912 Number ef paymente 1 Ameunt ef the nayment FVMEHE Ad Future 1 lialue at an ineeetment With the aheve terme Fiifirate per nmtevtyee FVEXAMPLE 3 In the formula there are 4 inputs namely Rate 11 in cell A2 as the interest rate 35 as Nper number of periods in cell A3 2000 notice the minus sign as Pmt payment per period in cell A4 1as Type in cell A5 The value of Pv was omitted by entering a blank for the value note the double commas The answer is 8284625 EXAM PL E 11 Annual intereet rate 35 Number ef naylnente eeee Ameunt ef the payment 1 Payment ie tlue at the heuinning ef the ierietl FWAEI12 a3 A4 a5 Future value ef an investment with the ahwe terme 32 ere E FEWINGquotIETJITIL1VJF1 e VU 67 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU FV SCHEDULE Returns the future value of an initial principal after applying a series of compound interest rates FVSCHEDULEprincipal schedule Principal present value Schedule an array of interest rates to apply FV SCHEDULEEXAMPLE In this example the Principal is 1 The compound rates 009 01101 are given within curly brackets The answer is 133089 FVSCHEDULEprincipalschedule FVSCHEDULE100901101 Future value of 1 with compound interest rates of 00901101 133089 IPMT Returns the interest payment for an investment for a given period lPMTratepernperpvfvtype Rate interest rate per period Per period to find the interest Nper total number of payment periods Pv present value orthe lumpsum amount Fv future value or a cash balance Type number 0 or 1 ISPMT Calculates the interest paid during a specific period of an investment lSPMTratepernperpv Rate interest rate Pen penod Nper total number of payment periods Pv present value For a loan pv is the loan amount NOMINAL Returns the annual nominal interest rate NOM l NALeffectrate npery Effectrate effective interest rate Npery number of compounding periods per year NPER Returns the number of periods for an investment NPERrate pmt pv fv type Rate the interest rate per period Pmt payment made each period Pv present value orthe lumpsum amount Fv future value or a cash balance Type number 0 or 1 due NPV Returns the net present value of an investment based on a series of periodic cash flows and a discount rate PVratenperpmtfvtype 68 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Rate interest rate per period Nper is the total number of payment periods Pmt payment made each period Fv future value or a cash balance Type number 0 or 1 due M Returns the periodic payment for an annuity PMTratenperpvfvtype Rate interest rate Nper total number of payments Pv present value Fv future value Type number 0 zero or 1 PPMT Returns the payment on the principal for an investment for a given period PPMTratepernperpvfvtype Rate interest rate per period Per period and must be in the range 1 to nper Nper total number of payment periods Pv the present value Fv future value 0 Type the number 0 or 1 due PV Returns the present value of an investment PVratenperpmtfvtype Rate interest rate per period Nper total number of payment periods in an annuity Pmt payment made each period and cannot change over the life of the annuity Fv future value or a cash balance Type number 0 or 1 and indicates when payments are due RATE Returns the interest rate per period of an annuity RATEnperpmtpvfvtypeguess Nper total number of payment periods Pmt payment made each period Pv present value Fv future value or a cash balance 0 Type number 0 or 1 due Guess 10 RATEEXAMPLE 69 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Three inputs are specified 4 as years of loan in cell A5 200 as monthly payment in cell A6 and 8000 as amount of loan in cell A7 The answer is 00924176 or Microsoft Excel LeoturoEli5countlntoreet Eile Edit ew insert F rmat 10039s Qata window Help Ader F39DF Financial Functions v 539 H fl 133 r 74 39li i SUM quot X J i3 RATEAE12ampEMH 4 E C D E F 2 RATE RHTEnpenpmtpmfmtypeguess 3 4 Data Descriptin 5 at Years f the Ian 5 200 Mnthly payment 8000 emunt f the Ian 3 R TEA5t12 EM 1 a 0092 Annual rate f the Ian 00924176 r 924 924 10 70 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 9 Compound Interest Calculate returns from investments Annuities ExcelFunc ons OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 8 o Matrices 0 Matrix Applications using Excel QUESTIONS Every student wonders why he or she should study matrices Ther are mant important ques ons Where can we use Matrices Typical applications What is a Matrix What are Matrix operations Excel Matrix Functions There are many applications of matrices in business and industry especially where large amounts of data are processed daily TYPICAL APPLICATIONS Practical questions in modern business and economic management can be answered with the help of matrix representation in Econometrics Network Analysis Decision Networks Optimization Linear Programming Analysis of data Computer graphics WHAT IS A MATRIX A Matrix is a rectangular array of numbers The plural of matrix is matrices Matrices are usually represented with capital letters such as Matrix A B C Matrices are usually represented with capital letters Shown below are several matrices 41 1 1 El 1 52 FE SE A E I3 I 3 3 El 52 33 EB 45 5 2 The numbers in a matrix are often arranged in a meaningful way For example the order for school clothing in September is illustrated in the table as well as in the corresponding matrix 71 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU El 19 13 48 36 9 The data in the above table can be entered in the shape of a matrix as follows III ll 34 ill 12 IE 25 29 21 F IE 13 18 SE 9 2 F ll 24 H DIMENSION Dimension or Order of a Matrix Number of Rows x Number of Columns Example Matrix T has dimensions of 2x3 or the order of matrix T is 2x3 T l3 2 1 tr rnwl l III F rrIJWE f l 393 call EDIE EDIE ROW COLUMN OR SQUARE MATRIX A matrix with dimensions 1xn is referred to as a row matrix For example matrix A to the right is a 1x4 row matrix A matrix with dimensions nx1 is referred to as a column matrix For example matrix B to the right is a 2x1 column matrix A matrix with dimensions nxn is referred to as a square matrix For example matrix C to the right is a 3x3 square matrix 2 3 n12 1 ll 9 E39 C E El 1 5 n 4 m In a Row Matrix there is one row of values Example In Matrix A above the dimension is 1x4 In a Column Matrix there is one column of values Example In Matrix B above the dimension is 2x1 In a Square Matrix there is equal number of rows and columns Example In Matrix C above the dimension is 3x3 IDENTY MATRIX An identity matrix is a square matrix with 139s on the main diagonal from the upper left to the lower right and 039s off the main diagonal An identity matrix is denoted as Some examples of identity matrices are shown below The subscript indicates the size of the identity matrix For example I represents an identity matrix with dimensions nit n 72 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU GHQ HIEDI DIEDH EDI I39D MULTIPLICATIVE IDENTITY With real numbers the number 1 is referred to as a multiplicative identity because it has the unique property that the product a real number and 1 is that real number In other words 1 is called a multiplicative identity because for any real number n 139 n n and n391n With matrices the identity matrix shares the same unique property as the number 1 In other words a 2K2 identity matrix is a multiplicative 1 inverse because for any 2I12 matrix A I 39 A A and A39 I A Example 2 1 Given the 2I12 matrix A 3 4 1 L12 1 2 1 EPA 11 3 ril 3 r11 2 11 EJ 2 1 3 3 4 11 3 4 Work r1c1 12 03 2 r2c1 02 13 3 r1c1 21 10 2 r2c1 31 40 3 r1c2 11 04 1 r2c2 01 14 4 r1c2 20 11 1 r2c2 30 41 4 MULTIPLICATIVE INVERSES Real Numbers Two nonzero real numbers are multiplicative inverses of each other if their products in both orders is 1 Thus 1 1 1 1 the multiplicative inverse of a real number x is I or I since x 39 I 1 and I 39 x 1 Example 1 The multiplicative inverse of 5 is 5 since 1 1 5 51and 5 151 Matrices Two 2 2 matrices are inverses of each other if their products in both orders is a 2K2 1 1 identity matrix Thus the multiplicative inverse of a 2i 2 matrix A is A since A39 A and 1A 73 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 Example 3 2 2 1 The multiplicative inverse ofamatrix 1 4 is 5 3 since 3 2 2 1 1 1 1 4 5 3 1 1 2 1 3 2 1 1 5 3 1 4 1 1 Copyright Virtual University of Pakistan VU 74 Business Mathematics amp Statistics MTH 302 VU LECTURE 10 MATRICES OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 9 o Matrices EXAMPLE 1 An athletic clothing company manufactures Tshirts and sweat shirts in four differents sizes small medium large and xlarge The company supplies two major universities the U of R and the U of S The tables below show September39s clothing order for each university University of S39s September Clothing Order S M L XL T shirts sweat shirts 100 300 500 300 150 400 450 250 University of R39s September Clothing Order S M L XL T shirts sweat shirts 60 250 400 250 100 200 350 200 mtrix Representation The above information can be given by two matrices S and R as shown below IUD 30E EDD EDD s 15D 4GB 45E 25E ED 250 400 250 R IUD EDD 350 220 MATRIX OPERATIONS The matrix operations can be summarized as under Organize and interpret data using matrices Use matrices in business applications Add and subtract two matrices Multiply a matrix by a scalar 75 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 0 Multiply two matrices o Interpret the meaning of the elements within a product matrix PRODUCTION The clothing company production in preparation for the universities39 Septmber orders is shown by the table and corresponding matrix P below s M L XL T shirts 300 700 900 500 Sweat 300 700 900 500 shirts EDD TDD EDD EDD P EDD TDD EDD EDD ADDITION AND SUBTRACTION 0F MATRICES The sum or difference of two matrices is calculated by adding or subtracting the corresponding elements of the matrices To add or subtract matrices they must have the same dimensions PRODUCTION REQUIREMENT Since the U of S ordered 100 small Tshirts and the U of R ordered 60 then althogether 160 small Tshirts are required to supply both universities Thus to calculate the total number of Tshirts and sweat shirts required to supply both universities add the corresponding elements of the two order matrices as shown below 15D 4DD 45D 25D 1DD EDD EED 22D 16D 55D EDD 55D 1DD EDD EDD EDD 5D 25D 4DD 25D 25D EDD EDD 4ED OVERPRODUCTION Since the company produced 300 small Tshirts and the received orders for only 160 small Tshirts then the company produced 140 small Tshirts too many Thus to determine the company39s overproduction subtract the corresponding elements of the total order matrix from the production matrix as shown below EDD EDD EDD EDD 16D 55D EDD 55D 14D 15D D 5D EDD EDD EDD EDD 25D EDD EDD 4ED 5D 1DD 1DD ED MULTIPLICATION 0F MATRICES To understand the reasoning behind the definition of matrix multiplication let us consider the following example Competing Companies A and B sell juice in 591 mL 1 L and 2 L plastic bottles at prices of Rs160 Rs230 and Rs310 respectively The table below summarises the sales for the two companies during the month of July 76 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 591 mL 1L 2L 2 mpany 20000 5500 10500 g mpany 18250 7000 11000 What is total revenue of CompanyA What is total revenue of Company B Matrices may be used to illustrate the above information As shown at the right the sales can be written as a 2X3 matrix S the selling prices can be written as a column matrix P and the total revenue for each company can be expressed as a column matrix R P S 150 R 50000 5500 10500 23D 11510 15550 1000 11000 39 15500 310 Since revenue is calculated by multiplying the number of sales by the selling price the total revenue for each company is found by taking the product of the sales matrix and the price matrix 100 20000 5500 10000 T1510 25 15550 1000 11000 15500 Consider how the first row of matrix S and the single column P lead to the first entry ofR 2U mm 5 5m M 6m 150 50000115015500r550110500151015 W m 5 550 l l t 15550 1000 11000 15500 310 F39r00lL10t 0f F39r0tlL10t 0f F39r0dL10t 0f Fir5t Entries 3500110 Entries Third Entries With the above in mind we define the product of a row and a column to be the number obtained by multiplying corresponding entries first by first second by second and so on and adding the results MULTIPLICATION RULES If matrix A is a mi n matrix and matrix B is a nit p matrix then the product AB is the m p matrix whose entry in the ith row and the jth column is the product of the ith row of matrix A and the jth row of matrix B The product of a row and a column is the number obtained by multiplying corresponding elements first by first second by second and so on To multiply matrices the number of columns ofA must equal the number of rows of B MULTIPLICATION RULES Given the matrices below decide if the indicated product exists And if the product exists determine the dimensions of the product matrix 77 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 5 11 4 1112 3 111 15 1 39 4 3 11 1 5 1 394 3 1 1 1 3 5 2 111 391 1 1 1 El 3915 T E a 9 MULTIPLICATION CHECKS The table below gives a summary whether it is possible to multiply two matrices It may be noticed that the product of matrix A and matrix B is possible as the number of columns ofA are equal to the number of rows of B The product BA is not possible as the number of columns of b are not equal to rows of A Does a product exist Is it possible to multiply the given matrices in this order Dimensions of Product Matrix Dimesions of PI39OdUCt the Matrices Yes the product exists A3K3 B sincethe 3W2 inner AB T T dimensions 3 2 match inner dime na39o n5 of columns of A of rows of B B3K2 A No the 3Kl3 product BA T T does not na ex1st inner dime na39o n5 Since the inner 78 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU dimensions do not match of columns of B E of rows of A 79 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 11 MATRICES OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 10 0 Matrix functions in Excel 0 Set up and manipulate ratios 0 Allocate an amount on a prorata basis using proportions MATRIX FUNCTIONS IN MS EXCEL The Matrix Functions in Microsoft Excel are as follows MDETERM Returns the matrix determinant of an array MINVERSE Returns the matrix inverse of an array MMULT Returns the matrix product of two arrays MINVERSE Returns the inverse matrix for the matrix stored in an array Syntax MINVERSEarray Array is a numeric array with an equal number of rows and columns Remarks 0 Array can be given as a cell range such as A1 03 as an array constant such as 1 23456789 or as a name for either of these 0 If any cells in array are empty or contain text MINVERSE returns the VALUE error value 0 MINVERSE also returns the VALUE error value if array does not have an equal number of rows and columns Formulas that return arrays must be entered as array formulas Inverse matrices like determinants are generally used for solving systems of mathematical equations involving several variables The product of a matrix and its inverse is the identity matrix the square array in which the diagonal values equal 1 and all other values equal 0 0 As an example of how a tworow twocolumn matrix is calculated suppose that the range A1 82 contains the letters a b c and d that represent any four numbers The following table shows the inverse of the matrix A1 82 Column A Column B Row 1 dadbc bbcad Row 2 cbcad aadbc o MINVERSE is calculated with an accuracy of approximately 16 digits which may lead to a small numeric error when the cancellation is not complete 0 Some square matrices cannot be inverted and will return the NUM error value with MINVERSE The determinant for a noninvertable matrix is 0 80 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU MI E IM I uI E REE array Array i5 a I llII I IiEI39iiE array with an E ll l numb Er 31f rams an E llll l ll l II I39HIEI39EE air that In atriat A 132 C lllli ll l Ealurnn El Flaw 1 tla lljaL39JtI IJL39JE lili ili39JJiE i39li Flaw Emil tic a til aiia leEI Iir till Array f arlnula 391 F2 2 Entar fannula 3 Etrl Shift Entar MlNVERSEEXAMPLE The slide below shows the inversion of matrix with row 1 4 1 and row 2 2 0 The formula in the example must be entered as an array formula Select the range A4B5 starting with the formula cell Press F2 and then press CTRLSHFTENTER If the formula is not entered as an array formula the single result is 0 The process was as follows Enter data of array to be inverted Cells A4B5 Select cells A6B7 for the formula Enter the formula MINVERSE Select the range A4B5 Enter Press CTRLSHFTENTER Press Enter NPPPPN Please note that the entry of the array Formula can be tricky You must enter the data and formula as summarised above If your entry is correct the curly bracket will indicate that the formula was entered as an array formula illicrusuft Excel Lecture1 iiatrices Elle Edit ew insert Fgrmat Innls Qatar indnw Help D l g pit v g v i l q mnav T lirial 1 1 139 l I g 39 39 C12 1 39 39i39i39i IIIIII IIIIII E a It 3539 Iiil Iiil 9 i 3 U r I 2 MlNVERvTSE 3 Data Data 1 4 1 5 l U 05 l lllNVERii r435 H I i 81 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU MDETERM Returns the matrix determinant of an array Syntax MDETERMarray Array is a numeric array with an equal number of rows and columns Remarks 0 Array can be given as a cell range for example A1 03 as an array constant such as 1 23456789 or as a name to either of these o If any cells in array are empty or contain text MDETERM returns the VALUE error value 0 MDETERM also returns VALUE if array does not have an equal number of rows and columns 0 The matrix determinant is a number derived from the values in array For a three row threecolumn array A1 C3 the determinant is defined as MDETERMA1C3 equals A1 BZC3B3C2 A2B3C1B1C3 A3B1C2BZC1 0 Matrix determinants are generally used for solving systems of mathematical equations that involve several variables 0 MDETERM is calculated with an accuracy of approximately 16 digits which may lead to a small numeric error when the calculation is not complete For example the determinant of a singular matrix may differ from zero by 1E16 MDETERMEXAMPLE The example shows an array of size 4 x 4 in cell range A14d17 The formula was entered in cell A18 The result of this calculation is 88 Hiereseft Excel Beel hail Elle Edit iew insert Fgrmat leels gate indew Help Adobe PDF 2 at 3 m 2 v we v 3 i a g gum v x 4 f MDETERMEAHD1 3 E r D E F 11 MDETERM 12 3 Date Date Date Date 14 1 5 1 6 1 5 1 1 1 I T 1U 18 MDETERM 14D1T 1g Determinant If the matrix above 1 There are other ways of using this function You can enter the matrix as an array constant MDETERM3611103102 Determinant of the 82 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU matrix as an array constant You can calculate the determinant of the array constant MDETERM3611 Determinant of the matrix in the array constant 3 Unequal number of rows and columns results in an error MDETERM13851361 Returns an error because the array does not have an equal number of rows and columns VALUE MMULT Returns the matrix product of two arrays The result is an array with the same number of rows as array1 and the same number of columns as array2 Syntax MMULTarray1array2 Array1 array2 are the arrays you want to multiply Remarks 0 The number of columns in array1 must be the same as the number of rows in array2 and both arrays must contain only numbers 0 Array1 and array2 can be given as cell ranges array constants or references o If any cells are empty or contain text or if the number of columns in array1 is different from the number of rows in array2 MMULT returns the VALUE error value 0 The matrix product array a of two arrays b and c is 19 Ziggy where i is the row number andj is the column number 0 Formulas that return arrays must be entered as array formulas MMULTEXAMPLE Array1 was entered in cell range A25BZ6 Array2 was entered in cell range A28829 The 83 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hit rusuft Excel Lent u re1 ltatrites Eile Edit ElE W insert Fgrmal Innls Qatar indnw Help Adnlge F DF if it E E r 4 v lt5 i n n SLIM 1 K J F MMULTII 25EIEEi EEEIEEij A E C n 23 MMU LT 21 rray 1 rray 1 25 1 25 T 2 rray rray 23 0 29 u MMULTjA25 3D 325A23B29 The formula was entered as an array formula Cell A30 was selected for entry of the array Formula for MMULT After entering MMULT the range A25326 was selected Then was entered Next range A28329 was selected Next was entered F2 was pressed to start the entry of array formula Then the keys CTRLSHFTENTER were pressed simultaneously The answer 2 was obtained in cell A30 The formula was also entered in cell C29 to show the syntax RATIO A Ratio is a comparison between things If in a room there are 30 men and 15 women then the ratio of men to women is 2 to 1 This is written as 21 where the is the notation for a ratio The method of calculating ratios is as under 1 Find the minimum value 2 Divide all the values by the smallest value In the above example the smallest value was 15 Division gives 30 15 2 for men and 15 15 1 for women The ratio is therefore 21 for men and women RATIOEXAMPLE Three friends ali Fawad and Tanveer are doing business together To set up the business Ali invested Rs 7800 Fawad Rs 5200 and Tanveer Rs 6500 respectively The question is what is the ratio of their investments As discussed above the smallest value is 5200 All values are divided by 5200 the results are 15 for Ali 1 for Fawad and 125 for Tanveer The answer is 15 1 125 84 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrusuft Excel Lectu r21 Ratiu5P ru pa rtiu n5 hail Elle Edit ew Insert Fgrmat Tools Data window Help Adobe PDF cil v PM EvEJif 1 Ul T F H uquot r EIEEIIEIEB A E C D I 52 53 54 U le N 35 1172 55 SE 5E Fa wad 1 gmnveer 5500 BSQIB53 ED El This example was solved in Excel The formula is as under Cell D57 B57IB58 Cell D58 B58IB58 Cell D59 B59IB58 The result for cell D59 was shown in cell D60 because the cell D59 was used to display the formula ESTIMATING USING RATIO Ratio of sales of Product X to sales of Product Y is 43 The sales of product X is forecasted at Rs 180000 What should be the Sales of product Y to maintain the ratio of sales between the two products CALCULATION Ratio sales X Y 43 Insert the value for forecasted sale for X 180000 Y4 3 It can be rewritten as 180000Y 43 Cross multiply 180000 x 3 4xY Rewrite to bring the unknown to the left of the equality 4xY 180000 x 3 Solve Y 180000 x 3I4 Y 135000 glculations using EXCEL In cells B70 and B71 the ratios of Product X and Y were entered The value of forecast of product X was entered in cell D70 Before writing down the formula in excel it was derived as follows 1 Ratio ofX cell B70 85 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 2 Ratio of y cell B71 3 Sale ofX cell D70 4 Sale of Y cell D71 Now Ratio X Y cell B70 cell B71 Ratio of sales cell D70 cell D71 Crossmultiply cell B70 x cell D71 cell B71 x cell D70 Cell D71 is unknown Hence cell D71 cell B71 x cell D70 cell B70 Or cell D71 cell B71 cell B70 cell D70 Thus the formula was B71B70D70 Please note that actually we are using the ratio Y to X as it is easier to think of ratio of unknown to the known Hicrusuft Excel Lecture1 Ratiu5Prupnrtiun5 31 Eile Edit Eiew insert Fgrmal Innls Qata window Help MUSE F39DF SIZE PM Eva TL quot 5quot SLIM v E d 5 Eli lelF IIFDF III A El 3 D E EE 5 ESTIMATING USING RATIS EB 59 RATI SALES m Prduct 4 1 10mm l n Frduct i I BT1lBDD70 2 135000 F3 86 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU ESTIMATING USING RATIOEXAMPLE 2 In a 500 bed hospital there are 200 nurses and 150 other staff If the hospital extends by a new wing for 100 beds then what additional staff is needed Let us 500 beds B1 and 100 beds BZ Staff nurses N1 is 200 and other staff O1 is 150 What is the value of N2 and 02 for BZ Obviously the ratio of beds will be used As pointed out above think of the ratio of unknown to known In other words ratio 3231 or BZIB1 Ratio of nurses would be N2IN1 Ratio of other staff would be 0201 Now N2IN1 BZIB1 Or N2 BZIB1N1 or N2 100500200 40 Nurses 02IO1 BZIB1 Or N2 BZIB1O1 or 02 100500150 30 other staff Calculation Beds Nurses Other staff 500 200 150 100 X Y Nurses 500 200 100 X 500 X 200 x 100 X 200 x 100500 40 Other staff Y 150 x100l500 30 glculation usinq EXCEL The calculation using EXCEL was done in a similar fashion as the previous example The calculation is selfexplanatory Hit rnsuft Excel Lectu r21 lFlatiu5F ru pl rtin n5 Eile Edit Eiew insert Fgrmal Innls gate window Help adage F39DF CECE PM Eva i a E SLIM 1 X J 15 DFID5EE A E C D E F I G H 1 2 Uat ilNG 3 1 Hospital ddition 5 100 5 Nurses 40 jitherstaff DTID5E5 a El ESTIMATING USING RATIOEXAMPLE 3 A Fruit Punch recipe requires mango juice apple juice and orange juice ratio of 321 To make 2 litres of punch calculate quantity of ingredient needed 87 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Again we shall use the raio of unknown to the unknown The unknowns are mango and apple juice Consider first ratio of required mango juice 3 to total quantity of punch 6 This was calculated from 321 Now the quantity of required mango for 2 litre would simply be 3162 Similarly the required quantity of apple juice is 262 Calculation Mangojuice Applejuice Orange juice 3 2 1 Total 6 X 39 Y Z Total 2 litre Mango juice X 3I62 1 litre Apple juice Y 262 067 litre Orange juice Z 1162 033 litre glculation usinq EXCEL Here also the similar ratios were used Mango 820823D23 Apple 821823D23 Orange 822823D23 Hit result Excel Lect ure1 llRatie5P re pe rtie n5 Elle Edit iew insert Fgrmat leels gate indew elp Adehe PDF 33 PM EvenIE SLIM r K 3 f3 EIEEIEIEED23 EDMangjuice 1 21 juice 2 E irange juice 1 23 Ttel Litre 6 I 24 88 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 12 RATIO AND PROPORTION MERCHANDISING OBJECTIVES The objectives of the lecture are to learn about 0 Module 3 Review Lecture 11 Ratio and Proportions Merchandising Assignment 1A and 1B MODULE 3 Module 3 has the following content 0 Ratio and Proportions o Merchandising Lectures 12 0 Mathematics of Merchandising Lectures 1316 ESTIMATING USING RATIOSEXAMPLE 1 In the previous lecture we studied how ratios can be used to determine unknowns Here is another example with a slightly different approach Here the ratios of quantities are known Only one quantity is known How do we estimate the total quantity that can be made It is the quantity of orange juice that will determine the total quantity that can be made Again the method is to use the ratio of the unknown to the known Punch recipe Ratio of mango juice apple juice and orange juice 321 If you have 15 litres of orangejuice how much punch can you make Calculation Mangojuice Applejuice Orangejuice 3 2 39 1 Total 6 X Y 215 Total litre Mango juice X 3115 45 litre Applejuice Y 2115 30 litre Orange juice Z 15 litre Total 45 30 15 9 litre EXCEL calculation The method used is the same as used in previous examples 89 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hit rusuft Excel Lecture1 atiu5l3rupu rtiu n5 Elle Edit iew insert Fgrmat Innls gate indnw Help adage F39DF cila r 1quot 3 3 1 F sum v n 4 EISMEIEE T133 31 Mang juice 45 32 juice quot 33 iirange juice 15 Total Litre 334 f 033 35 BE ESTIMATING USING RATIOSEXAMPLE g In this example the ratios are the same Quantity of orange juice is known The quantity of mango and applejuice is to be calculated if the total requirement is 500 litre Punch recipe The ratio of mangojuice applejuice and orangejuice is 3 2 15 If you have 500 milliliters of orangejuice how much mangojuice and applejuice is needed Mangojuice Applejuice Orangejuice 3 2 39 15 Total 65 X Y Z 500 litre Total litre Mangojuice X 315500 1000 litre Apple juice Y 21 5500 667 litre Orange juice Z 500 litre Total 1000 667 500 2167 litre EXCEL calculation Here also ratios were used Mango B45B47D47 Apple B46B47D47 Orange D47 90 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrnsnft Excel LectureIURatiu5l3rnpurtinns Hail Elle Edit iew Insert Fgrmat Innls gate indnw elp adage F39DF ltl v PM Evr f 1L SLIM r X d it EMEIEMFDA A El C D E F 41 13 44 1000 5 Mang juice B45lB47i D47 45 4 range juice 15 e Ttal MI 49 EXERCISE In a certain class the ratio of passing grades to failing grades is 7 to 5 How many of the 36 students failed the course The ratio quot7 to 5quot or 7 5 or 75 tells you that of every 7 5 12 students five failed That is 512 of the class flunked Then 512 36 15 students failed PROPORTION ab old the values in the quotbquot and quotcquot positions are called the quotmeansquot of the proportion while the values in the quotaquot and quotdquot positions are called the quotextremesquot of the proportion A basic defining property of a proportion is that the product of the means is equal to the product of the extremes In other words given ab old it is a fact that ad bc PROPORTIONEXAMPLES ls 24140 proportional to 30176 Check 140gtlt30 4200 24X176 4224 So the answer is that They are not proportional PROPORTION EXAMPLE 1 Find the unknown value in the proportion 2 x 3 9 2 x 3 9 First convert the colonnotation ratios to fractions 2x 39 Then solve 2x 39 18 3x 6 x PROPORTION EXAMPLE 2 Find the unknown value in the proportion 2x 1 2 x 2 5 91 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 2x12x25 First convert the colonnotation ratios to fractions 2x 12 x 25 Then solve 2x 12 x 25 52x 1 2x 2 10x 5 2x 4 8x 1 18 MERCHANDISING What does merchandising cover 0 Understand the ordinary dating notation for the terms of payment of an invoice o Solve merchandise pricing problems involving mark ups and markdowns 0 Calculate the net price of an item after single or multiple trade discounts 0 Calculate a single discount rate that is equivalent to a series of multiple discounts 0 Calculate the amount of the cash discount for which a payment qualifies STAKEHOLDERS IN Merchandising Who are the stakeholders in merchandising The main players are 0 Manufacturer 0 Middlemen 0 Receive varying levels of trade discounts o Retailer 0 Consumer There are discounts at all levels in the above chain Trade Discount If L is the list price then discount is calculated as of this price List price less discount is the net price In mathematical terms we can write Amount of discount dL D Discount L List Price Net Price L1 d Net Price List Price Amount of Discount 92 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 13 MATHEMATICS OF MERCHANDISING OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 12 o Solve merchandising pricing problems involving markup and markdown MARKUP A golf shop pays its wholesaler 2 400 Rs for a certain club and then sells it for 4500 Rs What is the markup rate glculation of Markup 1 First calculate the markup in absolute terms 4500 2400 2100 2 Then find the relative markup over the original price or the markup rate 2100 is some percent of 2400 or 2100 x2400 3 So the relative markup over the original price is 21002400 x 0875 4 Since x stands for a percentage remember to convert this decimal value to a percentThe markup rate is 875 glculation usinq EXCEL Enter wholesale price 2400 in cell B5 Enter sale price 4500 in cell B6 Enter formula for Rs Markup B6B5 in cell B7and press enter The answer is 2100 Enter formula for markup B7B5100 in cell B8 and press Enter The answer is 875 shown in cell B9 Hicreseft Excel Beek1 Elle Edit Eiew insert Fgrmet leels gate window Help Fidelge F39DF if it a v m v z v in f T a 1 e e T SLIM r K J J EIF39IEIE1IIIIZI A E I C D t l EMBRKUP 3 4 5 ViithleSele price a i iele price r Markup 3 Markup BTlB5100 a 75 IEI 93 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU MRKUPEXAMPLE 1 A computer software retailer used a markup rate of 40 Find the selling price of a computer game that cost the retailer Rs 1500 Markup The markup is 40 of the cost so the markup is 0401500 Rs 600 Selling Price Then the selling price being the cost plus markup is 1500 600 Rs 2100 The item sold for Rs 2100 glculation usinq EXCEL Enter wholesale price 1500 in cell B17 Enter Markup in cell B18 Enter formula for sale price including markup 1B18100B17 in cell B19 Here the term 1B18100 is the multiplication factor B18100 is the markup in fraction The result of this part of the calculation is 14 The answer 2100 is shown in cell B20 We could have calculated the multiplication factor separately But as you see it is not necessary as the entire calculation can be done in one line Hicreseft Excel Beek1 Elle Edit iew lnserl Fgrmat eels gate indew Help Adege F DF is at 3 w v 2 v are t e 1 391 e 39 SLIM 139 K 394 f3 I1El1BI1IIIIIIj1 El1Ff A E I I3 D 39 12 39 13 14MWRKUP 15 Example 1B 1 I kaihleSale price 1500 la Markup 40 1B1l 1 0011317quot 2391 00 21 MARKDOWN Markdown means a reduction from the original sale price Let us look at an example to understand how markdown is calculated MRKDOWNEXAMPLE 1 An item originally priced at 3300 Rs is marked 25 off What is the sale price 94 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Markdown First nd the markdown The markdown is 25 of the original value so x 0253300 825 Selling Price Then calculate the sale price by subtracting the markdown from the original price 3300 825 2475 The sale price is 2475 Rs glculation usinq EXCEL Enter original price 3300 in cell B28 Enter Markdown 25 in cell B29 Enter formula for Rs Markdown B29100B28 in cell B30 Here the term B29100 is the markdown in fraction The result of this part of the calculation is 825 Enter formula for net sale price B28 B30 in cell B31 This formula is not shown in the slide We could have calculated the net sale price directly also by writing just one formula lB29100B28 In other words the multiplication factor is calculated as 1025 075 and multiplied with the original price 3300 The answer would be the same By breaking the calculation in parts you can check the intermediate result and avoid errors But if you become very conversant with formulas then you may wish to reduce the number of unnecessary steps in the calculations Hicrusuft Excel Buul Eile Edit ierJ insert Fgrmat Inuls Qatar indnw Help adage F DF as it Ev r v 2 v v 3 1 39 tr T SUM v F uquot 13 ElEEiIiIIIIIIElEE A E I I3 D 25 25 Example 1 2 23 riginel price 29 We ezemmeze 31 at iele price 32 DISCOUNT Discount is a reduction in price which the seller offers to the buyer 95 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU DISCOUNTEXAMPLE 1 The price of office equipment is 3000 The manufacturer offers a 30 trade discount Find the net price and the trade discount amount Discount Net Price L1 d 30001 3 30007 2100 Rs Amount of discount dL 3 3000 900 Rs glculation usinq EXCEL Enter price of equipment 3000 in cell B39 Enter trade Discount 30 in cell B40 Enter formula for Rs Discount B40100B3 in cell B41 Here the term B40100 is the discount in fraction The result of this part of the calculation is 900 Enter formula for net price B39B41 in cell B42 This formula is not shown in the slide The result is 2100 as shown in cell B42 Hicrusuft Excel Hunl Elle Edit Eiew insert Fgrmal Inuls Qatar indnw elp ndnlge PIZle 515539 it Ev r v E f 4 v a 39L n It SLIM r K J f EMEII1IIIIII El33 A E I3 D 35 35 Dli iCIUNT 3 Example 1 33 39 Price f equipment Ill till Trade diecunt e40r10mese 42 Net price 2100 43 96 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 14 MATHEMATICS 0F MERCHANDISING PART 2 OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 13 Financial Mathematics Part 1 SERIES DISCOUNT This refers to the giving of further discounts as incentives for more sales TOTAL DISCOUNT The series discount is as follows 15 off first Then 10 off next Then 5 off next Total discount not 30 TRADE DISCOUNTEXAMPLE The price of office furniture is Rs 20000 The series discounts are 2010 5 What is the net price Trade Discount N Ll d N ldl ld2 ld2 20000l2l10l05 200008995 200006840 13680 Rs The calculation 20000l2l10l05 was used to write the formula for the discount Then the discount was subtracted from the original price 97 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrusuft Excel Buuk1 Elle Edit Eiew Lnsert Fgrmat Tools Qatar indnw Help Adnlge PDF s it sv e v 2 v s lt5 1 quot 539 39 gum v x J s El lElEf1IIIIIIIlElEif1IIIIIIj1ElEIIIIlIIIEIj A E r 5 ma DIE SCJAUNT w 3igxrss price 2000 re First discunt in s Next discunt A n first 10 an Next disctint it I1 first BTTt 1BTEI 32 a 100fltt1Bml1 l 2 Net prlce Rs 13609 itma m n LEIEST PRICE An order for power tools has a Rs 2100 net price after a 30 trade discount What is the list price Net Price Net Price L1 d L N1 d 21001 3 2100 7 3000 Rs EXCEL calculation EXCEL formula for original price was based on the calculation 21001 3 The net price was entered in cell B67 Trade discount was entered in B69 The formula for Original price was entered in cell B71 as B671B69100 The answer is shown in cell C72 as 3000 98 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrusuft Excel Buul Elle Edit ew insert Fgrmat Innls gate indnw elp Fider F39DF WaitEr v Ev lit I SLIM r K J f EIEFftiEiEEI1EIEI A E I C D E3 54 LIST PRICE 55 EE 5 Net price 2100 EB 59 Trade discunt Mu F EI eem1eeei1oo a 3000 F TRADE DISCOUNTEXAMPLE 2 Find the single discount rate that is equivalent to the series 15 10 and 5 Net Price N L1 d Trade Discount Apply the multiple discounts to a list price of Rs 100 1d11d21d3 15 10 5 10008509095 10007268 7268 Discount 100 7268 2762 EXCEL calculation EXCEL formula for original price was based on the calculation 10008509095 The formula for net price was entered in cell F8 The formula is shown in cell F8 The answer is shown in cell F12 99 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU liilicrnsnft Excel Bunk1 Eile Edit Eiew insert Fgrn39iat Innis gate indnw elp Fidnlge F39DF it Ev m 2 v e 1L quot 539 SLIM v X J 5 t1F4rl D1F5ii i1FEii1IIIjiji1IIIIII A E C D E F I G H J K 1 2 SERIES DISCUNTS 4 First Discount 15 5 Next Diseunt 10 In 5 Next Diseth 5 Wu F Net price 1F4l1 lt1F5l1 0011F6l manned 11 12 727 13 In the following slide the net price was calaculated in cell F8 Then the discount was calculated assuming the original price was 100 This is a common method to assume 100 as the price when no price is given but you are required to calculate the net discount Micrusuft Excel Buuk39l Elle Edit iew insert Fgrmat eels Qatar indnw Help dnlge F39DF cit IE v an v E v 3 1 quot EUM 1 K J 5 1UUFE A E r n E 4 3 H 2 SERIES DISCUNTS 3 it First Diseunt 15 in 5 Next Dieeunt 1 la 5 Next Diseunt 5 are 739 a Net price 72 1e 9 Diseunt 1 U F H Hill 12 1 00 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 TRADE DISCOUNTEXAMPLE 3 The price of car parts is Rs 20000 The series discounts are 20 8 2 What is the single equivalent discount rate Trade Discount 10008092098 10007213 7213 Discount 100 7213 2787 Rs Discount 0278720000 5574 Rs EXCEL calculation EXCEL formula for net price was based on the calculation 10008092098 The formula for net price was entered in cell F21 The formula is not shown Price of car parts was entered in cell F23 Formula for discount was based on 0278720000 and is shown in cell F24 The answer is shown in cell F26 as 5574 Copyright Virtual University of Pakistan VU 101 Business Mathematics amp Statistics MTH 302 VU LECTURE 15 MATHEMATICS OF MERCHANDISING PART 3 OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 14 0 Financial Mathematics Part 2 PARTIAL PAYMENTS When you buy on credit and have cash discount terms part of the invoice may be paid within the specified time These part payments are called Partial Payments Let us look at an example You owe Rs 40000 Your terms were 310 3 discount by 10 Within 10 days you sent in a payment of Rs 10000 Rs 10000 was a part payment How much is your new balance th day MRKETING TERMS There are a number of marketing terms First of these is the Manufacturer Cost This is the cost of manufacturing Next is the price charged to middlemen in The Distribution Chain The DistributorgtWholesalergtRetailer is a chain The next term is the Selling Price This is the price charged to Consumers by Retailers MARKETING OPERATING EXPENSES AND SELLING PRICE Gross Sales less Cost of Goods sold gives the Gross Profit The gross Profit less the Operating Expenses gives the Net Profit Marketing Gross Sales Rs X Less Cost of Good Sold X Gross Profit X MarginMarkup Less Operating Expenses X Net Profit Income Rs X Operating Expenses Expenses the company incurs in operating the business eg rent wages and utilities is called operating Expenses Selling Price Selling Price is composed of Cost and Markup Selling Price S Cost C Markup M MARGIN While determining Sale Price a company includes the operating expenses and pro t to their own cost This amount is called the margin of the company Example A computer s cost is 9000 An amount of Rs 3000 was added to this cost by the retailer to determine the sale price for the consumer Thus the selling price 102 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 9000 3000 Rs 12000 Rs Rs 3000 is Margin available to meet Expenses and make a Pro t MARKUP If the Markup is to be 33 on Cost then Selling Price S Cost C Markup M 133 100 33 Cost is 100 the Base Markup is the Rate Rs Markup is the Portion MRKUPEXAMPLE You buy candles for Rs 10 You plan to sell them for Rs15 What is your Rs Markup What is your percent Markup on cost Selling price Cost 15 10 Markup Rs 5 Markup 510100 50 SELLING PRICE Fawad s Appliances bought a sewing machine for Rs 1500 To make the desired profit he needs a 60 Markup on Cost What is Fawad s Rs Markup What is his Selling price Selling Price Rs Markup 1500 x 06 900 Rs Selling Price 1500 900 2400 Rs Or 1500 x 1O6 1500 x 16 2400 Rs EXCEL Cglculgtion Here 1500 is the Sewing machine cost in cell F4 and 06 is the Percent Rs Markup on cost in cell F5 EXCEL formula in cell F6 for Rs Markup on Cost was based on the calculation 1500 x 06 The Selling price was calculated in cell F7 by using the formula F4F6 The answer as shown in cell F7was 2400 103 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU RS MARKUP AND PERCENT 0N COST Tanveer s flower business sells floral arrangements for Rs 35 To make his desired profit Tanveer needs a 40 Markup on cost What do the flower arrangements cost Tanveer What is the Rs Markup Rs Markup and Percent on Cost Sale price S Cost C Markup M S C 40C 35 1 40C C 3514 25 Rs M 25 x 04 10 Rs EXCEL calculation Here 35 is the Selling pricefloral arrangement in cell H15 Markup on cost is in cell H16 EXCEL formula in cell H18 for Cost was based on the calculation 3514 The Rs Markup was calculated in cell H19 by using the formula H18H16100 The answer as shown in cell H19 was 10 Copyright Virtual University of Pakistan 104 Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Lecture14HiarkupDi5cnuntHarkdnwn Elle Edit Eiew insert Fgrmat Iools gate indow Help Fider PDF quot3 1111 EB 1 E El TL quot 5 SLIM v 1 J 5 H151 1H1EI1EIII 1431 E C D E F G H J K 11 12 11 N 1d 111 viiielling pricefloral arrangement 111 1111 Markup on 40 1 Cost H1sl1H16Hunl 111 Markup 10 El 21 Frmula 3 22 CU4C 23 MARKUP AGAIN You buy candles for 2 Rs You plan to sell them for 250 Rs What is your Rs Markup What is your Percent Markup on Selling Price Rs Markup Rs Markup 25 2 05 Rs Percent Markup on Sellinq Price Percent Markup on Selling Price 0525 x 100 20 EXCEL calculation Here 2 is the Purchase price in cell E30 Sale price is entered in cell E31 Rs Markup on Purchase Price was calculated by using the formula E31E30 in cell E32 EXCEL formula in cell H18 for Cost was based on the calculation 3514 The Markup on sale price was calculated in cell E33 by using the formula E32E31100 The answer as shown in cell E35 was 10 105 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Mir rusuft Excel Lectu re14 arkup i5cuu ntMarkduwn Elle Edit Eiew insert Fgrmal Innls gate indnw Help Adobe PDF a it in m z n 39L 39I 1 SLIP391 r K J f E32fE311EIEI A E C D E I F G H 2 23 K U P 3i N 25 an Purchase price 31 Sale price 32 3 n sale price E32lE31f100 34 39 35 35 LLING PRICE Fawad s Appliances bought a sewing machine for Rs 1500 To make the desired profit he needs a 60 Markup on Selling price What is Fawad s Rs Markup What is his Selling Price Selling Price Selling Price S 1500 068 S 06S 1500 Rs Or 048 1500 3750 Rs Rs Markup Rs Markup 3750 x 06 2250 RS EXCEL calculation Here 1500 is the Purchase price in cell E39 Markup on Sale Price is entered as 60 in cell E40 Sale Price was calculated by using the formula E391E40100 The result 3750 is shown in cell D41 EXCEL formula in cell E42 for Rs Markup was E41E39 The result 2250 is shown in cell E42 Basic formula SC06S is shown in cell A44 In cell A45 it was simplified to 04C In cell A46 it is rewritten as SC04SC1muC04 Here mu is the Markup 1 06 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Lecture14MarkupDi5cuuntMarkduwn Eile Edit Eiew insert Fgrmat Inuls gate window Help Adnlge PIZle a it 3 w v 2 v e 7L quot F SLIM r K 39J 13 E3Eift lE4IZII1IZIEIJ A El 3 D E F e 3 33 ii393ELLNafi PRICE 39 Purchase price 4 Markup n sale price 6031 viiale price E39i1E40i100 42 43 44 45 45 3Ci04iiCi1mu Ci04 4 RS MARKUP AND PERCENT 0N COST Tanveer s flower business sells floral arrangements for Rs 35 To make his desired profit Tanveer needs a 40 Markup on Selling Price What do the flower arrangements Cost Tanveer What is the Rs Markup Selling Price Selling Price 35 C 04x 35 35 C 14 C 35 14 21 Rs Or C S 04 S 06 S 06 x 35 21 Rs Rs Markup Rs Markup 35 x 04 14 Rs EXCEL calculation Here 350 is the Sale price in cell E50 Markup on Sale Price is entered as 40 in cell E51 Cost was calculated by using the formula E501E51100 The result 21 is shown in cell D52 EXCEL formula in cell E53 for Rs Markup was E50E52 The result 14 is shown in cell E53 Basic formula SC04S is shown in cell A55 n cell A56 it was simplified to 06SCS1mus 1 07 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Micrnsnft Excel Lecture14MarkupDi5cuuntMarkduwn Elle Edit iew insert Fgrmat Innls gate window Help Fieler PIZle if it Ev E v E if 39L quot F SLIM w x 039 r3 E5IIIlE51fl22lj A El 3 n E F 3 18 49 1 5 fiale price 51 la Markup n sale price 40 E501E1l100 53 Markup 14 5 CONVERTING MARKUPS Converting 50 Markup MU on Cost MU on S Formula To convert Markup on Selling Price mus to Markup on Cost muc Markup on Selling Price mus as of Cost Markup on C1 Markup on C mus muc1muc 05105 0515 mus 03333 3333 Convertinq Markups Converting 3333 MU on Sale MU on C Formula Markup on Selling Price mus to Markup on Cost muc Markup on 81 Markup on S muc mus1mus 033331 0333 0333306666 05 50 EXCEL calculation Here 333 is the Markup on sale in cell E61 EXCEL formula in cell E62 for Markup on cost was E611001E61100100 The result 50 is shown in cell E64 Basic formula mucmus1mus100 is shown in cell A65 108 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Locturol4Morkupli countMorkdown Elle Edit iew insert Format Iools Qatar window Help v 51 X 391 it it r i E iii T 59 T i 39 sun v x r r iEoiriooirriesmoorioo ill39i E C D E F G H J K T 59 CNVERTING MARKUP N SALE T MARKUP N CST El 51 Markup n sale ifquot Aquot 2 ill gMarkup n est E61l1U llllE 39llm lti EB 54 50 55 mucmusl1 muslw 55 E EB Microsoft Excel Boom ELI Elle Edit ew insert Fgrn39iat Innis Data window elp Fider PDF cit I n r v E v E 3 1L quot 539 SLIM v E 9 x F23F22r1IIIIZI A E I D E F G H 15 SINGLE EUIV iLENT DISCUNT RHTE HS 1 First Discunt 20 in IE Next Discunt in 19 Next Discount in El 21 Net price 721 la 22 Diecunt in 23 Price f car parts Rs Discunt F22r1ool 25 25 2 CASH DISCOUNT A discount given for the prompt payment of an account is called Cash Discount No Cash Discount is allowed on Invoices Returned Goods Freight Sales Tax and Trade Discounts 109 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU DISCOUNT PERIODS Discount Periods are periods for the buyer to take advantage of Discount Terms CREDIT PERIODS Credit Periods are periods for the buyers to pay invoices within specified times CASH DISCOUNT TERMS Invoice was dated May 3 the Terms 210 mean that there is Discount 2 if invoice is paid upto 10 May Then discount can be claimed CASH DISCOUNTEXAMPLE What is the net payment for invoice value of Rs 50000 if paid upto 10 May Cash Discount N L1 d 500001002 50000098 49000 Rs EXCEL calculation EXCEL formula for net price was based on the calculation 500001002 however here an IF condition was applied that means that if the payment date in cell D31 sign is put in front of row and column to fix its location is less than or equal to 10 May then the discount will be as given in cell d30 here also sign was used to fix the location of the cell In cell D38 the date was changed to 11 May and the same formula was applied again The result as shown in cell D39 and D40 as 0 discount and 0Rs Discount 110 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 16 MATHEMATICS 0F MERCHANDISING PART 4 OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 15 o Markup and Markdown Financial Mathematics Part 3 MARKDOWN Reduction from original selling Price is called Markdown Formula Markdown Rs Markdown Selling Price original 100 MRKDOWNEXAMPLE 1 Store A marked down a Rs 500 shirt to Rs 360 What is the Rs Markdown What is the markdown Rs Markdown Let S Sale price Rs Markdown Old S New S Rs 500 Rs 360 Rs 140 Markdown Markdown Markdown Markdown 100 Old S Markdown m 500 028 28 EXCEL calculation Here 500 is the Original price in cell E73 Price after Markdown is entered as 360 in cell E74 Rs Markdown was calculated in cell E75 by using the formula E73 E74 The result 140 is shown in cell D75 lll Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU EXCEL formula in cell E76 for Markdown was E75E73 100 The result 28 is shown Micrnsuft Excel Lecture14Harkupi5rnunt arkdnwn Eile Edit iew insert Fgrmat Innls Qatar indnw elp Adnge F39DF it IE v an v E v E 3 TL quot 5 SLIM r X a 3 3 E3 Ei A El 3 D E I F 3 H Tl r1 F2 r3 iriginal price 500 H Price after Markdti r5 Markdwn39 140 E73E74 n3 ill all f MRKDOWNEXAMPLE g A variety of plastic jugs that was bought for Rs 5775 was marked up 45 of the SellingPrice When the jugs went out of production they were marked down 40 What was the Sale Price after the 40 markdown Here there are two parts to this problem First we must find the original price so that markdown can be calculated on that price Original Sale Price Selling price 100 Markup 45 Cost 100 45 55 Original Sale price 10055 x 5775 105 Rs Markdown Markdown 40 04 Rs Markdown 105 x 04 42 le price after markdown Sale price after markdown 105 42 63 Rs EXCEL calculation Here 5775 is the Original sale price in cell F83 Selling price is entered as 100in cell F84 Rs Markup was calculated in cell F85 using the formula F84F83 The result is shown as 45 in cell F85 Original Sale Price was calculated in cell F87 by using the formula F84F86F83 The result 105 is shown in cell E87 Markdown was entered as 40 in cell F88 The Rs Markdown was calculated using the formula F87F88100 in cell F89 The result 42 is shown in cell F89 112 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU The reduced price was calculated by using the formula F87F89 in cell F90 The result is shown as 63 in cell F90 Micreseft Excel Lecture14Markupli5ceuntMarkdewn Elle Edit Eiew insert Fgrmal eels gate window elp Adobe PDF Eit E v In v E v E if SUM v H I it FEAIFEEFEE A E C D E F I G H 32 pricequot 105 F4lF35eF83 riginel ea Markdown 40 la 39 an Reduced Price El PROJECT FINANCIAL ANALYSIS When you carry out Project Financial analysis a number of Financial Calculations are required The important ones are summarized below Cost estimates Revenue estimates Forecasts of costs Forecasts of revenues Net cash flows Benefit cost analysis Internal Rate of Return BreakEven Analysis COST ESTMATES In every project you will be required to prepare a cost estimate Generally such cost estimates cover calculations based on quantities and unit rates Such calculations are done in the form of tabular worksheets In large projects there may be a number of separate calculations for part projects Such component costs are then combined to calculate total cost These are simple worksheet calculations unless conditional processing is required Such conditional processing is useful if unit prices are to be found for a specific model from a large database REVENUE ESTIMATES Along with costs even revenues are calculated These calculations are similar to component costs FORECASTS OF COSTS Forecasting requires a technique for projections Once such technique Time Series Analysis will be covered later in this course Forecasting techniques vary from case to 113 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU case The applicable method should be determined first Calculation of future forecasts can then be done through worksheets FORECASTS OF REVENUES These will be done similar to the forecast of costs Here also the method must be determined first Once the methodology is clear then the worksheets can be prepared easily NET CASH FLOWS The difference between Revenue and Cost is called the Net Cash flow This is an important calculation as the entire Project Operation and Performance is based on its cash flows BENEFIT COST ANALYSIS This is the end result of the Project Analysis The ratio between Present Worth of Benefits and Costs is called the Benefit Cost BC ratio For a project to be viable without profit or loss the BC Ratio must be 1 or more Generally a BC Ratio of 12 is considered acceptable For Public projects even lesser BC ratio may be accepted for social reasons NTERNAL RATE OF RETURN Internal Rate of Return or lRR is that Discount Rate at which the Present Worth of Costs is equal to the Present Worth of Benefits lRR is the most important parameter in Financial and Economic Analysis There are a number of functions in EXCEL for calculation of lRR BREAKEVEN AMLYSIS In every project where investment is made it is important to know how long it takes to recover the investment It is also important to find the breakeven point where the Cash lnflow becomes equal to Cash Outflow After that point the company has a positive cash flow ie there is surplus cash after meeting expenses 114 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 17 MATHEMATICS FINANCIAL MATHEMATICS INTRODUCTION TO SIMULTANEOUS EQUATIONS OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 16 0 Financial Mathematics 0 Introduction to Linear Equations MARKDOWN Reduction from original selling Price is called Markdown Module 4 Module 4 covers the following 0 Financial Mathematics Lecture 17 0 Applications of Linear Equations 0 Lecture 1718 0 Breakeven Analysis 0 Lectures 1922 0 MidTerm Examination PROJECT FINANCIAL ANALYSIS Project Financial Analysis covers the following 0 Cost estimates 0 Revenue estimates 0 Forecasts of costs 0 Forecasts of revenues 0 Net cash flows 0 Benefit cost analysis 0 Internal Rate of Return 0 BreakEven Analysis EXCEL FUNCTIONS FINANCIAL AMLYSIS List of Excel Financial functions is as under The name and utility of each function is given below AMORDEGRC Returns the depreciation for each accounting period by using a depreciation coefficient 1 15 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU AMORLINC Returns the depreciation for each accounting period AMORDEGRC Depreciation Using Depreciation Coefficient AMORDEGRCcostdatepurchasedfirstperiodsalvageperiodratebasis Cost cost of the asset Datepurchased date of the purchase of the asset Firstperiod date of the end of the first period Salvage salvage value at the end of the life of the asset Period period Rate rate of depreciation Basis year basis to be used AMORLINC Depreciation Using Prorated Depreciation AMORLINCcostdatepurchasedfirstperiodsalvageperiodratebasis Cost cost of the asset Datepurchased date of the purchase of the asset Firstperiod date of the end of the first period Salvage salvage value at the end of the life of the asset Period period Rate rate of depreciation Basis year basis to be used AMORLINCEXAMPLE Data Description 2400 Cost 2401 8192008 Date purchased 2402 12312008 End of the first period 2403 300 Salvage value 2404 1 Period 2405 15 Depreciation rate Actual basis see above Result AMORLINCA2A3A4A5A6A 7A 7First period depreciation 360 CUMIPMT Returns the cumulative interest paid between two periods CUMPRINC Returns the cumulative principal paid on a loan between two periods DB Returns the depreciation of an asset for a specified period using the fixeddeclining balance method DDB Returns the depreciation of an asset for a specified period using the doubledeclining balance method or some other method you specify IRR Returns the internal rate of return for a series of cash flows MIRR Returns the internal rate of return where positive and negative cash flows are financed at different rates INTERNAL RATE OF RETURN IRR RRvaluesguess 116 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 18 MATHEMATICS FINANCIAL MATHEMATICS SOLVE TWO LINEAR EQUATIONS WITH TWO UNKNOWNS OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 17 o Solve two linear equations with two unknowns AMORDEGRCEXAMPLE E3 Microsoft Excel Lecture1EFlnanc1alFunct1ons Ej Eile Edit ew insert Format Iools gate window Help ACIer F39DF worksheet Functions by cat 1 5 1quot sts av 2 a7 321 ifquot i v sum v x a a auoeoeoecpiamamemamasaaazn a a 13 RC39cost1clatepurchased1f39irsteriocLsalvageF H Data Description 15 0019 Date purchased 1 01 End of the first period 13 Salvage value is m 015 21 4 Actual basis see above AMRDEGRCA15A16 22A17A1 ll2A21i AMORLINCEXAMPLE 1 17 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsan Excel Lecture1EFinancialFunctinns SJ Elle Edit iesI insert Fgrmet Idels Date window elp Fiddge F39DF werksheetfunctidns by cat v 5 7L quot quot 1 quot59 it E v 10 E e v a v a v SUM quot X 939 f3 AMDRLINCENE 39lE 1F39 lB F EU ED A El Deseriptin Date purchased End f the first perid i ialvage value Pe ed 20115 1 22 4 basis see abve 5A16 22 A17A1 ia20ia20 22 First perid depreciatin 300 DBEXAMPLE Micrusuft Excel Lecture1EFinancialFunctiuns ELI Eile Edit iew insert Fgrmat eels gate windth Help Add e F39DF werksheetfunttienstly tat r El I 11 wag Ev at E E T 139 A 139 T SLIM r X J f3 DB EF QE EB1Fj a E 25 D30st2salvage2ife2perid2mnth 22 Data Descriptin 2 1000000 Initial est as 100000 Salvage value 29 quotLifetime in years an DBA27A23A2917 Depreciatin in first year I D ttest salvage life period menth I 31 with nly 7 mnths calculated 22 as ADDITIONAL DB EXAMPLES Look at the following examples to see how the DB function can be used in different ways DBA2A3A417 Depreciation in first year with only 7 months calculated 18608333 DBA2A3A427 Depreciation in second year 25963942 1 18 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU DBA2A3A437 Depreciation in third year 17681444 DBA2A3A447 Depreciation in fourth year 12041064 DBA2A3A457 Depreciation in fifth year 8199964 DBA2A3A467 Depreciation in sixth year 5584176 DBA2A3A475 Depreciation in seventh year with only 5 months calculated 1584510 Returns the present value of an investment PVratenperpmtfVtype Rate interest rate per period Nper total number of payment periods in an annuity Pmt payment made each period and cannot change over the life of the annuity Fv future value or a cash balance you want to attain after the last payment is made Type number 0 or 1 and indicates when payments are due Elle Edit ew insert Fgrrnat IDEIIS gate window elp Adobe PDF WDFkShEEtFUI39IEtiDI39IS hv Eat 539 X v s 2 v a a E v a v a SLIM r X J 5 F39VtMEI12 12M9M 0 a B T a PWratenperpmtfvtype 5 Data Description Money paid out of an insurance annuity at 500 V H H 1 the end of every month 00 Interest rate earned on the money paid out 18 9 Years the money will be paid out 5 s FWA4HQ Present value of an annuity with the terms i12m3949m47 above 95977715 3 0 51 39 Returns the net present value of an investment based on a series of periodic cash flows and a discount rate NPVratevaue1vaue2 Rate rate of discount over the length of one period Value1 value2 1 to 29 arguments representing the payments and income 1 19 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Lecture1 EFinancialFunctinn5 Elle Edit Eleni insert Fgrrnat Innis gate indnw elp Adn e F39DF WDFkSl39IEEtFUI39IEtiDI39IS til Eat 539 X quot3 it E r 7 E 39 ii 395 4E quotP lt5 7 7 T SUM quot X J 5 NF Vt 5 A E 5E ED El a E T e NPWrateyalue lyaluel 55 Data Descriptin 5 10 Annual discunt rate 40000 Initial f investment ne year frm teday 59 3000 Return frm first year 5 4200 Return frm seentl year E1 600 Return frm third year 52 2 Win57A53A59A60 i XNPV Returns the net present value for a schedule of cash flows that is not necessarily periodic XN PVratevauesdates Rate discount rate to apply to the cash flows Values series of cash flows that corresponds to a schedule of payments in dates Dates schedule of payment dates that corresponds to the cash flow payments Micrnsnft Excel Lecture1EFinancialFunctinns Eile Edit new insert Furmat Innis aata window Help adnaePDF W iksheeth39mtiDMbi mt 39 539 x 9 cit a T In 1 E v 40 iii E v 51gt r i v SLIM 1 X 9quot f3 NPVA5A58A59EDEH a B 5E Data Deseriptin 5 10 Annual diseunt rate 10000 Initial est f investment ne year frm teday Return frm first year Return frm seend year Return frm third year A57A53A59A60 l l3 3213 1 1 20 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LN Returns the straightline depreciation of an asset Micreseft Excel Lecture1FinanrialFunrtiens lg Eile Edit Eiew insert Fgrmat eels Data indew Help adobe F39DF werksheet Functions quot3 ll a r i r E a0 a T e T a sun v X J 5 SLarasaassaraj a a as SLNcestsalvaellfe By Data Descriptln 53 30000 Get E9 7500 Salvage value m 10 quotYears f useful life 3LNA6A69A70 r2 The depreciatin allwance fr each year 2250 frs SYD F 39h Returns the sumof years39 digits depreciation of an asset for a specified period Micrnseft Excel Lecture1EFinancialFunrtiens ELI Eile Edit view insert Fgrmat eels gate indew elp Fidelge F39DF werksheetfunttiens 3 cl E r 9 r E t 10 a T a a SUM T X J r 3LNl EEHl59 m a a as SLNcestsalvaelife 5 Data Descriptin 53 30000 Cat 59 7500 Salvage value m 10 Years f useful life 5LNA6A69ATO r2 The depreciatin allwance fr each year 2250 rs I 7 A 121 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU VDB Returns the depreciation of an asset for a specified or partial period using a declining balance method Hicreseft Excel Lecture1EFinantialFunttiens Elle Edit Eiew insert Fgrmet Idols gate window elp adage F39DF W rksheetfuntti nst 39 it E t 392 E ll i quotr 9 quotr A 139 sum 1 x a a ynagaaasaussnsaa seem a E as VDBHstsalyagelifestaereridiendperid a faeter1noswiteh 33 Data Deseriptin 39 2400 Initial est 9 300 Salvage value 91 10 Lifetime in years 92 VDBA9A90A91365 A9501 as First day39s depreciatin a Excel autmatieally assumes that faetr is 1 as I XIRR Returns the internal rate of return for a schedule of cash flows that is not necesarilL periodic Hicreseft Excel Lecture1EFinancialFunctiens Eile Edit yiew insert Fgrmat Idols gate window Help adage F39DF worksheet Functions by cal v 539 22 a a v e v 2 v a a SUM r X v 35 IHHIIAEFA101 a B as RR yaluesguess 95 Data lessriptiun 9 70000 Initial est fa business 93 12000 Net incme fr the first year 99 15000 Net incme fr the secnd year m 1000 Net ineme fr the third year W 21000 Net incme fr the furth year W 25000 Net ineme fr the fifth year mailRRA97A101 m4 IRR after 4 years 2 m5 RRH2M IRR after years 01 s RR 2M IRR after years include a messy441 s 1 22 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU icrnsuft Excel Lecture1BFinancialFunitinn5 hail Elle Edit ew insert Fgrmat Innis gate indnw elp Adobe PDF WDrkshBBt FLI 9 at E v E v 1 E v it sum 7 x or a itinetmiiniisni110110013 A El 109 11 Values Dates m 1 0000 20110101 112 2750 2000301 113 21101030 2750 20090401 E 1 1 r 3734 153111 110 v i v x 1m 311501 121 122 LINEAR EQUATIONS o Solve two linear equations with two variables 0 Solve problems that require setting up linear equations with two variables 0 Perform linear CostVolumeProfit and breakeven analysis employing o The contribution margin approach 0 The algebraic approach of solving the cost and revenue functions SOLVING LINEAR EQUATIONSPART 1 Here is an example of solving simultaneous linear equations 2x 3y 6 x y 2 Solve for y 2x 3y 6 2x 2y 4 5y 10 y 105 y 2 SOLVING LINEAR EQUATIONS PART 2 Let us look at the same equations again 2x 3y 6 x y 2 We solved for x Now let us substitute y by 2 2x 32 6 2x 6 6 2x 0 x 0 123 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 19 PERFORM BREAKEVEN ANALYSIS EXCEL FUNCTIONS FINANCIAL ANALYSIS OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 18 0 Perform breakeven analysis 0 MS EXCEL Financial Functions SLNEXAMPLE SLN Returns the straightline depreciation of an asset for one period Syntax SLNcostsalvageife Cost is the initial cost of the asset Salvage is the value at the end of the depreciation sometimes called the salvage value of the asset Life is the number of periods over which the asset is depreciated sometimes called the useful life of the asset Illicrnsnft Excel LectureJ EFinantialFunttiun5 Elle Edit ew insert Fgrmat nulls gate induw alp adulge F39DF wurksheetfunttiuns f if E i E ti i e T a a SUM 139 X J i5 SLNII EE EQ FD a a a3 SLNcuataalvaelite 5 Data Deser lptln EB g Tialvage value a 10 Years f useful life 53LNA6A59ATU a The depreciatin allowance fr each year 2250 SYD Returns the sumof years39 digits depreciation of an asset for a specified period Syntax SYDcostsalvageifeper Cost is the initial cost of the asset Salvage is the value at the end of the depreciation sometimes called the salvage value of the asset Life is the number of periods over which the asset is depreciated sometimes called the useful life of the asset Per is the period and must use the same units as life Remark 0 SYD is calculated as follows 1 24 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU SFD nn51 aefvegej lr per lwa it l 1 Micrnsnft Excel Lecture l LFinanciaLFunctinns Elle Edit Eiew insert Fgrrnat Innis gate indnw elp riidnlge F39DF W rksl39leetluntti ns lZIv Eat 139 El I quot3 ll 3 r E an E E 39Ir 6 1 i 139 T SUM r X J 8 SrD ED l EE1 a a n3 SYDeest salvage life per rr SYD Cest Salvagelifeper1F2 if elife1i F8 F9 Data Description 3 30000 Initial cast a 7500 Salvage value 32 10 quotLifespan in years ea SYDA0A31A321 a are l cnst salvage life per 34 l l l 35 PHquot riatlien allowance for the first year 409091 VDB Returns the depreciation of an asset for any period you specify including partial periods using the doubledeclining balance method or some other method you specify VDB stands for variable declining balance Syntax VDBcostsalvageifestartperiodendperiodfactornoswitch Cost is the initial cost of the asset Salvage is the value at the end of the depreciation sometimes called the salvage value of the asset Life is the number of periods over which the asset is depreciated sometimes called the useful life of the asset Startperiod is the starting period for which you want to calculate the depreciation Startperiod must use the same units as life Endperiod is the ending period for which you want to calculate the depreciation Endperiod must use the same units as life Factor is the rate at which the balance declines lf factor is omitted it is assumed to be 2 the doubledeclining balance method Change factor if you do not want to use the doubledeclining balance method For a description of the doubledeclining balance method see DDB Noswitch is a logical value specifying whether to switch to straightline depreciation when depreciation is greater than the declining balance calculation o If noswitch is TRUE Microsoft Excel does not switch to straightline depreciation even when the depreciation is greater than the declining balance calculation o If noswitch is FALSE or omitted Excel switches to straightline depreciation when depreciation is greater than the declining balance calculation All arguments except noswitch must be positive numbers 1 25 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU icrusuft Excel Lecture LFinancialJunctiuns ENE Edit ew insert Fgrmat Inn39s Qata ind w Help adage pm workshootfunctionsl 3 it E v P v E E s Ts sum 139 x J a Vuarsaasaosairaaa season a E as VDB cost salvage life startperiod endperiod a factor noswitch 33 Data Description a Initial cost 9 Salvage value 9 10 Lifetime in years 92 VDBA9A90A91365 A9501 as First day39s depreciation a Excel automatically assumes that factor is 1 95 Returns the internal rate of return for a series of cash flows represented by the numbers in values These cash flows do not have to be even as they would be for an annuity However the cash flows must occur at regular intervals such as monthly or annually The internal rate of return is the interest rate received for an investment consisting of payments negative values and income positive values that occur at regular periods Syntax lRRvaluesguess Values is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return 0 Values must contain at least one positive value and one negative value to calculate the internal rate of return 0 IRR uses the order of values to interpret the order of cash flows Be sure to enter your payment and income values in the sequence you want 0 If an array or reference argument contains text logical values or empty cells those values are ignored Guess is a number that you guess is close to the result of IRR 0 Microsoft Excel uses an iterative technique for calculating IRR Starting with guess IRR cycles through the calculation until the result is accurate within 000001 percent If IRR can39t find a result that works after 20 tries the NUM error value is returned 0 In most cases you do not need to provide guess for the IRR calculation If guess is omitted it is assumed to be 01 10 percent 0 If IRR gives the NUM error value or if the result is not close to what you expected try again with a different value for guess Remarks IRR is closely related to NPV the net present value function The rate of return calculated by IRR is the interest rate corresponding to a 0 zero net present value The following formula demonstrates how NPV and IRR are related 126 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU NPVRRB1B6B1B6 equals 360E08 Within the accuracy of the IRR calculation the value 360E08 is effectively 0 zero IRREXAMPLE In the slide the Excel worksheet is shown In cell A97 the investment of 70000 is entered with minus sign to denote negative cash flow In cell A98 to A102 revenue per year 1 to 5 is entered In the first formula in cell A103 IRRA97A101 only years 1 to 4 were selected for the revenue stream The HR is 2 in this case In the next formula in cell A105 the entire revenue stream was considered The IRR improved to 9 Next only first 2 years of revenue stream were considered with an initial guess of 10 not shown in slide The result was 44 Niereeeit NEEI Leeture1EFineneialFunetiene Elle Edit New insert Fermet eels gate winderl Help 1 E X 3912 it it I r E a E 1quot 1 1quot 1quot sea r X J a Ieeraerza1m a B ae are late leeeriptien 9 70000 Initial f a 93 12000 Net incme fr first year 99 15000 Net incme fr year 0 1000 Net incme fr third year 1 21000 Net iname fr furth year my 20000 Net incme fr fifth year we IRRA07Ai01 e INN after 4 2 a v I1 1 r Heheet15heet25heet3 M i iii Edit 1 27 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU XIRR Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic To calculate the internal rate of return for a series of periodic cash flows use the IRR function If this function is not available and returns the NAME error install and load the Analysis ToolPak addin To do that 1 On the Tools menu click AddIns 2 In the AddIns available list select the Analysis ToolPak box and then click OK 3 If necessary follow the instructions in the setup program Syntax XIRRvauesdatesguess Values is a series of cash flows that corresponds to a schedule of payments in dates The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment If the first value is a cost or payment it must be a negative value All succeeding payments are discounted based on a 365day year The series of values must contain at least one positive and one negative value Dates is a schedule of payment dates that corresponds to the cash flow payments The first payment date indicates the beginning of the schedule of payments All other dates must be later than this date but they may occur in any order Dates should be entered by using the DATE function or as results of other formulas or functions For example use DATE2008523 for the 23rd day of May 2008 Problems can occur if dates are entered as text Guess is a number that you guess is close to the result of XIRR Remarks 0 Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations By default January 1 1900 is serial number 1 and January 1 2008 is serial number 39448 because it is 39448 days after January 1 1900 Microsoft Excel for the Macintosh uses a different date system as its default 0 Numbers in dates are truncated to integers o XIRR expects at least one positive cash flow and one negative cash flow otherwise XIRR returns the NUM error value o If any number in dates is not a valid date XIRR returns the VALUE error value o If any number in dates precedes the starting date XIRR returns the NUM error value o If values and dates contain a different number of values XIRR returns the NUM error value 0 In most cases you do not need to provide guess for the XIRR calculation lf omitted guess is assumed to be 01 10 percent 0 XIRR is closely related to XNPV the net present value function The rate of return calculated by XIRR is the interest rate corresponding to XNPV 0 0 Excel uses an iterative technique for calculating XIRR Using a changing rate starting with guess XIRR cycles through the calculation until the result is accurate within 0000001 percent lf XIRR can39t find a result that works after 100 tries the NUM error value is returned The rate is changed until where P J I39 92 lairnil quot3 1 rate 355 di the ith or last payment date d1 the 0th payment date Pi the ith or last payment 128 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU XIRR EXAMPLE Here the investment is in cell A111 The revenue stream is in cells A112 to a115 The dates for each investment or revenue are given in cells B111 to B115 Please note that the dates are in European format yearmonthday On your computer you may not have this format Hieruseft Excel Leeture1EFinaneialFunetiuns Eile Edit Eiew Lnsert Fgrmet leels gate indew Help Adelge PDF werksheetfu e is es 2 v e e v e SLIM X J f3 EIRHIIA111A1158111EI115I1 A e 109 XIRW velues etesguess 11 Values Dates 1 1 0000 21100101 12 2T50 21100301 13 4250 21101030 11 2009021 11527t tl 20090401 E 1 1 or 3134 H 1 B1 1 1 3 311501 1 After entering these days in Excel you can right click on the cell You see a short cut menu as shown below Hicreseft Excel Leeture1EFinancialFunctiens lg Eile Edit ew insert Fgrmet Ieels gate window Help D l ts gzvgi t q 100 s Mal vEDvIuEE tdatassss ns Elil l v 5 DATE12IIIIIEE11 B I 109SQUESS 11 Dates ll2000101 35 Cut 12 2000301 m 13 20131030 Ease Paste Ejeciel 2009021 20090401 HE XIRR 037336 34 LE Insert Comment i I 1 1E Eermet Cells 1 19 Picl Frem List 1239 eld etch 121d te5 e meant When you will select Format Cells the Format Cells Dialog Box appears as shown below You can then choose the desired format for the date 1 29 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Fe rmat Eells Alignment l Fpnt l Elprder l Patterns F39rcntectipnl Qategcnr39y Sample General J EIIIIIIEIIIIlIIII Numher 39 Currency Type Accpuntini den 14 mars EDEII quot13 setups14 Percentage came344 133e Fractlpn 3143314 Scienti c mas14 1aaa 9335 143 2am Esmm J Lecale IIIcIcatipnII ISwedish j Date Fermats displasr date and time serial numbers as date values Except Fcur items that ha39re an asterislt If J applied Fermats dc net switch date erders with the prerating system I DIE Cancel I In cell A116 the formula XIIRA111A115B111B11501 the range A111A115 is the cost and revenue stream The range B111B115 is the stream for dates The third term 01 is the initial guess for XIRR The answer in fraction or is given in cell B1163734 LINEAR EQUATIONS Linear equations have following applications in Merchandising Mathematics 0 Solve two linear equations with two variables Solve problems that require setting up linear equations with two variables Perform linear CostVolumeProfit and breakeven analysis employing The contribution margin approach The algebraic approach of solving the cost and revenue functions SOLVING LINEAR EQUATIONS AGAIN Let us look at the example of two linear equations we did in handout 18 2X 3y 6 l X y 2 2 My We solved for y Result y 2 SOLVING LINEAR EQUATIONS PART 2 Let us look at the same example again Solve for X We solved for X x 0 l 3 0 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Check your answer By substituting the values into each of the equations Eguation 1 2x 3y 6 x 0 y 2 LHS 2x 3y 2032 6 RHS Equation 2 x y 2 LHSx y022RHS The right side is equal to left hand side Hence the answer is correct 131 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 20 PERFORM BREAKEVEN ANALYSIS EXCEL FUNCTIONS FOR FINANCIAL ANALYSIS OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 18 0 MS EXCEL Financial Functions 0 Perform BreakEven Analysis SETTING UP LINEAR EQUATIONS Zain purchases the same amount of commodity 1 and 2 each week After price increases from Rs 110 to Rs 115 per item of commodity 1 and from Rs 098 to Rs 114 per item of commodity 2 the weekly bill rose from Rs 8440 to Rs 9170 How many items of commodity 1 and 2 are purchased each week Setting up Linear Equations Let x of commodity 1 Let y of commodity 2 Settinq up Linear Equations Equation 1 110x 098y 8440 1 Eliminate x in 1 by Dividing both sides by 110 110x 098y110 8440110 x 08909y 7673 Equation 2 115x114y917 2 Eliminate x in 2 by Dividing both sides by 115 115x 114y115 9170115 x 09913y 7974 Result 1 x 08909y 7673 3 x 09913y 7974 4 Next Subtract 4 from 3 Result 2 01004y 301 y 30101004 Or y2998 Le 30 nos 110x 098y 8440 Substitution Substitute value of y in 1 Result 110x 0982998 8440 Solve 110x 2938 8440 110x 8440 2938 110x 5502 Result x 5002 Le 50 nos New weele cost Commodiy 1 50 x115 5750 Commodiy 2 132 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU 30 x 114 3420 Total cost 9170 TERMINOLOGY There are either Business Costs or Expenses Fixed Costs Fixed Costs are such costs that do not change if sales increase or decrease eg rent property taxes some forms of depreciation Variable Costs Variable costs do change in direct proportion to sales volume eg material costs and direct labour costs Break Even Point Break Even point we discussed earlier It is a point at which neither a profit nor loss is made Contribution Marqin Contribution Margin is the Rs amount that is found by deducting ALL Variable Costs from Net Sales and contributes to meeting Fixed Costs and making a Net Profit Contribution Rate Contribution Rate is the Rs amount expressed as a percent of Net Sales A CONTRIBUTION MARGIN STATEMENT Rs Net Sales Price Units Sold x 100 Less Variable Costs x x Contribution Margin x x Less Fixed Costs x x Net Income x x The net sales are calculated by multiplying price per unit with number of units This figure is treated as 100 Next variable costs are specified and deducted from the Net sales to obtain the Contribution Margin Next Fixed costs are deducted from the contribution Margin The result is Net Income Under the column percentage of each item is calculated with respect to the Net Sales SCENARIO 1 Market research for a new product indicates that the product can be sold at Rs 50 per unit Cost analysis provides the following information Fixed Costs FC per period Rs 8640 Variable Costs VC Rs 30 per unit 133 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Production Capacity per period 900 units How much does the sale S of an additional unit of a firm s product contribute towards increasing its net income Formula Contribution Margin CM S VC Contribution Rate CR CMS 100 lk Even Point BEP in Units x Rs x FC CM S in Sales Rs Rs x FC CM S in of Capacity BEPin UnitsPC100 At Break Even Net Profit or Loss 0 Scenario 1 Summary The new product can be sold at Rs 50 per unit Costs are as follows Fixed Costs are Rs 8640 for the period Variable Costs are Rs 30 per unit Production Capacity is 900 units per period Copyright Virtual University of Pakistan 134 Business Mathematics amp Statistics MTH 302 VU LECTURE 21 PERFORM LINEAR COSTVOLUME PROFIT AND BREAKEVEN ANALYSIS USING THE CONTRIBUTION MARGIN APPROACH OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 18 0 Perform BreakEven Analysis 0 MS EXCEL Financial Functions 0 OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 20 0 Perform linear costvolume profit and breakeven analysis 0 Using the contribution margin approach SCENARIO 1 CMS VC50302O Rs CR CMS 100 Rs 2050 100 40 Break Even Point Units x FC CM 864020 432 Units ln Rs x FCCM S Rs 8640Rs20 Rs50 Rs21600 BEPin units PC100 432 900100 48 of apacity SCENARIO 2 135 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU The Lighting Division of A Lighting Fitting Manufacturer plans to introduce a new street light based on the following accounting information FC Rs 3136 VC Rs157 S Rs185 Capacity 320 units Calculate the break even point BEP in units in rupees as a percent of capacity Break Even Point in units FC CM S VC CM Rs185 157 Rs28 Rs313628 112 Units Break Even Point in Rupees FC CM S 313628 185 20720 Rs Break Even Point as a percent of capacitv BEPin unitsPC1OO 112320 100 35 of Capacity SCENAR0 21 FC Rs3136 VC Rs157 S Rs185 Capacity 320 units Determine the BEP as a of capacity if FC are reduced to Rs2688 Formula BEPin unitsPC100 Step1 Find CM Step 2 Find BEP in units Step 3 Find of Capacity Step1 Find CM S 185 VC CM Rs 28 Step 2 Find BEP in units FCCM Rs 2688 Rs28 96 Units Step 3 Find of cmcitv BEPin units PC100 96320100 30 of Capacity SCENARIO 22 136 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU FC Rs3136 VC Rs157 S Rs185 Capacity 320 units VC S80 Rs148 Determine the BEP as a of capacity if FC are increased to Rs4588 and VC reduced to 80 of S BEPin unitsPC100 Step1 Find CM S 185 VC CM Rs 37 Step 2 Find BEP in units FCCM Rs 4588 Rs 37 124 Units Step 3 Find of cmcitv BEPin units PC100 124320100 39 of Capacity SCENARIO 2 3 FC Rs 3136 VC Rs157 S Rs185 Capacity 320 units Determine the BEP as a of capacity if S is reduced to Rs171 BEPin unitsPC100 Step1 Find CM S 171 VC 1 CM Rs 14 Step 2 Find BEP in units FCCM Rs 3136 Rs 14 224 Units Step 3 Find of Capaciy BEPin units PC100 224320100 70 of Capacity 137 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 22 PERFORM LINEAR COSTVOLUME PROFIT AND BREAKEVEN ANALYSIS OBJECTIVES The objectives of the lecture are to learn about I Review Lecture 21 I Perform Linear CostVolume Profit and BreakEven analysis Using Microsoft Excel SCENARIO 1 Let us look at different scenarios for calculation of contribution margin and net profit The explanations are given in the slides The Break Even in Rs ls 21600 The break Even in units is 48 Hicrusuft Excel Lecture225cenariu51 Eile Edit ew Insert Format Tools Data indow Help Finler F39DF Ail D twtt a E atlmt tarial vznvnru iv am 3 aEPinunitsrPcnnu a a a C D E F r3 H J I41 2 1 3 Production Capacity 4 si39iLale 5 Variable Costs a Contribution Margin H4H5 Fixed Cost FC 540 3 Units x FC l on H7lH6 g BEP in ch l our H tH4 m BEF in units lF Ci 100 a 4 HlH3100 11 SCENARIO 2 The Break Even in Rs ls 20720 The break Even in units is 35 1 3 8 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Microsoft Excel Lecture225cenarius1 Ail i Eile Edit iew insert Format Idols gate window elp Adobe PDF use is amazaaiaawai 14 Production Capacity 15 Sale S 15 15 Variable Costs MC 157 1 Contribution Margin SVC H15H16 13 Fixed Cost FC 19 Units x FC i CM 112 H1iH1T 2n BEP in FC i CM 5 20720 H19H15 21 BEF39in units iPCt1UU a H19iH14t1UU 23 SCENARIO 21 he Break Even in Rs Is 17760 The break Even in units is 30 Microsoft Excel Lecture22 cenarins1 39539 i Eile Edit Eiew insert Format Idols gate window elp adobe F39DF dress a gassi ltnrial vEUvnsu E E H32 3 H3nrH25nnn a a t D E F s H J K cenanogt1 21 25 Production Capacity 25 Sale 15 2 Variable 157 as Contribution Margin 2 H26H27 29 Fixed FC 3n Units x FC i 96 H29iH2 31 BEP in i CM 17760 H30H26 BEPin units iPC i 100 130 H30iH251100 33 SCENARIO 22 The Break Even in Rs Is 22940 The break Even in units is 39 1 3 9 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Eile Edit Eiew lnsert Format Idols Eata window elp Adobe PDF 539 A noaa amzamg canal vmv naaataaamv 4 Prductin Capacity ame a 15 42 Variable Cato VC 0S 14 43 Cntributin Margin 37 H41H42 a Fixed Cat FC 45 45 Units x FC i 124 H44iH43 as BEP in R5 FC i CMF S H451H41 a BEPin units iPC 1100 a H45iH401 100 aa SCENARIO 23 he Break Even in Rs Is 38304 The break Even in units is 70 Eile Edit iew lnsert Format Idols gate indow elp Adobe PDF E Da a oedema tarial vmv au faittiavi E Q Q J A B C II E F I3 H J K 5i cenanow3 52 Production Capacity 53 Sale 171 a Variable Coats WC 157 55 Contribution Margin 14 H53H54 as Fixed Coat FC 5 Units 4 FC i 224 H56iH55 ea BEP in FC i CMF H571 H53 59 BEPin unite iPC1100 a TU H57iH5T100 El 140 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU SCENARIO 24 FC Rs 3136 VG Rs 157 8 Rs 185 Capacity 320units Determine the NI if 134 units are sold Formula for Net Income NI Units above BEPCM BEP in unitsPC1OO Step1 Find CM S 185 V0 1 CM Rs 28 CM of Rs28 per unit Step 2 Find BEP in units FCCM Rs 3136 Rs 28 112 Units Step 3 Find unit over BEP Units Sold 134 BEP 112 Over BEP 22 Hence Company had a NI of 22 Rs 28 Rs 616 Scena o FC Rs 3136 VG Rs157 S Rs185 Capacity 320 units What unit sales will generate NI of Rs 2000 Formula for Net Income Units above BEP NICM Step1 Find CM S 185 V0 1 CM Rs 28 CM of Rs28 per unit Step 2 Find BEP in units FCCM Rs 3136 Rs 28 112 Units Step 3 Find unit over BEP NICM Rs 2000Rs 28 per Unit 72 Units above Break Even Hence 72 Units above BEP 112 BEP Units Total Sales Units 184 141 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Hicrnsnft Excel Lecture Z5cenariu51 Eila Edit Eiaw insert Fgrmat Innis gate indnw elp aaaaa PDF A Da a a E s i tarial wraaaagtatatt av a1 3 EHaaiHaa a a C a E F a H I a a aa riable aa Cnntributinn Margin 28 aa Fiaacl FCi aa Units at FCi i 112 H85iH04 aa BEP in FCiCMF H861H82 aa BEPin units 121100 a H86iH811100 aa Units staid aa NI Unita twat BEP iCM 2000 l Units naar BEP MEN 1 LHBn i aa Tntal units EiEP abnaa BEP units H1H8ti Scenario 26 FC Rs 3136 VG Rs157 S Rs185 Capacity 320 units What are the unit sales if there is a Net Loss of Rs336 Formula Units below BEP NICM Step1 Find CM 3 185 VC 1 CMRs 28 CM of Rs28 per unit Step 2 Find BEP in units FCCM Rs 3136 Rs 28 112 Units Step 3 Find unit below BEP NIICM Rs 336Rs 28 per Unit 12 Units below Break Even Hence 112 BEP 12 Units Below Total Sales Units 100 142 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU DE E ii a Him 3 H1IIEH1IIi a a c n E F e 5 Scenario as Prndustinn Capacity 9 Sale S as riabls Casts 99 Csntributinn Margin CM inn Fixed Cast FCI 101 Units in FC i ID BEP in FCI i CM ma BEPin units iPCI1DD lIZIEi Units ssld ms NI Units aver BEPi CIM 105 Units aver BEP NliCIWi islTntal units BEP absvs BEP units FC Rs 3136 VG Rs157 S Rs185 Capacity 320 units The company operates at 85 capacity Find the Profit or Loss Formula units above BEP CM N Step1 Find CM E 3 M i f Ariel 3 185 VC 1 CMRs 28 CM of Rs28 per unit Step 2 Find BEP in units FCCM Rs 3136 Rs 28 112 Units Step 3 Find unit over BEP 32085 2 Units Production 272 BEP 112 Over BEP 160 Hence 160 Units 28 Profit 4480 Rs 157 23 HSTH98 112 H100iHSS 20720 H101HST H1o1rHs1nn 134 12 H105H99 mu H105H101 Copyright Virtual University of Pakistan 143 Business Mathematics amp Statistics MTH 302 VU maggot sfnvz tml finial vl v f Hill 3 E C A 1 Scenario 111 Production Capacity PC B5 Capacit 112 Sale 1315 113 Variable Costs W 157 111 Contribution Margin CM SVC 23 H112 H113 115 Fixed Cost FE 1113 Units Ft 1 CM 112 H1125lH114 111 BEP in FC1lCiMilri H116H112 11s BEPin units lPCi100 41176 H11t36lH111 i100 119 Units sold 134 12a Units over BEP PCBEP Units 160 H111H11E 121 Profit Units over BEP CM 4430 H120H111 CASE Company A s year end operating results were as follows Total Sales of Rs 375000 Operated at 75 of capacity Total Variable Costs were Rs 150000 Total Fixed Costs were Rs 180000 What was Company A s BEP expressed in rupees of sales 1 44 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU LECTURE 23 STATISTICAL DATA REPRESENTATION OBJECTIVES The objectives of the lecture are to learn about 0 Review Lecture 22 0 Statistical Data Representation MODULE 5 Statistical data representation Lecture 23 Measures of central tendency Lectures 2425 Measures of dispersion and skewness Lectures 2627 MODULE 6 Correlation Lecture 2829 Line Fitting Lectures 3031 Time Series and Exponential Smoothing Lectures 3233 MODULE 7 Factorials Permutations and Combinations Lecture 34 Elementary Probability Lectures 3536 ChiSquare Lectures 37 Binomial Distribution Lectures 38 MODULE 8 Patterns of probability Binomial Poisson and Normal Distributions Lecture 3941 Estimating from Samples Inference Lectures 4243 Hypothesis testing ChiSquare Distribution Lectures 4445 EndTerm Examination STATISTICAL DATA Information is collected by government departments market researchers opinion pollsters and others Information then has to be organised and presented in a way that is easy to understand BASIS FOR CLASSIFICATION 1 Qualitative Attributes sex religion 2 QuantitativeCharacteristics Heights weights incomes etc 3 Geographical Regions Provinces divisions etc 4 Chronological or Temporal 5 By time of occurrence Time series TYPES OF CLASSIFICATION 145 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU There are different types of classifications Oneway One characteristic Population Twoway Two characteristics at a time Threeway Three characteristics at a time METHODS OF PRESENTATION Different methods of representation are Text quotThe majority of population of Punjab is located in rural areas Semitabular Data in rows Tabular Tables with rows and columns Graphic Charts and graphs TYPES OF GRAPHS 0 Column Graphs 0 Line Graphs 0 Circle Graphs Sector Graphs 0 Conversion Graphs 0 Travel Graphs 0 Statistical Graphs 0 Frequency Tables 0 Histograms 0 Frequency distributions 0 Cumulative Distributions PICTURE GRAPHS Picture graphs use the picture as one unit In the example below one car represents 10 cars PICTUE GPHS Ears passing schnnl ate durin the i39 39 Earth lfpl 39m 39 13952 Hm I f Emu 2 3 gun 11 1 1311 illi39l ll E1 in 345539 mm 6 7 mm SECTOR GRAPHS 146 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 VU Sector graphs use the division of a circle into different sectors The full circle is 360 degrees For each percentage degees are calculated and sectors plotted SECTOR GRAPHS Distributien ef employment injuries by necupatinn 39L39il3939u il39il uli39quot Jud uLLJ i 3939I mu 3 5 rI39Jr39I39 El39liiei393Iquot391tquot39 I 3j3939i3239 ERNIE II39Jltj d39 a39 E a COLUMN AND BAR GRAPHS The following slide gives the Proportion of households by size in the form of a Column and Bar graph Prepertien ef heusehelde by size L gE was 3 7191 113 39 39L if i g r 43 In 7mm lmquot Fine Edm39ll Pelee i ieus ihe ii LINE GRAPHS Line graphs are the most commonly used graphs Here the data of one variable say Height is plotted against data of the other variable say Age 147 Copyright Virtual University of Pakistan Business Mathematics amp Statistics MTH 302 LINE GRAPHS A student39s height With IE EEG EE39J 3quot tea H hIEI V M y a 3 5quot am i 1 Huxley3 r1th I i I I I I I I I I I I I1 i L I 3 d S E 395 E E1 I I E L5 139 5 i 5 i1 15 I LU VU 148 Copyright Virtual University of Pakistan

### BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.

### You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

## Why people love StudySoup

#### "Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

#### "I made $350 in just two days after posting my first study guide."

#### "Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

#### "Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

### Refund Policy

#### STUDYSOUP CANCELLATION POLICY

All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email support@studysoup.com

#### STUDYSOUP REFUND POLICY

StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here: support@studysoup.com

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to support@studysoup.com

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.