Unit2 Bcom6.xlsx

Introduction To Financial Modelling Purpose Steps for Building a Financial Model

Explanation Explanation Explanation

Charts Name A B C D E F G H I J K L M N O P Q R

Lectures Taken 140 120 90 140 121 120 132 45 90 120 130 140 110 60 48 78 128 130

Total Lectures 150 150 150 150 150 150 150 150 150 150 150 150 150 150 150 150 150 150

Q1

Make all the charts for the data above • Column charts • Line charts • Pie charts • Bar graphs • Area charts

1

ACCRINT Issue date

First interest date Settlement date Coupon rate 3/1/2008 8/31/2008 5/1/2008 0.1

Calculate Accrued interest for a treasury bond with the terms above. 2

3

CUMIPMT Loan amount total no of payments Interest rate DB Data

100000 360 5%

Description

$1,000,000 $100,000 6

Initial cost Salvage value Lifetime in years

Calculate Depreciation in first year, with only 7 months 4

DDB Data $2,400 $300 10

Description Initial cost Salvage value Lifetime in years

Calculate First day's depreciation, using double-declining balance method. Default factor is 2. 5

6

SLN Data $30,000 $7,500 10

Description Cost Salvage value Years of useful life

SYD Description Initial cost Salvage value Lifespan in years

Data $30,000.00 $7,500.00 10

Calculate Yearly depreciation allowance for the first year 7

IPMT Description Annual interest

Data 10.00%

Period for which you want to find the interest paid. Years of loan Present value of loan

1 3 $8,000

Calculate Interest due in the first month for a loan 8

CUMPRINC Description Interest rate per annum Term in years Present value

Data 0.09 30 125000

Calculate The total principal paid in the second year of payments, periods 13 through 24 9

10

11

12

13

RATE FUNCTION Description Years of the loan Monthly payment Amount of the loan

Data 4 200 8000

PRICE FUNCTION Description Settlement date Maturity date Percent semiannual coupon Percent yield Redemption value Frequency is semiannual 30/360 basis

Data 2/15/2008 11/15/2017 5.75% 6.50% 100 2 0

COUPDAYBS Description Settlement date Maturity date Semiannual coupon Actual/actual basis

Data 25-Jan-11 15-Nov-11 2 1

XNPV and XIRR Values -10,000 2,750 4,250 3,250 2,750

Date 1-Jan-08 1-Mar-08 30-Oct-08 15-Feb-09 1-Apr-09

MIRR Description Initial cost Return first year Return second year

Data -120000 39000 30000

Return third year Return fourth year Return fifth year

21000 37000 46000

Annual interest rate for the 120,000 loan 0.1 Annual interest rate for the reinvested profits

13

0.12

HLOOKUP ORDER ID 1 2 3 4 5

SUBTOTAL 100 200 300 400 500

REGION WEST NORTH SOUTH WEST SOUTH

SHIPPING

NORTH

SOUTH

EAST

SHIPPING RATE ZONE RATE

14

10%

15%

VLOOKUP

Product ID 2345 5457 9823 1233 2344

Available Stock 500 234 155 122 166

Product ID

Price

9823 1233

Price 15 28 13 12 24

20%

Par value Frequency Basis 1000 2 0 semiannual 30/360 basis

WEST 13%

Components of a financial model, Profit and Loss account Balance sheet Cash Flow Statement

Building the template Filling Historical Data Identifying Assumptions and Drivers

Explanation of Components + Format Explanation of Components + Format Explanation of Components + Format

Explanation Explanation Explanation

Forecasting financial statement and various schedules

Given below is the detail of company ABC (P) Limited. Prepare the projected profit and loss account and the balance 1) Sales of the company comprises of three parts, Sale of product A, sale of product B and the Service/maintenance of the products (AMC). Sale of product B is dependent on sale of product A and is one per client. It is assumed that minimum order quantity of product A, will be 25 each year per client. Clients targeted each year Number of clients Existing Customers Selling Price of Product A per unit Selling Price of Product B per unit

Y1 10 5 30,000 30,000

Y2 50 5 35,000 30,000

Y1 4,500 4,500

Y2 4,725 4,725

AMC per product A is Rs. 500, and per product, B is Rs. 1000, with 10% increases YoY. AMC will start from year 2 as for the first year there is a warranty. It is assumed that 60% of the existing customers will opt for AMC. 2) Cost of goods sold is as follows: Cost of goods sold Cost of product A per unit Cost of product B per unit

3) The following table shows the manpower requirement and salaries per person. Manpower Requirement CEO CFO Software Engineer Senior Software Engineer Hardware Engineer Senior Hardware Engineer Support Engineer Sales Executive Account Manager Marketing Manager HR Manager Workers Total

Y1 1 1 4 3 1 1 2 1 10 24

Y2 1 1 4 3 1 4 2 2 10 28

The salary per annum per category is as follows: Salaries CEO CFO Software Engineer Senior Software Engineer Hardware Engineer Senior Hardware Engineer Support Engineer Sales Executive Account Manager Marketing Manager HR Manager Workers

Y1 10,00,000 10,00,000 6,60,000 8,00,000 2,20,000 8,00,000 1,20,000 2,40,000 6,00,000 6,00,000 6,00,000 1,50,000

Y2 12,00,000 12,00,000 7,26,000 8,80,000 2,42,000 8,80,000 1,32,000 2,64,000 6,60,000 6,60,000 6,60,000 1,65,000

4) Rent per month is Rs. 50,000/-, 10% increase p.a 5) Telephone cost – for support function, 20 calls per client, call rate @ Rs.3/-. Sales call 6000, 15000, 20,000, 30000 and 50,000 for years Y1 to Y5. Personal calls of employees, Rs. 50 per month per employee. 6) Electricity charges are Rs. 50000 pm, with 10% increase YoY. 7) Training expenses to be provided at Rs. 100000 pa, with 20% YoY increase. 8) Travel costs Rs. 50000 pm, with 10% increase YoY. 9) Printing and stationery cost Rs. 5000 pm, with 5% increase YoY. 10) Books and periodicals Rs 3000 pm, with 10% increase YoY. 11) Membership and subscriptions, Rs.25,000 pm with 15% increase YoY 12) Consultancy charges Rs.20,000 pm, YoY, 5% increase 13) Other miscellaneous expenses Rs.5000 pm, YoY 10% increase. 14) Capital expenditure details are as follows: Gross block is given below: Gross Block Depreciation Software 60% Engineering tools and components 40% Hardware Tools 40% Total

Y1 500000 900000 1100000 2500000

Depreciation is calculated on the SLM basis 15) The company has a term loan of Rs. 25 lakhs taken at an interest rate of 14%, repayable over 5 Years. 16) The tax rate applicable for the company is 32.45%.

17) Debtors are 60 days sales, and creditors are 30 days of cost of good sold, expenses payable is 15 days, inventory is 30 days. Capital infused in the business is Rs. 70lakhs

t and loss account and the balance sheet.

Y3 75 10 40,000 35,000

Y4 100 13 45,000 35,000

Y5 200 20 45,000 35,000

Y3 4,961 4,961

Y4 5,209 5,209

Y5 5,470 5,470

Y3 1 1 2 1 10 3 4 1 1 10 34

Y4 1 1 2 1 24 5 10 1 1 10 56

Y5 1 1 2 1 46 10 20 1 1 10 93

Y3 20,00,000 20,00,000 7,98,600 9,68,000 2,66,200 9,68,000 1,45,200 2,90,400 7,26,000 7,26,000 7,26,000 1,81,500

Y4 30,00,000 30,00,000 8,78,460 10,64,800 2,92,820 10,64,800 1,59,720 3,19,440 7,98,600 7,98,600 7,98,600 1,99,650

Y5 45,00,000 45,00,000 9,66,306 11,71,280 3,22,102 11,71,280 1,75,692 3,51,384 8,78,460 8,78,460 8,78,460 2,19,615

Y2 600000 1600000 1200000 3400000

Y3 1600000 2400000 1500000 5500000

Y4 3500000 3200000 1800000 8500000

epayable over 5 Years.

Y5 6500000 4000000 2000000 12500000

VARIOUS APPROACHES TO VALUATION

Explanation

Q (i) (ii) (iii) (iv) (v) (vi) (vii) (viii) (ix) (x) (xi) (xii) (xii)

The following sets of final account relates to KM Ltd.Calculate the following ratios for the com Inventory Turnover Ratio Gross Profit Ratio Working Capital Turnover Ratio Current Ratio Quick Ratio Net Profit Ratio Capital Employed Turnover Ratio Fixed Asset Turnover Ratio Total Assets Turnover Ratio Return on Capital Employed Return on Equity Return on Total Assets Earning per share

Liabilities Share Capital: Authorised Issued,Subscribed and Paid -up 12,500 Equity Shares of Rs 10 e 6,000, 8% Preference shares of R Reserve and Surplus: General Reserve Profit and Loss A/c Secured Loans: 8% Debentures Unsecured Loan Current Liabilities and Provisions: Creditors Preference Dividend Equity Dividend

Dr.

Particulars To Opening Stock To Purchases To Gross Proft

To Operating Expense To Interest on Debntures To Net Proft c/d

To General Reserve To Preference Dividend To Equity Dividend

.Calculate the following ratios for the company:

BALANCE SHEET as on 31 st Mrach 2018 Rs.

Assets

Rs.

Fixed Assets: ? Plant and Machinery Investments: 1,25,000 Current Assets, Loan and Advances 60,000 Current Assets: Stock 25,000 Debtors 98,700 Cash at Bank Cash in Hand 40,000 Miscellaneous Expenditure ….

3,04,350

35,000 46,000 17,250 3,650 ….

d Provisions: 34,000 4,800 18,750 4,06,250

4,06,250

PROFIT AND LOSS ACCOUNT for the year ended 31 st March ,2018 Cr.

Rs.

Particulars

Rs.

24,000 3,26,000 2,25,000 5,75,000

By Sales By Closing Stock

5,40,000 35,000

81,800 3,200 1,40,000 2,25,000

By Gross Profit b/d

25,000 4,800 18,750 1,47,250

By Balance b/d By Net Profit b/d

5,75,000 2,25,000

2,25,000 7,250 1,40,000 1,47,250

Explanation EPS and multiples Fundamentals EV/EBITDA Explanation Fundamentals -EV/Sales Explanation

Sensitivity analysis

Example: Sales quantity

1500

Selling Price

100

Variable cost

50/unit

Fixed cost

50000

Calculate the following:

(i) Quantity change from 1000 to 1500, and impact on profit

(ii) Fixed cost changes from -15% to 15%, and impact on profit.

(iii) Variable cost changes from 40 to 60, quantity changes from 1000 to 1500, and its impact on profit.

Looking at the probabilistic analysis of the best and worst case scenario. Explanation