π Lecture 13 β Mathematics of Merchandising
π Overview: This lecture covers the mathematics behind retail pricing, focusing on how markup and markdown are used to determine selling prices. It explains how to calculate markup as a percentage of cost or sale price, and how to compute markdowns and trade discounts, which are essential for business decision-making.
ποΈ Topics Covered
The lecture reviews markup as a percentage of cost and sale price, including calculations for Rs markup and markup rate. It then covers markdown as a percentage of current selling price and Rs markdown, followed by an introduction to trade discount, including single trade discount calculations and net price determination. Examples and Excel-based calculation methods are provided for each concept.
π Lecture Summary
MARKUP
Markup is an amount added to a cost price to calculate a selling price. It accounts for overhead and profit. Markup can be expressed as a percentage of cost, a percentage of sale, or in rupees (Rs markup).
Markup as Percentage of Cost (MUC): Here markup is some percentage of the cost price, also called %Markup on cost.
π Formula: Selling Price = Cost price + (Cost price Γ %Markup on cost) = Cost price (1 + %Markup on cost)
Markup as Percentage of Sale price (MUS): Here markup is some percentage of the selling price, also called %Markup on sale.
π Formula: Selling Price = Cost price + (Selling price Γ %Markup on sale) π Formula: Cost price = Selling price β (Selling price Γ %Markup on sale) = Selling price (1 β %Markup on sale)
Rs Markup: Markup in terms of rupees.
π Formula:
- Selling Price = Cost price + Rs Markup
- Rs Markup = %Markup on cost Γ Cost price
- Rs Markup = %Markup on sale Γ Selling price
π Example: The cost price of a certain item is 80Rs and its selling price is 100Rs. Then Rs Markup = 100 β 80 = 20 Rs.
π‘ Why this matters: If a percentage is given as markup without specifying whether it is on cost or sale, it is understood to be %markup on cost.
EAMPLE 1
A golf shop pays its wholesaler 2,400Rs for a club, and sells it for 4,500Rs. What is the markup rate?
Calculation of Markup: Cost price = 2400Rs, Selling price = 4500Rs.
Step 1: Calculate Rs markup: Rs markup = 4500 β 2400 = 2100Rs
Step 2: Calculate %Markup on cost: %Markup on cost = (Rs markup / Cost price) Γ 100% = (2100 / 2400) Γ 100% = 87.5%
The markup rate is 87.5%.
Calculation using EXCEL: Enter wholesale price 2400 in cell B5, sale price 4500 in cell B6. Enter formula for Rs. Markup =B6-B5 in cell B7 (answer 2100). Enter formula for % markup =B7/B5*100 in cell B8 (answer 87.5%).
MARKUP-EXAMPLE 2
A computer software retailer used a markup rate of 40%. Find the selling price of a computer game that cost the retailer Rs. 1,500.
Markup: The markup is 40% on cost. Rs Markup = %Markup on cost Γ Cost price = (0.40)(1,500) = Rs. 600
Selling Price: Selling Price = Cost price + Rs Markup = 1,500 + 600 = Rs. 2,100
The item sold for Rs. 2,100.
Calculation using EXCEL: Enter wholesale price 1500 in cell B17. Enter % Markup in cell B18. Enter formula =(1+B18/100)*B17 in cell B19 (answer 2100).
MARKDOWN
Markdown means a reduction from the original sale price to stimulate demand, take advantage of reduced costs, or force competitors out of the market. Markdown can be expressed as a percentage of current selling price or Rs markdown.
Markdown as Percentage of current selling price: Also called percent markdown (%markdown).
π Formula: New selling price = Current selling price β (Current selling price Γ %markdown) = Current selling price (1 - %markdown)
Rs Markdown: Markdown in terms of rupees.
π Formula:
- New selling price = Current selling price β Rs Markdown
- Rs markdown = Current selling price Γ %markdown
π Example: An item originally priced at 3,300 Rs is marked 25% off. What is the sale price?
Markdown: Rs markdown = original price Γ %markdown = (0.25)(3300) = 825Rs
Selling Price: New Selling price = 3,300 β 825 = 2,475Rs
The sale price is 2,475 Rs.
Calculation using EXCEL: Enter original price 3300 in cell B28. Enter % Markdown 25 in cell B29. Enter formula for Rs. Markdown (=B29/100*B28) in cell B30 (result 825). Enter formula for new sale price (=B28-B30) in cell B31. Alternatively, use formula (=(1-B29/100)*B28).
DISCOUNT
Discount is a reduction in price which the seller offers to the buyer. Types include: Trade discount, Cash discount, Seasonal discount, etc.
TRADE DISCOUNT
When a manufacturer or wholesaler offers goods for sale, a list price or retail price is set for each item. A discount on the list price granted by a manufacturer or wholesaler to buyers in the same trade is called trade discount. It represents a reduction in list price in return for quantity purchases.
π Formula:
- Rs Trade discount = list price Γ discount rate
- Net price = list price β Rs trade discount
Types of trade discounts: Single trade discount, Multiple or series trade discount.
SINGLE TRADE DISCOUNT-EXAMPLE 1
The price of office equipment is 3000 Rs. The manufacturer offers a 30% trade discount. Find the net price and the trade discount amount.
Discount Amount: Amount of discount = dL = 0.3 Γ 3000 = 900 Rs.
Net Price: Net Price = L(1 β d) = 3000(1 β 0.3) = 3000(0.7) = 2100 Rs.
The net price is 2100 Rs and the discount amount is 900 Rs.
Calculation using EXCEL: Enter price of equipment 3000 in cell B39. Enter % trade Discount 30 in cell B40. Enter formula for Rs. Discount =B40/100*B39 in cell B41 (result 900). Enter formula for net price =B39-B41 in cell B42 (result 2100).
β Key Takeaways
The most critical concepts from this lecture are: understanding the distinction between markup on cost versus markup on sale price, as the formulas differ (cost-based uses multiplication by 1+ rate, while sale-based uses 1-rate). Markdown is always calculated as a percentage of the current selling price, reducing it to a new lower price. Trade discount reduces the list price to a net price using a discount rate. All calculations can be performed in Excel using simple arithmetic formulas, which helps verify intermediate steps. Remember that when markup percentage is given without specification, it is assumed to be %markup on cost.
π§ Quick Revision Questions
- What is the formula for selling price when markup is given as a percentage of cost?
- If an item costs 500 Rs and is sold for 700 Rs, what is the Rs markup and the %markup on cost?
- A store offers a 15% markdown on an item originally priced at 2,000 Rs. What is the new selling price?
- What is the difference between a trade discount and a markdown?
- If a product has a list price of 5,000 Rs and a 20% trade discount is applied, what is the net price?
π Lecture 14 β Mathematics of Merchandising Part 2
π Overview: This lecture continues the study of merchandising mathematics by focusing on series trade discounts. It explains how multiple discounts are applied sequentially to a list price, how to calculate single equivalent discount rates, and introduces cash discounts for prompt payment. Understanding these concepts is essential for pricing, purchasing, and financial decision-making in business.
ποΈ Topics Covered
The lecture begins with a review of trade discounts from Lecture 13, then introduces series trade discounts and their calculation method. It covers finding net price from list price using multiple discount rates, computing list price from net price, calculating single equivalent discount rates, and understanding cash discount terms like "2/10, net/30". Excel applications are demonstrated for each calculation.
π Lecture Summary
SERIES TRADE DISCOUNT
This refers to giving further discounts as incentives for more sales, usually offered for selling products in bulk. When series discounts of 15%, 10%, and 5% are offered on a list price L, the net price is calculated by applying each discount sequentially. First, subtract 15% of L from L to get Lβ. Then subtract 10% of Lβ from Lβ to get Lβ. Finally, subtract 5% of Lβ from Lβ to get the net price. Alternatively, N = L(1 β dβ)(1 β dβ)(1 β dβ) where dβ = 15%, dβ = 10%, dβ = 5%. The total discount is not the sum of the percentages (15% + 10% + 5% = 30%).
π Definition β Series Trade Discount: Multiple discounts applied sequentially to a list price, where each discount is applied to the reduced price from the previous step.
π Formula: N = L(1 β dβ)(1 β dβ)(1 β dβ)... where N = net price, L = list price, dβ, dβ, dβ... = discount rates as decimals.
π Example: Office furniture has list price Rs. 20,000 with series discounts 20%, 10%, 5%. Net price = 20,000(1-0.2)(1-0.1)(1-0.05) = 20,000(0.8)(0.9)(0.95) = 20,000(0.6840) = Rs. 13,680.
LIST PRICE
When only the net price and trade discount rate are known, the list price can be found by rearranging the discount formula. The relationship is Net Price = L(1 β d), so L = N / (1 β d).
π Formula: L = N / (1 β d) where L = list price, N = net price, d = discount rate as decimal.
π Example: An order for power tools has a Rs. 2,100 net price after a 30% trade discount. L = 2,100 / (1 β 0.3) = 2,100 / 0.7 = Rs. 3,000.
π‘ Why this matters: Businesses often know what they paid (net price) and the discount rate, but need to find the original list price for inventory valuation or pricing strategies.
TRADE DISCOUNT β FINDING SINGLE EQUIVALENT DISCOUNT RATE
To find a single discount rate equivalent to a series of discounts, apply the series to a list price of Rs. 100 (a convenient base). The net price calculated represents the percentage of list price that is paid, so the single equivalent discount rate is 100% minus this net price percentage.
π Definition β Single Equivalent Discount Rate: A single discount percentage that produces the same net price as applying a series of discounts sequentially.
π Example 1: Find single discount equivalent to series 15%, 10%, 5%. Apply to Rs. 100: Net price = 100(0.85)(0.9)(0.95) = 72.68. Single equivalent discount = 100 β 72.68 = 27.62%.
π Example 2: Car parts priced at Rs. 20,000 with series 20%, 8%, 2%. Net price on Rs. 100 = 100(0.8)(0.92)(0.98) = 72.13. Single equivalent discount = 100 β 72.13 = 27.87%. Rs. discount = 0.2787 Γ 20,000 = Rs. 5,574.
CASH DISCOUNT
A seller always desires prompt payment, so a cash discount is given for the early payment of dues. This benefits both parties β the buyer saves money while the seller gets funds sooner. Cash discount is allowed on invoices, returned goods, freight, sales tax, etc.
π Definition β Cash Discount: A discount offered to encourage prompt payment of an invoice within a specified time period.
π Key Business Phrase: "3/10, net/30" means a 3% discount is offered if paid within 10 days; otherwise, the full amount is due in 30 days. For Rs. 100 due, buyer pays Rs. 97 within 10 days or Rs. 100 within 30 days.
DISCOUNT PERIODS AND CREDIT PERIODS
Discount Periods are the time frames within which the buyer must pay to take advantage of discount terms. Credit Periods are the times given for buyers to pay invoices within specified limits.
π Example: Invoice dated May 1st with terms 2/10 means 2% discount if paid by May 10th. For Rs. 50,000 invoice: N = 50,000(1 β 0.02) = 50,000(0.98) = Rs. 49,000.
π‘ Why this matters: Cash discounts directly impact a company's cash flow. Buyers must calculate whether the discount is worth paying early, while sellers use this to accelerate receivables.
β Key Takeaways
Series trade discounts are applied sequentially, not added together β the formula N = L(1 β dβ)(1 β dβ)(1 β dβ) must be used. The total discount from a series is always less than the sum of individual percentages. To find a single equivalent discount rate, assume a list price of Rs. 100 and subtract the resulting net price from 100. Cash discount terms like "2/10, net/30" define specific time windows (discount periods and credit periods) within which payment must occur to receive the discount. Excel formulas can automate all these calculations, making them efficient for business applications.
π§ Quick Revision Questions
-
If series discounts of 25%, 10%, and 5% are offered on a list price of Rs. 50,000, what is the net price? Show your calculation.
-
What is the single equivalent discount rate for the series 20%, 15%, and 10%? Use the Rs. 100 method.
-
An invoice of Rs. 30,000 has terms "4/15, net/45". If paid on day 12, what amount should be paid?
-
Why is it incorrect to simply add 15% + 10% + 5% = 30% as the total discount when applying series trade discounts?
-
If the net price is Rs. 5,100 after a 15% trade discount, what was the list price?
π Lecture 15 β Mathematics of Merchandising Part 3
π Overview: This lecture focuses on the mathematics behind merchandising decisions, including partial payments on invoices and the critical distinction between markup on cost and markup on selling price. Understanding these concepts is essential for setting profitable selling prices and managing cash discounts in business transactions.
ποΈ Topics Covered
The lecture begins with a review of partial payments, explaining how cash discounts apply to part payments on invoices. It then introduces key marketing and financial terms including manufacturer cost, selling price, and the distribution chain. A major portion is dedicated to explaining margin versus markup, with detailed examples showing how to calculate selling price, cost, and rupee markup using both cost-based and sale-based markups. The lecture concludes with formulas for converting between markup on cost and markup on selling price.
π Lecture Summary
Partial Payments
When you buy on credit with cash discount terms, part of the invoice may be paid within the specified time. These part payments are called Partial Payments.
In the example: You owe Rs. 40,000 with terms 3/10 (3% discount by 10th day). Within 10 days you sent a payment of Rs. 10,000.
First, find the amount (t) that when given a 3% discount, equals Rs. 10,000: 10000 = t (1 β 0.03) t = 10000 / (1 β 0.03) t = 10309 Rs.
This means although you pay Rs. 10,000, due to the 3% cash discount, Rs. 10,309 of the Rs. 40,000 is considered paid. Hence, the new balance = 40000 β 10309 = 29691 Rs.
π Definition β Partial Payment: A payment made on an invoice within the discount period that is less than the full invoice amount, where the discount applies to the portion being paid.
Marketing Terms
There are several important marketing terms. Manufacturer Cost is the cost of manufacturing. Next is the price charged to middlemen in The Distribution Chain, which flows: Distributor > Wholesaler > Retailer. Selling Price is the price charged to consumers by retailers. It may or may not be the same as list price.
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.
Operating Expenses are expenses the company incurs in operating the business, e.g. rent, wages and utilities.
Selling Price is composed of Cost and Rs Markup: Selling Price (S) = Cost (C) + Rs Markup (M)
Margin
While determining Sale Price, a company includes the operating expenses and profit to their own cost. This amount is called the margin of the company. It is usually calculated as a percentage but can also be expressed in rupees. It is also named as markup on sale.
Margin or markup on sale = (Selling price - Cost price) / Selling Price Γ 100%
Selling price = Cost price + Rs Margin
Margin and markup confuse many. By margin, a company evaluates how much is left over to cover basic operating costs and profit for every rupee generated in sales. Markup represents the amount added to a cost to arrive at a selling price.
Markup on cost = (Selling price β Cost price) / Cost price Γ 100%
Example: An item costs Rs. 50, and is sold for Rs. 100: Markup = (100 β 50) / 50 Γ 100% = 100% Margin = (100 β 50) / 100 Γ 100% = 50%
π‘ Why this matters: The same numerical markup yields different percentages depending on whether it is calculated on cost or on selling price, which can significantly affect profit analysis.
Note: Remember unless it is mentioned that markup is on sale, simple markup means markup on cost.
Example
A computerβs cost is Rs. 9,000. An amount of Rs. 3,000 was added to this cost by the retailer to determine the sale price for the consumer. Thus, the selling price = 9,000 + 3,000 = 12,000 Rs. Rs. 3,000 is the margin available to meet expenses and make a profit.
Markup
If the Markup on cost is 33% then: Selling Price (S) = Cost (C) + {Cost (C) Γ Markup on cost (MUC)} S = C + (C Γ MUC) = C(1 + MUC)
Markup - Example
You buy candles for Rs. 10 and plan to sell them for Rs. 15. Rs. Markup = Selling price β Cost price = 15 β 10 = Rs. 5 %Markup on cost = 5/10 Γ 100% = 50%
Selling Price (Cost-Based Markup)
Fawadβs Appliances bought a sewing machine for Rs. 1,500. He needs a 60% Markup on Cost.
Rs. Markup = Cost price Γ %Markup on cost = 1,500 Γ 0.6 = 900 Rs.
Selling Price (S) = Cost (C) + Rs Markup (M) = 1,500 + 900 = 2,400 Rs.
Or using the formula: S = C(1 + MUC) = 1,500 Γ (1+0.6) = 1,500 Γ 1.6 = 2,400 Rs.
π Excel Calculation: With cost 1500 in cell F4 and 60% markup in cell F5, Rs. Markup = 60/100*1500 in cell F6, and Selling Price = F4+F6 in cell F7, giving result 2400.
Rs. Markup and Percent on Cost (Finding Cost)
Tanveerβs flower business sells floral arrangements for Rs. 35. He needs a 40% Markup on cost.
S = C + 0.40(C) 35 = 1.40(C) C = 35/1.4 = 25 Rs. Rs. Markup = 25 Γ 0.4 = 10 Rs.
π Excel Calculation: Selling price 35 in cell H15, % Markup on cost 40 in cell H16. Cost = 35/1.4 in cell H18. Rs. Markup = H18*H16/100 in cell H19, giving result 10.
Markup Again (Percent Markup on Selling Price)
You buy candles for Rs. 2 and plan to sell them for Rs. 2.50. Rs. Markup = 2.5 β 2 = 0.5 Rs.
As explained in lecture 13: Cost price = Selling price (1 β %Markup on sale)
Markup on selling price = (Selling price - Cost price) / Selling price Γ 100% Markup on Selling Price = (0.5/2.5) Γ 100% = 20%
π Excel Calculation: Purchase price 2 in cell E30, Sale price 2.5 in cell E31. Rs. Markup = E31-E30 in cell E32. % Markup on sale price = E32/E31*100 in cell E33, giving 20.
Selling Price (Sale-Based Markup)
Fawadβs Appliances bought a sewing machine for Rs. 1,500. He needs a 60% Markup on Selling price.
As explained in lecture 13: Selling Price = Cost price + (Selling price Γ %Markup on sale) S = 1,500 + 0.6S S - 0.6S = 1,500 0.4S = 1,500 S = 3,750 Rs.
Rs. Markup = 3,750 Γ 0.6 = 2,250 Rs.
π Excel Calculation: Purchase price 1500 in cell E39, % Markup on Sale Price 60 in cell E40. Sale Price = E39/(1-E40/100) in cell D41, giving 3750. Rs. Markup = E41-E39 in cell E42, giving 2250.
Basic formula: S = C + 0.6S, simplified to 0.4S = C, rewritten as S = C/0.4 = C/(1-mus).
Rs. Markup and Percent Markup on Cost (Finding Cost with Sale-Based Markup)
Tanveerβs flower business sells floral arrangements for Rs. 35. He needs a 40% Markup on Selling Price.
Selling Price = Cost price + (Selling price Γ %Markup on sale) 35 = C + (0.4 Γ 35) 35 = C + 14 C = 35 β 14 = 21 Rs.
Or alternatively: C = S - 0.4S = 0.6S = 0.6 Γ 35 = 21 Rs. Rs. Markup = 35 Γ 0.4 = 14 Rs.
π Excel Calculation: Sale price 35 in cell E50, % Markup on Sale Price 40 in cell E51. Cost = E50*(1-E51/100) in cell D52, giving 21. Rs. Markup = E50-E52 in cell E53, giving 14.
Converting Markups
Convert 50% Markup (MU) on Cost to %MU on Sale
The formula for converting %Markup on Cost (muc) to %Markup on Selling Price (mus) is: mus = muc / (1 + muc)
Solution: mus = 0.5 / (1+0.5) = 0.5/1.5 = 0.3333 = 33.33%
Convert 33.33% MU on Sale to %MU on Cost
The formula for converting %Markup on Sale (mus) to %Markup on Cost (muc) is: muc = mus / (1 - mus)
Solution: Markup on cost = 0.3333/(1 β 0.333) = 0.3333/0.6666 = 0.5 = 50%
π Excel Calculation: Markup on sale 33.3 in cell E61. Markup on cost = (E61/100)/(1-E61/100)*100 in cell E62, giving 50.
β Key Takeaways
The lecture demonstrates that markup calculations fundamentally differ depending on the base usedβcost versus selling priceβand these cannot be used interchangeably without conversion. Partial payments involve "grossing up" the payment amount to account for the discount applicable to the paid portion. Margin (markup on sale) always yields a lower percentage than markup on cost for the same numerical markup. The conversion formulas between muc and mus are mus = muc/(1+muc) and muc = mus/(1-mus), which are critical for translating between pricing strategies. Mastery of the selling price formula S = C(1+MUC) for cost-based markup and S = C/(1-MUS) for sale-based markup is essential for solving for any unknown variable.
π§ Quick Revision Questions
- If an invoice of Rs. 50,000 has terms 2/10 and you send a partial payment of Rs. 20,000 on day 8, what is the new balance?
- A retailer buys a product for Rs. 800 and wants a 25% markup on cost. What is the selling price?
- How do you calculate margin (markup on sale) if the cost is Rs. 200 and selling price is Rs. 300?
- Convert a 40% markup on cost to a markup on selling price.
- If the selling price of an item is Rs. 120 and the markup on selling price is 20%, what is the cost?
π Lecture 16 β Mathematics of Merchandising PART 4
π Overview: This lecture covers markdowns (reductions from original selling price) and introduces project financial analysis concepts. It demonstrates how to calculate markdown percentages and sale prices, and provides an overview of key financial calculations used in project evaluation, including cost estimates, revenue forecasts, cash flows, benefit-cost analysis, internal rate of return, and break-even analysis.
ποΈ Topics Covered
The lecture begins with a review of markup concepts from Lecture 15, then introduces markdown calculations with examples. It then transitions to project financial analysis, covering cost estimates, revenue estimates, forecasts of costs and revenues, net cash flows, benefit-cost analysis, internal rate of return, and break-even analysis. Excel calculation methods are shown for markdown problems.
π Lecture Summary
Markdown
Markdown is defined as the reduction from the original selling price.
π Definition β Markdown: Reduction from the original selling price.
π Formula: %Markdown = (Rs. Markdown / Selling Price (original)) Γ 100%
- Rs. Markdown = Old Selling Price β New Selling Price
Markdown β Example 1
Store A marked down a Rs. 500 shirt to Rs. 360.
Step 1: Rs. Markdown Let S = Sale price Rs. Markdown = Old S β New S = Rs. 500 β Rs. 360 = Rs. 140 Markdown
Step 2: % Markdown % Markdown = (Markdown / Old S) Γ 100% % Markdown = (140 / 500) Γ 100% = 0.28 Γ 100% = 28%
π Excel Calculation: Original price 500 in cell E73. Price after markdown 360 in cell E74. Rs. Markdown in cell E75 using formula =E73-E74 (result 140 in D75). % Markdown in cell E76 using =E75/E73*100 (result 28).
Markdown β Example 2
A variety of plastic jugs bought for Rs. 57.75 was marked up 45% of the selling price. When the jugs went out of production, they were marked down 40%. What was the sale price after the 40% markdown?
Part 1: Find Original Sale Price Let Selling price = 100 %Markup on selling price = 45% Cost = 100 β 45 = 55 Thus Original Sale price = (100/55) Γ 57.75 = Rs. 105
Part 2: Rs. Markdown %Markdown = 40% = 0.4 Rs. Markdown = 105 Γ 0.4 = Rs. 42
Part 3: Sale Price After Markdown Sale price after markdown = 105 β 42 = Rs. 63
π Excel Calculation: Purchase price 57.75 in cell F83. Selling price as 100 in cell F84. Rs. Markup in cell F85 using =F84-F83 (result 45 in F85). Original Sale Price in cell F87 using =F84/F86F83 (result 105 in E87). % Markdown as 40 in cell F88. Rs. Markdown in cell F89 using =F87F88/100 (result 42 in F89). Reduced price in cell F90 using =F87-F89 (result 63 in F90).
π‘ Why this matters: Markdown calculations are essential for retail pricing strategies, clearance sales, and inventory management. The two-step process in Example 2 shows how to work backwards from cost and markup to find original price, then apply markdown.
Project Financial Analysis
Financial analysis is the analysis of the accounts and the economic prospects of a firm, which can be used to monitor and evaluate the firm's financial position, to plan future financing, and to designate the size of the firm and its rate of growth.
Key financial calculations required:
- Cost estimates
- Revenue estimates
- Forecasts of costs
- Forecasts of revenues
- Net cash flows
- Benefit cost analysis
- Internal Rate of Return
- Break-Even Analysis
Cost Estimates
In every project, you will be required to prepare a cost estimate. Such cost estimates cover calculations based on quantities and unit rates. Calculations are done in the form of tabular worksheets. In large projects, there may be a number of separate calculations for part projects. 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. One such technique, Time Series Analysis, will be covered later in the course. Forecasting techniques vary from case to 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, 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 1.2 is considered acceptable. For public projects, even lesser BC ratio may be accepted for social reasons.
Internal Rate of Return
Internal Rate of Return (IRR) is that Discount Rate at which the Present Worth of Costs is equal to the Present Worth of Benefits. IRR is the most important parameter in Financial and Economic Analysis. There are a number of functions in EXCEL for calculation of IRR.
Break-Even Analysis
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 Inflow becomes equal to Cash Outflow. After that point, the company has a positive cash flow (i.e., there is surplus cash after meeting expenses).
π‘ Why this matters: These financial analysis concepts are foundational for evaluating project viability, making investment decisions, and understanding a firm's financial health through metrics like BC ratio and IRR.
β Key Takeaways
Markdown is the reduction from original selling price, calculated as a percentage using the formula %Markdown = (Rs. Markdown / Original Selling Price) Γ 100%. When solving markdown problems involving both markup and markdown, first find the original selling price from cost and markup percentage, then apply the markdown percentage to find the reduced price. Project financial analysis involves multiple calculations including cost/revenue estimates, forecasts, and net cash flows. The Benefit-Cost (BC) Ratio must be at least 1 for project viability, with 1.2 generally acceptable. IRR is the discount rate where present worth of costs equals benefits, and break-even analysis identifies when cash inflows equal cash outflows.
π§ Quick Revision Questions
- A store marks down a Rs. 800 jacket to Rs. 600. What is the Rs. Markdown and % Markdown?
- An item costing Rs. 120 was marked up 30% of selling price, then marked down 25%. What is the final sale price?
- What is the formula for calculating %Markdown?
- What is the significance of a Benefit-Cost (BC) Ratio of exactly 1.0?
- Define Internal Rate of Return (IRR) in project financial analysis.
π Lecture 17 β Mathematics Financial Mathematics Introduction to Simultaneous Equations
π Overview: This lecture introduces Module 4, focusing on Financial Mathematics and the beginning of Linear Equations. It provides a comprehensive overview of Excel financial functions used for depreciation, loan calculations, and investment analysis. The lecture serves as a foundation for understanding break-even analysis and project financial evaluation, emphasizing practical application in business contexts.
ποΈ Topics Covered
The lecture covers Module 4 overview and project financial analysis components, followed by an extensive list of Excel financial functions including AMORDEGRC, AMORLINC, CUMIPMT, CUMPRINC, DB, DDB, MIRR, and IRR. Each function's syntax, parameters, and usage are explained with mathematical formulas and examples where applicable.
π Lecture Summary
Financial Mathematics
This section introduces Module 4 which covers Financial Mathematics (Lecture 17), Applications of Linear Equations (Lectures 17-18), and Break-even Analysis (Lectures 19-22). The Project Financial Analysis component includes cost estimates, revenue estimates, forecasts of costs, forecasts of revenues, net cash flows, benefit cost analysis, Internal Rate of Return, and Break-Even Analysis.
π‘ Why this matters: Understanding these financial analysis tools is essential for evaluating project viability and making informed business investment decisions.
Excel Functions for Financial Analysis
This section details various Excel financial functions used in business mathematics.
AMORDEGRC returns the depreciation for each accounting period, taking into account prorated depreciation when an asset is purchased mid-period. A depreciation coefficient is applied depending on the life of the assets.
π Definition β AMORDEGRC: A function that returns depreciation for each accounting period with a depreciation coefficient based on asset life.
π Formula Syntax: AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
- Cost: cost of the asset
- Date_purchased: date of purchase
- First_period: date of end of first period
- Salvage: salvage value at end of asset life
- Period: period for depreciation
- Rate: rate of depreciation
- Basis: year basis (0 or omitted = 360 days NASD, 1 = Actual, 3 = 365 days, 4 = 360 days European)
Remarks:
- Excel stores dates as sequential serial numbers (Jan 1, 1900 = 1; Jan 1, 2008 = 39448)
- Function returns depreciation until last period or until cumulative depreciation exceeds cost minus salvage
- Asset life calculated by (1 / "rate")
- Depreciation coefficient: 1.5 for life 3-4 years, 2 for 5-6 years, 2.5 for >6 years
- Rate grows to 50% for period preceding last, 100% for last period
- #NUM! error for life between 0-1, 1-2, 2-3, or 4-5 years
AMORLINC returns the depreciation for each accounting period, with prorated depreciation for mid-period purchases.
π Definition β AMORLINC: A function that returns depreciation for each accounting period, similar to AMORDEGRC but without the depreciation coefficient.
π Example β AMORLINC:
- Data: Cost = 2400, Date purchased = 8/19/2008, End of first period = 12/31/2008, Salvage value = 300, Period = 1, Depreciation rate = 15%, Basis = 1 (Actual)
- Formula:
=AMORLINC(A2,A3,A4,A5,A6,A7,A8)where cells contain the above data - Result: First period depreciation = 360
CUMIPMT returns the cumulative interest paid between two periods (described in Lecture 8).
CUMPRINC returns the cumulative principal paid on a loan between two periods (described in Lecture 8).
DB returns the depreciation of an asset for a specified period using the fixed-declining balance method.
π Definition β DB: A function that computes depreciation using the fixed-declining balance method at a fixed rate.
π Formula Syntax: DB(cost,salvage,life,period,month)
- Cost: initial cost of asset
- Salvage: value at end of depreciation
- Life: number of periods (useful life)
- Period: period for depreciation (same units as life)
- Month: number of months in first year (assumed 12 if omitted)
π Formulas:
- rate = 1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places
- Period depreciation: (cost - total depreciation from prior periods) * rate
- First period: cost * rate * month / 12
- Last period: ((cost - total depreciation from prior periods) * rate * (12 - month)) / 12
DDB returns the depreciation of an asset for a specified period using the double-declining balance method or another specified method.
π Definition β DDB: A function that computes accelerated depreciation using the double-declining balance method.
π Formula Syntax: DDB(cost,salvage,life,period,factor)
- Cost: initial cost
- Salvage: value at end (can be 0)
- Life: useful life
- Period: period for depreciation
- Factor: rate of decline (2 if omitted = double-declining balance)
π Formula:
Min( (cost - total depreciation from prior periods) * (factor/life), (cost - salvage - total depreciation from prior periods) )
MIRR returns the modified internal rate of return for a series of periodic cash flows, considering both the cost of investment and interest received on reinvestment of cash.
π Definition β MIRR: A function that calculates the modified internal rate of return accounting for financing and reinvestment rates.
π Formula Syntax: MIRR(values,finance_rate,reinvest_rate)
- Values: array of payments (negative) and income (positive) at regular periods
- Finance_rate: interest rate paid on money used in cash flows
- Reinvest_rate: interest rate received on reinvested cash flows
IRR returns the internal rate of return for a series of cash flows.
π Definition β IRR: A function that calculates the internal rate of return using an iterative technique.
π Formula Syntax: IRR(values,guess)
- Values: array of cash flows (must contain at least one positive and one negative)
- Guess: initial estimate (assumed 0.1 or 10% if omitted)
Remarks:
- Uses iterative technique, cycles until accurate within 0.00001%
- Returns #NUM! error if no result after 20 tries
- Order of values must match cash flow sequence
π Example β IRR:
- Data: Investment of 70,000 in cell A97 (negative cash flow), Revenue per year in cells A98 to A102 (years 1-5)
- Formula in A103:
=IRR(A97:A101)β only years 1 to 4 selected - Result: -2%
- Formula in A105:
=IRR(A97:A102)β entire revenue stream considered - Result: 9%
- Formula with guess: Considering only first 2 years with initial guess of -10%
- Result: -44%
β Key Takeaways
- Excel provides specialized financial functions (AMORDEGRC, AMORLINC, DB, DDB) for calculating different types of asset depreciation, each using distinct methods like fixed-declining balance, double-declining balance, and coefficient-based approaches.
- Investment analysis functions (IRR, MIRR) evaluate project profitability by computing internal rates of return, requiring at least one positive and one negative cash flow value for calculation.
- The basis parameter (0-4) in depreciation functions determines the day-count convention (360-day, actual, or 365-day years), significantly affecting depreciation calculations.
- The DB function uses a fixed rate formula (rate = 1 - ((salvage/cost)^(1/life))) with special adjustments for first and last period calculations based on the month parameter.
- IRR uses iterative calculation and may require a user-provided guess value if the default 10% assumption fails to converge within 20 iterations.
π§ Quick Revision Questions
- What is the syntax for the AMORDEGRC function and what does the "basis" parameter represent?
- How does the depreciation coefficient change based on asset life in the AMORDEGRC function?
- What formula does the DB function use to calculate the fixed depreciation rate?
- In the IRR example, why did the result change from -2% to 9% when different cash flow ranges were selected?
- What are the key differences between the MIRR and IRR functions in terms of what they account for?
π Lecture 18 β Mathematics Financial Mathematics Solve Two Linear Equations with Two Unknowns
π Overview: This lecture covers financial mathematics functions in Excel, including depreciation methods (AMORDEGRC, AMORLINC, DB) and present value functions (PV, NPV, XNPV). It also reviews solving two linear equations with two unknowns, building on concepts from Lecture 17.
ποΈ Topics Covered
The lecture reviews depreciation functions from Lecture 17 including AMORDEGRC, AMORLINC, and DB with additional examples. It then introduces present value (PV), net present value (NPV), and XNPV functions for investment analysis. The lecture concludes with methods for solving two linear equations with two unknowns.
π Lecture Summary
AMORDEGRC-EXAMPLE
The AMORDEGRC function returns depreciation for each accounting period using a French declining balance method. This function is primarily used in French accounting systems. The syntax requires parameters including cost, date purchased, first period end, salvage value, period, rate, and basis.
AMORLINC-EXAMPLE
The AMORLINC function returns depreciation for each accounting period using a French straight-line method. Unlike AMORDEGRC which uses declining balance, AMORLINC applies straight-line depreciation across periods. Each function requires similar input parameters but calculates depreciation differently.
DB-EXAMPLE
The DB function returns the depreciation of an asset for a specified period using the fixed-declining balance method. The syntax is DB(cost, salvage, life, period, month). This calculates depreciation at a fixed rate, with higher depreciation in early years and decreasing amounts over time.
ADDITIONAL DB_EXAMPLES
The DB function can handle partial-year depreciation by specifying the number of months in the first year. Examples show:
- =DB(A27,A28,A29,1,7) β Depreciation in first year with only 7 months calculated (186,083.33)
- =DB(A27,A28,A29,2,7) β Depreciation in second year (259,639.42)
- =DB(A27,A28,A29,3,7) β Depreciation in third year (176,814.44)
- =DB(A27,A28,A29,4,7) β Depreciation in fourth year (120,410.64)
- =DB(A27,A28,A29,5,7) β Depreciation in fifth year (81,999.64)
- =DB(A27,A28,A29,6,7) β Depreciation in sixth year (55,841.76)
- =DB(A27,A28,A29,7,5) β Depreciation in seventh year with only 5 months calculated (15,845.10)
π‘ Why this matters: Partial-year depreciation is essential when assets are purchased mid-year, and the DB function automatically adjusts calculations to reflect the actual months of use.
PV
PV returns the present value of an investment. The present value represents the current worth of a future sum of money or stream of cash flows given a specified rate of return.
Syntax: PV(rate, nper, pmt, fv, type)
- Rate β interest rate per period
- Nper β total number of payment periods in an annuity
- Pmt β payment made each period, 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 indicating when payments are due (0 = end of period, 1 = beginning)
π Definition β Present Value (PV): The current value of a future sum of money or stream of cash flows given a specified rate of return.
NPV
NPV returns the net present value of an investment based on a series of periodic cash flows and a discount rate. Net present value is the difference between the present value of cash inflows and the present value of cash outflows over a period of time.
Syntax: NPV(rate, value1, value2, ...)
- Rate β rate of discount over the length of one period
- Value1, value2, ... β 1 to 29 arguments representing the payments and income
π Definition β Net Present Value (NPV): The difference between the present value of cash inflows and the present value of cash outflows, used to analyze profitability of an investment.
XNPV
XNPV returns the net present value for a schedule of cash flows that is not necessarily periodic. This function is more flexible than NPV because it accepts specific dates for each cash flow.
Syntax: XNPV(rate, values, dates)
- 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
π Definition β XNPV (Extended Net Present Value): A function that calculates NPV for cash flows that occur at irregular intervals by specifying exact dates for each cash flow.
β Key Takeaways
The DB function calculates depreciation using a fixed-declining balance method, with higher depreciation in early years. Both AMORDEGRC and AMORLINC are French accounting methods for calculating periodic depreciation. PV calculates the current worth of future cash flows, while NPV extends this to analyze the difference between cash inflows and outflows. XNPV provides additional flexibility by accepting non-periodic cash flows with specific dates. The functions PV, NPV, and XNPV are essential tools for evaluating the profitability and value of investments in financial mathematics.
π§ Quick Revision Questions
- What is the difference between AMORDEGRC and AMORLINC functions?
- How does the DB function calculate the depreciation in the seventh year if only 5 months are used (result 15,845.10)?
- What does the PV function return, and what are its five parameters?
- What is the key distinction between NPV and XNPV functions?
- If an asset has cost 1,000,000, salvage value 100,000, and life of 10 years, what would be the first year depreciation using DB with 7 months?