π Lecture 7 β Applications of Basic Mathematics
π Overview: This lecture introduces the concept of annuities, which are series of fixed payments made or received over time, and provides formulas for calculating their accumulated (future) and discounted (present) values. It also reviews fundamental algebraic operations, exponents, and the method for solving linear equations, establishing a foundation for further studies in business mathematics and statistics.
ποΈ Topics Covered
The lecture covers the scope of Module 2, a review of Lecture 6, and then dives into the definition of an annuity. It explains how to calculate the accumulated value (future value) of an annuity using the accumulation factor and the discounted value (present value) using the discount factor. The latter part of the lecture reviews algebraic operations, including division by monomials, multiplication of polynomials, working with exponents, and solving linear equations.
π Lecture Summary
Annuity
An annuity is a series of fixed payments required from you or paid to you at a specified frequency over a fixed period of time. Annuities are typically used to build retirement income, save for a childβs education, or create a trust fund. The most common payment frequencies are yearly, semi-annually, quarterly, and monthly.
π‘ Why this matters: Understanding annuities is crucial for personal financial planning, like retirement savings or loan payments.
π Definition β Annuity: A series of fixed payments made or received at a specified frequency over a fixed period of time.
Calculating the Future Value or accumulated value of an Annuity
The future value of an ordinary annuity formula is useful for finding out how much you would have in the future by investing at a given interest rate. The formula calculates the accumulated value of all cash flows.
π Formula:
S = R * [((1 + i)^n - 1) / i]
Where:
S= Accumulated valueR= Payment per period (amount of annuity)i= interest rate per conversion periodn= number of payments((1 + i)^n - 1) / iis called the accumulation factor for n periods.
Thus: Accumulated value = Payment per period Γ Accumulation factor for n periods
π Example 1: You receive $1,000 every year for the next five years and invest each payment at 5% interest.
R= $1000i= 5% = 0.05n= 5Accumulation Factor (AF)=((1 + 0.05)^5 - 1) / 0.05=[1.27628 - 1] / 0.05=5.53S= $1,000 Γ 5.53 = $5,525.63 Note: The formula gives a more accurate result than manually calculating and adding each yearβs future value, avoiding rounding errors.
Calculating the Present Value or discounted value of an Annuity
If you would like to determine today's value of a series of future payments, you need to use the formula that calculates the present value or discounted value of an ordinary annuity.
π Formula:
A = R * [(1 - (1 + i)^(-n)) / i]
Where:
A= Discounted or present worth of an annuityR= Cash flow per periodi= interest raten= number of payments(1 - (1 + i)^(-n)) / iis called the discount factor for n periods.
Thus: Discounted value = Payment per period Γ Discount factor for n periods
π Example 2: Using the same cash flow schedule (receiving $1,000 every year for five years at 5% interest), calculate the present value.
R= $1000i= 0.05n= 5Discount Factor (DF)=(1 - (1 + 0.05)^(-5)) / 0.05=(1 - 0.7835) / 0.05=0.2165 / 0.05=4.33A= $1,000 Γ 4.33 = $4,329.48
Notations
The following notations are used in calculations of Annuity:
R= Amount of annuityN= Number of paymentsI= Interest rate per conversion periodS= Accumulated valueA= Discounted or present worth of an annuity
Accumulated Value
The accumulated value S of an annuity is the total payments made including the interest.
- Formula:
S = R ((1+i)^n β 1)/i - Accumulation factor for n payments =
((1 + i)^n β 1) / i
The discounted or present worth of an annuity is the value in todayβs rupee value. For example, if you deposit 100 rupees and get 110 rupees after one year (10% interest), the Present Worth of 110 rupees is 100. Here 110 is the future value of 100. If 110 is invested again, it can be Rs. 121 after year 2. The present value of Rs. 121 at the end of year 2 is also 100.
Discount Factor and Discounted Value
When future value is converted into present worth, the rate at which the calculations are made is called the discount factor rate. In the previous example, 10% was used. This rate is called the Discount Rate. The present worth of future payments is called Discounted Value.
Example 1. Accumulation Factor (AF) for n Payments
Calculate Accumulation Factor and Accumulated value when:
i= 4.25% = 0.0425n= 18R= 10,000 Rs.AF=((1 + 0.0425)^18 - 1) / 0.0425= 26.24S= 10,000 Γ 26.24 = 260,240 Rs.
Example 2. Discounted Value (DV)
In the above example, calculate the discounted (present) value.
i= 4.25% = 0.0425n= 18R= 10,000 Rs.DF=(1 - 1/(1+0.0425)^18) / 0.0425= 12.4059- Discounted value = 10,000 Γ 12.4059 = 124,059 Rs.
Example 3. Discounted Value (DV)
How much money deposited now will provide payments of Rs. 2000 at the end of each half-year for 10 years if interest is 11% compounded six-monthly?
R= 2000 Rs.i= 11% / 2 = 0.055n= 10 Γ 2 = 20DF=(1 - 1/(1+0.055)^20) / 0.055= 11.95- Discounted value = 2,000 Γ 11.95 = 23,900.77 Rs.
Algebraic Operations
An 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. In the expression 2xΒ² β 3x - 1, the components are separated by minus signs.
In algebraic expressions, there are four types of terms:
- Monomial: 1 term (e.g.,
3xΒ²) - Binomial: 2 terms (e.g.,
3xΒ² + xy) - Trinomial: 3 terms (e.g.,
3xΒ² + xy - 6yΒ²) - Polynomial: more than 1 term (Binomial and trinomial examples are also polynomials)
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 (e.g.,
xy = x*y). - Division is assumed when one factor is written under another.
Factors can be further subdivided into NUMERICAL and LITERAL coefficients.
Division by a Monomial
There are two steps:
- Identify factors in the numerator and denominator.
- Cancel factors in the numerator and denominator.
π Example: 36xΒ²y / 60xyΒ²
36=3 Γ 12,60=5 Γ 12xΒ²y=(x)(x)(y),xyΒ²=(x)(y)(y)- Expression becomes:
[3 Γ 12 Γ (x)(x)(y)] / [5 Γ 12 Γ (x)(y)(y)] 12(x)(y)cancel, leaving3(x) / 5(y)
π Example: (48aΒ² β 32ab) / 8a
Steps:
- Divide each term in the numerator by the denominator.
- Cancel factors.
48aΒ² / 8a=[8 Γ 6 Γ (a)(a)] / [8a]=6(a)32ab / 8a=[4 Γ 8 Γ (a)(b)] / [8(a)]=4(b)- Answer =
6a β 4b
How to Multiply Polynomials?
π Example: -x(2xΒ² β 3x - 1)
Each term in the trinomial is multiplied by -x.
= (-x)(2xΒ²) + (-x)(-3x) + (-x)(-1)- Note: The product of two negatives is positive.
= -2xΒ³ + 3xΒ² + x
Exponents
An exponent of a term means calculating some power of that term.
π Example: (3xβΆyΒ³ / xΒ²zΒ³)Β²
Steps:
- Simplify inside the brackets first:
(3xβΆβ»Β²)(yΒ³)/zΒ³ = (3xβ΄)(yΒ³)/zΒ³ - Square each factor:
(3Β²)(xβ΄*Β²)(yΒ³*Β²) / zΒ³*Β² - Simplify:
9xβΈyβΆ / zβΆ
Linear Equation
If there is an expression A + 9 = 137, we can calculate the value of A by moving the 9 to the right of the equality: A = 137 β 9 = 128.
To solve linear equations:
- Collect like terms.
- Divide both sides by the numerical coefficient.
π Example: x = 341.25 + 0.025x
Step 1:
x β 0.025x = 341.25x(1 - 0.025) = 341.250.975x = 341.25Step 2:x = 341.25 / 0.975 = 350
β Key Takeaways
- An annuity is a series of fixed payments, and its value can be calculated as an accumulated value (future value) or a discounted value (present value). The accumulation factor
((1+i)^n - 1)/iand the discount factor(1 - (1+i)^(-n))/iare the core tools for these calculations, with the total value being the payment per period multiplied by the respective factor. The fundamental algebraic operations of division by monomials, multiplying polynomials, and simplifying exponents are essential for manipulating these and other mathematical formulas. Finally, solving a linear equation likex = a + bxrequires collecting like terms and then dividing by the coefficient ofx, a process critical for solving for unknown variables in business problems.
π§ Quick Revision Questions
- What is the definition of an annuity, and what are the most common payment frequencies?
- What is the formula for the accumulated value (S) of an ordinary annuity, and what is the component
((1+i)^n - 1)/icalled? - What is the formula for the discounted value (A) of an ordinary annuity, and what is the component
(1 - (1+i)^(-n))/icalled? - Simplify the algebraic expression:
(24xΒ³yΒ²) / (8xΒ²y). - Solve the linear equation for x:
5x + 10 = 2x + 25.
π Lecture 8 β Compound Interest
π Overview: This lecture introduces compound interest concepts and a comprehensive suite of Excel financial functions for investment analysis. It covers how to calculate returns from investments, annuities, and use specialized Excel functions to determine cumulative interest, principal payments, future values, and net present values. Mastering these tools is essential for real-world financial decision-making in banking, loans, and investments.
ποΈ Topics Covered
The lecture reviews compound interest fundamentals, then systematically explains and demonstrates over a dozen Excel financial functions: CUMIPMT, CUMPRINC, EFFECT, FV, FVSCHEDULE, IPMT, ISPMT, NOMINAL, NPER, NPV, PMT, PPMT, PV, and RATE. Each function is defined with its syntax, and most include practical examples with complete numerical values showing how to calculate loan interest, investment growth, payment amounts, and more.
π Lecture Summary
Review of Lecture 7
The lecture begins with a brief review of previous material before diving into new compound interest applications and Excel functions. The focus shifts from basic calculations to using spreadsheet tools for complex financial analysis.
Compound Interest
Compound interest is interest calculated on the initial principal and also on the accumulated interest from previous periods. It allows investments to grow exponentially over time, as earnings generate additional earnings.
π‘ Why this matters: Compound interest is the foundation of all modern finance β it determines how loans accumulate debt and how investments grow over time.
CUMIPMT
Returns the cumulative interest paid on a loan between start_period and end_period.
π Definition β CUMIPMT: A function that calculates total interest paid over a specified range of payment periods.
π Formula: CUMIPMT(rate, nper, pv, start_period, end_period, type)
β Calculates total interest paid from one period to another.
π Example: For a 9% annual rate, 30-year loan of 125,000 with monthly payments:
- Total interest in second year (periods 13-24):
=CUMIPMT(9%/12, 30*12, 125000, 13, 24, 0)β (-11135.23) - Interest in first single payment (period 1):
=CUMIPMT(9%/12, 30*12, 125000, 1, 1, 0)β (-937.50)
Type=0 means payment at end of period.
CUMPRINC
Returns the cumulative principal paid on a loan between two periods.
π Definition β CUMPRINC: A function that calculates total principal paid over a specified range of payment periods.
π Formula: CUMPRINC(rate, nper, pv, start_period, end_period, type)
β Calculates total principal repaid from one period to another.
π Example: Same loan (9%, 30 years, 125,000):
- Total principal in second year (periods 13-24):
=CUMPRINC(9%/12, 30*12, 125000, 13, 24, 0)β (-934.1071) - Principal in first single payment:
=CUMPRINC(9%/12, 30*12, 125000, 1, 1, 0)β (-68.27827)
EFFECT
Returns the effective annual interest rate given a nominal rate and number of compounding periods per year.
π Definition β EFFECT: Converts a nominal (stated) interest rate to the actual annual rate earned after compounding.
π Formula: EFFECT(nominal_rate, npery)
β Shows true annual yield when interest compounds multiple times per year.
π Example: Nominal_rate = 5.25%, Npery = 4 (quarterly compounding):
=EFFECT(5.25%, 4) β 0.053543 or 5.3543% (round to 5.35%)
FV
Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
π Definition β FV: Calculates what an investment or series of payments will be worth at a future date.
π Formula: FV(rate, nper, pmt, pv, type)
β Determines total accumulated value including principal, payments, and compound interest.
π Example 1: Rate=6%, Nper=10, Pmt=-200, Pv=-500, Type=1 β 2581.40
π Example 2: Rate=12%, Nper=12, Pmt=-1000 (no Pv/Type) β 12682.50
π Example 3: Rate=11%, Nper=35, Pmt=-2000, Type=1 (Pv omitted with double commas) β 82846.25
FVSCHEDULE
Returns the future value of an initial principal after applying a series of compound interest rates.
π Definition β FVSCHEDULE: Calculates growth when interest rates vary each period.
π Formula: FVSCHEDULE(principal, schedule)
β Applies different compound rates sequentially to a starting principal.
π Example: Principal=1, Rates={0.09, 0.11, 0.1}:
=FVSCHEDULE(1, {0.09, 0.11, 0.1}) β 1.33089
IPMT
Returns the interest payment for an investment for a given period.
π Formula: IPMT(rate, per, nper, pv, fv, type)
β Calculates the interest portion of a payment in a specific period.
ISPMT
Calculates the interest paid during a specific period of an investment (simpler version of IPMT).
π Formula: ISPMT(rate, per, nper, pv)
β For a loan, pv is the loan amount.
NOMINAL
Returns the annual nominal interest rate given the effective rate and number of compounding periods.
π Formula: NOMINAL(effect_rate, npery)
β Converts an effective annual rate back to its nominal (stated) equivalent.
NPER
Returns the number of periods for an investment.
π Formula: NPER(rate, pmt, pv, fv, type)
β Tells how many payments/periods needed to reach a financial goal.
NPV
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.
π Formula: NPV(rate, value1, value2, ...)
β Determines whether an investment is worthwhile in today's money.
PMT
Returns the periodic payment for an annuity.
π Formula: PMT(rate, nper, pv, fv, type)
β Calculates the fixed payment amount needed to pay off a loan or reach a future value.
PPMT
Returns the payment on the principal for an investment for a given period.
π Formula: PPMT(rate, per, nper, pv, fv, type)
β Shows how much of a specific payment goes toward reducing principal.
PV
Returns the present value of an investment.
π Formula: PV(rate, nper, pmt, fv, type)
β Determines what a future sum or series of payments is worth today.
RATE
Returns the interest rate per period of an annuity.
π Formula: RATE(nper, pmt, pv, fv, type, guess)
β Solves for the interest rate given payment, periods, and present value.
π Example: 4 years loan, -200 monthly payment, 8000 loan amount:
=RATE(4, -200, 8000) β 0.09241767 or 9.24%
β Key Takeaways
Students must memorize the syntax and purpose of all major Excel financial functions covered: CUMIPMT, CUMPRINC, EFFECT, FV, FVSCHEDULE, IPMT, ISPMT, NOMINAL, NPER, NPV, PMT, PPMT, PV, and RATE. The most critical concept is that negative signs typically represent cash outflows (payments) while positive signs represent inflows (received amounts). Compound interest calculations require careful conversion of annual rates to periodic rates (divide by periods per year) and annual terms to total periods (multiply by periods per year). The difference between IPMT (interest for a specific period) and ISPMT (simpler version) as well as between FV (constant rate) and FVSCHEDULE (variable rates) is essential. Finally, understanding that CUMIPMT and CUMPRINC work over ranges of periods while PMT/PPMT/IPMT work for single periods is fundamental for exam problems.
π§ Quick Revision Questions
- What is the syntax and purpose of the CUMIPMT function? What does the Type argument of 0 versus 1 mean?
- How do you calculate the total principal paid in the first year of a 30-year loan using CUMPRINC?
- If a nominal interest rate is 5.25% compounded quarterly, what is the effective annual rate (EFFECT)?
- Using the FV function, what inputs are needed and what does a negative sign on Pmt or Pv indicate?
- How does the RATE function work, and what was the result (as a percentage) in the example with a 4-year, 8000 loan with 200 monthly payments?
π Lecture 9 β Matrix and its dimension Types of matrix
π Overview: This lecture introduces the fundamental concept of matrices, which are rectangular arrays of numbers used extensively in business, economics, and data processing. It covers matrix dimensions, various types of matrices including row, column, square, and identity matrices, and explains the multiplicative identity property of matrices.
ποΈ Topics Covered
The lecture begins with reviewing the importance and applications of matrices in business contexts such as econometrics, network analysis, and linear programming. It then defines what a matrix is and demonstrates how tabular data can be represented as a matrix. The lecture proceeds to explain matrix dimension (order), different types of matrices (row, column, square), and concludes with a detailed examination of identity matrices and their multiplicative identity property with worked examples.
π Lecture Summary
Review and Applications
Matrices have many important applications in business and industry where large amounts of data are processed daily. Practical questions in modern business and economic management can be answered with matrix representation in fields such as econometrics, network analysis, decision networks, optimization, linear programming, analysis of data, and 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. The numbers in a matrix are often arranged in a meaningful way. For example, the order for school clothing in September is illustrated in a table with rows representing clothing types (Sweat Pants, Sweat Shirts, Shorts, T-shirts) and columns representing sizes (Youth S, M, L, XL). This data can be entered in the shape of a matrix.
Dimension
Dimension or Order of a Matrix equals Number of Rows Γ Number of Columns. The 'Γ' is just notation and does not mean to multiply.
π Definition β Dimension of a Matrix: The size of a matrix given by rows Γ columns, written as r Γ c where r is the number of rows and c is the number of columns.
π Example: Matrix T has dimensions of 2Γ3 or the order of matrix T is 2Γ3.
Row, Column and Square Matrix
A matrix with dimensions 1Γn is referred to as a row matrix. For example, matrix A is a 1Γ4 row matrix. A matrix with dimensions nΓ1 is referred to as a column matrix. For example, matrix B is a 2Γ1 column matrix. A matrix with dimensions nΓn is referred to as a square matrix. For example, matrix C is a 3Γ3 square matrix.
π Definition β Row Matrix: A matrix with exactly one row and n columns (1Γn dimension). π Definition β Column Matrix: A matrix with exactly one column and n rows (nΓ1 dimension). π Definition β Square Matrix: A matrix with equal number of rows and columns (nΓn dimension).
Identity Matrix
An identity matrix is a square matrix with 1's on the main diagonal from the upper left to the lower right and 0's off the main diagonal. An identity matrix is denoted as I. The subscript indicates the size of the identity matrix. For example, Iβ represents an identity matrix with dimensions nΓn.
π Definition β Identity Matrix (I): A square matrix with 1s on the main diagonal and 0s elsewhere.
Multiplicative Identity
With real numbers, the number 1 is referred to as a multiplicative identity because it has the unique property that the product of a real number and 1 is that real number. With matrices, the identity matrix shares the same unique property. In other words, a 2Γ2 identity matrix is a multiplicative inverse because for any 2Γ2 matrix A, IΒ·A = A and AΒ·I = A.
π Definition β Multiplicative Identity Property: For any matrix A and identity matrix I of compatible dimensions, AΒ·I = A and IΒ·A = A.
π Example: Given the 2Γ2 matrix A = [[2, -1], [-3, 4]] and identity matrix I = [[1, 0], [0, 1]]:
IΒ·A = [[1,0],[0,1]] Β· [[2,-1],[-3,4]] = [[2,-1],[-3,4]] = A
Work: r1c1 = 1(2) + 0(-3) = 2, r1c2 = 1(-1) + 0(4) = -1, r2c1 = 0(2) + 1(-3) = -3, r2c2 = 0(-1) + 1(4) = 4
AΒ·I = [[2,-1],[-3,4]] Β· [[1,0],[0,1]] = [[2,-1],[-3,4]] = A
Work: r1c1 = 2(1) + -1(0) = 2, r1c2 = 2(0) + -1(1) = -1, r2c1 = -3(1) + 4(0) = -3, r2c2 = -3(0) + 4(1) = 4
Where 'r' denotes row and 'c' denotes column.
π‘ Why this matters: The identity matrix is essential for solving matrix equations and finding matrix inverses, which are fundamental operations in solving systems of linear equations in business applications.
β Key Takeaways
The most critical concepts from this lecture are: a matrix is a rectangular array of numbers with dimension given by rows Γ columns; there are three main types based on dimensions β row matrices (1Γn), column matrices (nΓ1), and square matrices (nΓn); the identity matrix is a special square matrix with 1s on the main diagonal and 0s elsewhere; and the identity matrix serves as the multiplicative identity for matrices, meaning any matrix multiplied by the identity matrix remains unchanged. These fundamental concepts form the foundation for all matrix operations used in business mathematics and statistics.
π§ Quick Revision Questions
- What is the definition of a matrix and how is its dimension expressed?
- What are the three main types of matrices based on their dimensions? Provide an example of each.
- What is an identity matrix and what are its defining characteristics?
- What is the multiplicative identity property for matrices? How does it relate to the identity matrix?
- Given matrix A = [[2, -1], [-3, 4]] and identity matrix I = [[1,0],[0,1]], verify that AΒ·I = A by showing the multiplication steps.
π Lecture 10 β Matrices
π Overview: This lecture introduces matrices as a tool for organizing and interpreting business data, with a focus on how matrices can represent real-world scenarios such as university clothing orders and company sales. It covers fundamental matrix operations including addition, subtraction, scalar multiplication, matrix multiplication, and multiplicative inverses, all essential for business applications.
ποΈ Topics Covered
The lecture begins with a review of previous content, then introduces matrices through a real example of a clothing company supplying two universities. It covers the organization and interpretation of data using matrices, matrix addition and subtraction, scalar multiplication, matrix multiplication including the rules for when multiplication is possible, and concludes with the concept of multiplicative inverses for matrices.
π Lecture Summary
EXAMPLE 1
An athletic clothing company manufactures T-shirts and sweatshirts in four sizes (small, medium, large, x-large) for two universities: University of S and University of R. The September orders are shown as tables, which can be represented as matrices. For University of S, the order matrix S has T-shirts: [100, 300, 500, 300] and sweatshirts: [150, 400, 450, 250] across the four sizes. For University of R, the order matrix R has T-shirts: [60, 250, 400, 250] and sweatshirts: [100, 200, 350, 200].
π Definition β Matrix: A rectangular array of numbers organized in rows and columns.
MATRIX OPERATIONS
Matrix operations include organizing and interpreting data using matrices, using them in business applications, adding and subtracting two matrices, multiplying a matrix by a scalar, and multiplying two matrices. The goal includes interpreting the meaning of elements within a product matrix.
PRODUCTION
The company's production in preparation for the September orders is shown in matrix P. For T-shirts: [300, 700, 900, 500] and for sweatshirts: [300, 700, 900, 500] across sizes S, M, L, XL.
ADDITION AND SUBTRACTION OF 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: To calculate total orders for both universities, add corresponding elements of matrices S and R. For small T-shirts: 100 (U of S) + 60 (U of R) = 160 total. The full sum yields total requirements.
OVERPRODUCTION: To determine over-production, subtract the total order matrix from the production matrix P. For small T-shirts: 300 (produced) - 160 (ordered) = 140 over-produced.
π Example: Over-production calculation for small T-shirts: Production = 300, Total orders = 100 + 60 = 160, Over-production = 300 - 160 = 140.
MULTIPLY A MATRIX BY A SCALAR
Given a matrix A and a number c, the scalar multiplication cA is computed by multiplying the scalar c by every element of A.
MULTIPLICATION OF MATRICES
Consider two competing companies, A and B, selling juice in 591 mL, 1 L, and 2 L bottles at prices of Rs. 1.60, Rs. 2.30, and Rs. 3.10, respectively. Sales for July are: Company A: [20,000, 5,500, 10,600] and Company B: [18,250, 7,000, 11,000] for the three bottle sizes. Total revenue is found by multiplying the sales matrix by the price matrix.
The sales data forms a 2Γ3 matrix S, prices form a column matrix P, and total revenue forms a column matrix R. Revenue is calculated by multiplying number of sales by selling price. The product of a row and a column is defined as the number obtained by multiplying corresponding entries (first by first, second by second, etc.) and adding the results.
π Definition β Matrix multiplication: If matrix A is an mΓn matrix and matrix B is an nΓp matrix, then the product AB is the mΓp matrix whose entry in the i-th row and j-th column is the product of the i-th row of A and the j-th column of B.
π Formula: For matrix multiplication, number of columns of A must equal number of rows of B. The resulting matrix has dimensions (rows of A) Γ (columns of B).
π Example: Company A's total revenue = (20,000 Γ 1.60) + (5,500 Γ 2.30) + (10,600 Γ 3.10) = sum of products. Company B's total revenue = (18,250 Γ 1.60) + (7,000 Γ 2.30) + (11,000 Γ 3.10).
MULTIPLICATION CHECKS: For product AB with A = 3Γ3 and B = 3Γ2, the product exists because inner dimensions match (# columns of A = # rows of B = 3), and the product matrix is 3Γ2. For product BA with B = 3Γ2 and A = 3Γ3, the product does NOT exist because inner dimensions do not match (# columns of B = 2 β # rows of A = 3).
π‘ Why this matters: Understanding when matrix multiplication is possible is critical for business calculations like revenue, cost, and production planning.
MULTIPLICATIVE INVERSES
Real Numbers: Two non-zero real numbers are multiplicative inverses of each other if their product, in both orders, is 1. The multiplicative inverse of a real number x is 1/x, since x Γ (1/x) = 1 and (1/x) Γ x = 1.
π Example: The multiplicative inverse of 5 is 1/5, since 5 Γ (1/5) = 1 and (1/5) Γ 5 = 1.
Matrices: Two 2Γ2 matrices are inverses of each other if their products, in both orders, is a 2Γ2 identity matrix (a matrix with 1s on the main diagonal and 0s elsewhere). The multiplicative inverse of a 2Γ2 matrix A is denoted Aβ»ΒΉ.
π Definition β Identity matrix: A square matrix with 1s on the main diagonal (from top-left to bottom-right) and 0s everywhere else.
π Example: The multiplicative inverse of matrix [2 1; 5 3] is matrix [3 -1; -5 2]. Their product in both orders equals the identity matrix [1 0; 0 1].
β Key Takeaways
A matrix is a rectangular arrangement of data in rows and columns, useful for organizing business information like orders, production, and sales. Matrix addition and subtraction require matrices of the same dimensions, with operations performed on corresponding elements. Matrix multiplication requires the number of columns in the first matrix to equal the number of rows in the second, and the resulting product yields a matrix where each element is the sum of products of corresponding row and column entries. Scalar multiplication involves multiplying every element of a matrix by a constant. The multiplicative inverse of a matrix, when it exists, produces the identity matrix when multiplied in either order, analogous to the reciprocal of a real number.
π§ Quick Revision Questions
- What condition must be satisfied for two matrices to be added or subtracted?
- How is scalar multiplication of a matrix performed?
- What is the rule for determining whether matrix multiplication AB is possible, and what are the dimensions of the resulting product?
- If matrix A is 2Γ3 and matrix B is 3Γ4, what is the dimension of the product AB?
- What is the identity matrix for a 2Γ2 matrix, and what property does it have with respect to multiplication?
π Lecture 11 β Matrices
π Overview: This lecture covers three key matrix functions in Microsoft Excel β MINVERSE, MDETERM, and MMULT β along with the practical applications of ratios and proportions. Understanding these topics is essential for solving systems of equations, performing matrix algebra in business contexts, and allocating resources proportionally.
ποΈ Topics Covered
The lecture begins by reviewing three Microsoft Excel matrix functions: MINVERSE (for finding the inverse of a matrix), MDETERM (for calculating the determinant of a matrix), and MMULT (for multiplying two matrices). It then introduces the concept of ratios as comparisons between quantities, followed by proportions as equations stating that two ratios are equal. Finally, it demonstrates how to use ratios and proportions for estimating unknown quantities in real-world business scenarios.
π Lecture Summary
Matrix Functions in MS Excel
The three main matrix functions in Microsoft Excel are MINVERSE, MDETERM, and MMULT. These functions allow users to perform advanced matrix operations directly in spreadsheets.
MINVERSE
MINVERSE returns the inverse matrix for a matrix stored in an array. The function requires a numeric array with an equal number of rows and columns (a square matrix).
π Definition β MINVERSE(array): Returns the inverse of a square matrix stored in a cell range or array constant.
π Formula: For a 2Γ2 matrix [a b; c d], the inverse is: MINVERSE = [d/(ad-bc) b/(bc-ad); c/(bc-ad) a/(ad-bc)]
π Example: Find the multiplicative inverse of matrix [4 -1; 2 0]
- Enter data in cells A4:B5 (4, -1 in row 1; 2, 0 in row 2)
- Select cells A6:B7 (four cells for the result)
- Press F2, type =MINVERSE(A4:B5)
- Press Ctrl+Shift+Enter simultaneously
- The inverse appears in cells A6:B7
π‘ Why this matters: The product of a matrix and its inverse yields the identity matrix (diagonal = 1, all others = 0). This is fundamental for solving systems of linear equations.
Remarks:
- Array can be a cell range (A1:C3), array constant ({1,2,3;4,5,6;7,8,9}), or a name
- Empty cells or text return #VALUE! error
- Non-square matrices return #VALUE! error
- Formulas returning arrays must be entered as array formulas (Ctrl+Shift+Enter)
- Accuracy is approximately 16 digits
- Non-invertible matrices (determinant = 0) return #NUM! error
MDETERM
MDETERM returns the matrix determinant of an array. The determinant is a single number derived from the values in a square array.
π Definition β MDETERM(array): Returns the determinant (a scalar value) of a square matrix.
π Formula: For a 3Γ3 matrix A1:C3: MDETERM(A1:C3) = A1*(B2C3-B3C2) + A2*(B3C1-B1C3) + A3*(B1C2-B2C1)
π Example: For a 4Γ4 matrix in cells A14:D17, MDETERM returns 88.
Additional examples:
- =MDETERM({3,6,1;1,1,0;3,10,2}) returns 1
- =MDETERM({3,6;1,1}) returns -3
- =MDETERM({1,3,8,5;1,3,6,1}) returns #VALUE! (not square)
β οΈ Note: Determinants are used for solving systems of equations. A singular matrix (determinant = 0) cannot be inverted.
MMULT
MMULT returns the matrix product of two arrays. The result has the same number of rows as array1 and the same number of columns as array2.
π Definition β MMULT(array1, array2): Returns the product of two matrices as an array.
π Formula: The element at row i, column j of the product is: aα΅’β±Ό = Ξ£(bα΅’β Γ cββ±Ό) where k is the common dimension
π Example: Find AB where A = [1 3; 7 2] and B = [2 0; 0 2]
- Enter array1 (A) in cells A25:B26 and array2 (B) in D25:E26
- Select cells A29:B30 (2Γ2 result)
- Press F2, type =MMULT(A25:B26, D25:E26)
- Press Ctrl+Shift+Enter
- Product AB appears in A29:B30
β οΈ Rule: The number of columns in array1 must equal the number of rows in array2. Both arrays must contain only numbers. Empty cells or text return #VALUE! error.
Ratio
A ratio is a comparison between things. It is written as a:b and read as "a is to b." The order matters β a ratio of 2:1 is not the same as 1:2.
π Definition β Ratio: A comparison between two or more quantities, expressed as a:b.
π Calculation method:
- Find the minimum value among all quantities
- Divide all values by the smallest value
π Example: Three friends invest β Ali Rs. 7,800, Fawad Rs. 5,200, Tanveer Rs. 6,500
- Smallest value = 5,200
- Ali: 7,800/5,200 = 1.5
- Fawad: 5,200/5,200 = 1
- Tanveer: 6,500/5,200 = 1.25
- Ratio = 1.5 : 1 : 1.25
π‘ Why this matters: Ratios allow comparison of different quantities on a common scale, essential for financial analysis, resource allocation, and recipe scaling.
Proportion
A proportion is an equation that states two ratios are equal. For example, 3:4 = 6:8 or 3/4 = 6/8. When one number is unknown, cross-multiplication is used to find it.
π Definition β Proportion: An equation stating that two ratios are equal.
π Formula: a:b = c:d β a/b = c/d β a Γ d = b Γ c (cross-multiplication)
π Example 1: Sales ratio of Product X to Y is 4:3. If X sales are forecast at Rs. 180,000, find Y sales.
- 180,000 : Y = 4 : 3
- 180,000/Y = 4/3
- Cross-multiply: 180,000 Γ 3 = 4 Γ Y
- 4Y = 540,000
- Y = 135,000 Rs.
Excel formula: =B71/B70*D70 where B70=4 (ratio X), B71=3 (ratio Y), D70=180,000 (sales X)
π Example 2: Hospital expansion β 500 beds to 100 beds. Currently 200 nurses, 150 other staff.
- Nurses needed: Nβ = (100/500) Γ 200 = 40
- Other staff needed: Oβ = (100/500) Γ 150 = 30
π Example 3: Fruit punch recipe β mango:apple:orange = 3:2:1. Find quantities for 2 liters.
- Total parts = 3+2+1 = 6
- Mango: (3/6) Γ 2 = 1.0 liter
- Apple: (2/6) Γ 2 = 0.67 liter
- Orange: (1/6) Γ 2 = 0.33 liter
Excel formulas: Mango = B20/B23D23, Apple = B21/B23D23, Orange = B22/B23*D23
β Key Takeaways
The three essential Excel matrix functions are MINVERSE (for inverses), MDETERM (for determinants), and MMULT (for multiplication) β all must be entered as array formulas using Ctrl+Shift+Enter. A ratio compares quantities by dividing each by the smallest value, and order matters (2:1 β 1:2). A proportion equates two ratios and is solved using cross-multiplication. For estimating unknown quantities, use the ratio of the unknown to the known (e.g., new_value = (ratio_unknown/ratio_known) Γ known_value). Matrix functions are critical for solving systems of equations, while ratios and proportions are fundamental for resource allocation, recipe scaling, and financial forecasting.
π§ Quick Revision Questions
- What keyboard shortcut is required to enter an array formula in Excel, and why is it necessary for MINVERSE, MDETERM, and MMULT?
- How do you calculate the multiplicative inverse of a 2Γ2 matrix manually, and what condition makes a matrix non-invertible?
- A hospital has 800 beds, 320 nurses, and 240 other staff. If it expands by 200 beds, how many additional nurses and other staff are needed?
- Three partners invest Rs. 12,000, Rs. 8,000, and Rs. 10,000. What is the ratio of their investments in simplest form?
- If the sales ratio of two products is 5:2 and the first product's sales are Rs. 250,000, what must the second product's sales be to maintain this ratio?
π Lecture 12 β Ratio and Proportion / Merchandising
π Overview: This lecture introduces the concepts of ratio and proportion, demonstrating how to estimate unknown quantities when only partial data is available. It then transitions into merchandising, covering trade discounts, list prices, and the roles of stakeholders in the distribution chain. Understanding these concepts is essential for solving real-world business and pricing problems.
ποΈ Topics Covered
The lecture covers estimating using ratios with two detailed examples, proportion and its fundamental property, solving for unknown values in proportions, and an introduction to merchandising including stakeholders, list price, and trade discounts. Excel calculations are also demonstrated for ratio problems.
π Lecture Summary
Estimating Using Ratios-Example 1
When only one quantity is known from a ratio, we can estimate the total quantity. The method uses the ratio of the unknown to the known quantity. For a punch recipe with a ratio of mango juice, apple juice, and orange juice at 3:2:1, if you have 1.5 liters of orange juice, you can calculate the other quantities. Using the ratio, mango juice = (3/1) Γ 1.5 = 4.5 liters, apple juice = (2/1) Γ 1.5 = 3.0 liters, and total punch = 4.5 + 3.0 + 1.5 = 9 liters.
π Definition β Ratio: A comparison of two or more quantities, often expressed as a:b or a/b, indicating their relative sizes. π Formula: Unknown Quantity = (Ratio of Unknown / Ratio of Known) Γ Known Quantity β This allows finding missing values when one quantity is known. π Example: Punch recipe ratio 3:2:1, with 1.5 L orange juice. Mango = (3/1)Γ1.5 = 4.5 L, Apple = (2/1)Γ1.5 = 3.0 L. Total punch = 4.5 + 3.0 + 1.5 = 9 L.
Estimating Using Ratios-Example 2
This example uses a ratio with a decimal value. The punch ratio is 3:2:1.5, and you have 500 milliliters of orange juice. Mango juice = (3/1.5) Γ 500 = 1000 liters, apple juice = (2/1.5) Γ 500 = 667 liters, and total punch = 1000 + 667 + 500 = 2167 liters. The Excel formula used is Mango juice = B45/B47*D47.
π Formula: For ratio a:b:c with known c, total quantity = (a/c)Γknown + (b/c)Γknown + known. π Example: Ratio 3:2:1.5, orange juice = 500 L. Mango = (3/1.5)Γ500 = 1000 L, Apple = (2/1.5)Γ500 = 667 L. Total = 2167 L.
Example
In a class, the ratio of passing to failing grades is 7:5. Out of 36 students, the proportion who failed is 5/12 (since 7+5=12 total parts). Then (5/12) Γ 36 = 15 students failed.
π Definition β Proportion: An equation stating that two ratios are equal, written as a/b = c/d. π Formula: (5/12) Γ 36 = 15 β Multiply the fraction representing the desired category by the total number. π Example: Ratio 7:5, total students 36. Failed students = (5/(7+5)) Γ 36 = (5/12) Γ 36 = 15.
Proportion
In a proportion a/b = c/d, the values in the "b" and "c" positions are called the means, while the values in the "a" and "d" positions are called the extremes. The fundamental property is that the product of the means equals the product of the extremes: ad = bc.
π Definition β Means: In a proportion a/b = c/d, the middle terms (b and c) are called the means. π Definition β Extremes: In a proportion a/b = c/d, the outer terms (a and d) are called the extremes. π Formula: ad = bc β The product of the extremes equals the product of the means. π‘ Why this matters: This property allows us to verify if two ratios form a proportion and to solve for unknown values.
Proportion-Examples
To check if 24/140 is proportional to 30/176, cross multiply: 140Γ30 = 4200 and 24Γ176 = 4224. Since 4200 β 4224, the ratios are not proportional.
π Example: Check 24/140 = 30/176. Cross multiply: 140Γ30 = 4200, 24Γ176 = 4224. Not proportional (4200 β 4224).
Proportion Example 1
Find the unknown value in 2:x = 3:9. Convert to fractions: 2/x = 3/9. Cross multiply: 2Γ9 = 3x β 18 = 3x β x = 6.
π Example: 2:x = 3:9 β 2/x = 3/9 β 18 = 3x β x = 6.
Proportion Example 2
Find the unknown in (2x+1):2 = (x+2):5. Convert to fractions: (2x+1)/2 = (x+2)/5. Cross multiply: 5(2x+1) = 2(x+2) β 10x + 5 = 2x + 4 β 8x = -1 β x = -1/8.
π Example: (2x+1):2 = (x+2):5 β (2x+1)/2 = (x+2)/5 β 10x+5 = 2x+4 β 8x = -1 β x = -1/8.
Merchandising
Merchandising covers understanding ordinary dating notation for invoice payment terms, solving pricing problems involving markups and markdowns, calculating net price after single or multiple trade discounts, calculating equivalent single discount rates, and calculating cash discount amounts.
π Definition β Merchandising: The activities involved in promoting and selling products, including pricing, discounts, and payment terms.
Stakeholders in Merchandising
The main stakeholders are the manufacturer, middleman, retailer, and consumer. Discounts occur at all levels in this chain. A middleman buys products from the manufacturer and sells either at retail prices to the public or at wholesale prices to a distributor.
π Definition β Middleman: A person who buys products from the manufacturer and sells them to retailers or consumers, serving as an intermediary in the distribution chain.
List Price or Retail Price
List price is the manufacturer's suggested retail price. It depends on the product itself, the built-in profit margin, and supply and demand. Resellers buy products in bulk at a substantial discount to make a profit when selling at or below list price.
π Definition β List Price: The manufacturer's suggested retail price for a product, from which discounts are often given.
Trade Discount
Trade discount is a percentage reduction from the list price given to resellers. If L is the list price and d is the discount percentage, then amount of discount = d Γ L, and net price = L - Ld = L(1-d).
π Definition β Trade Discount: A reduction from the list price offered to buyers in the distribution chain, expressed as a percentage of the list price. π Formula: Net Price = L(1-d) β List price multiplied by (1 minus the discount rate) gives the final price after discount. π Formula: Amount of Discount = d Γ L β The discount rate multiplied by the list price gives the dollar amount of the discount. π Example: If list price is $100 and trade discount is 20%, net price = 100(1-0.20) = $80, and discount amount = 0.20 Γ 100 = $20.
β Key Takeaways
Ratios allow estimating unknown quantities when only one part is known by using the proportion of each part relative to the known part. A proportion is an equality of two ratios, with the fundamental property that the product of means equals the product of extremes. In merchandising, the list price is the suggested retail price from which trade discounts are subtracted to arrive at the net price. The main stakeholders in merchandising include manufacturers, middlemen, retailers, and consumers. The net price formula is L(1-d), where L is list price and d is the discount rate.
π§ Quick Revision Questions
- In a punch recipe with ratio 3:2:1, if you have 2 liters of mango juice, how much punch can you make?
- Are 15/25 and 9/15 proportional? Verify using the means-extremes property.
- Solve for x in the proportion: (3x + 1):4 = (2x - 1):3.
- What is the net price of an item with a list price of $500 and a trade discount of 30%?
- What is the amount of trade discount if the list price is $250 and the discount rate is 15%?