MS Excel Tips and Tricks: Conditional Formatting
Would you like to be able to scan your company’s financial operations spreadsheet and instantly see which departments are over budget or behind schedule or which accounts receivable are past due? There’s an easy way to do that in Excel, which can automatically flag cells that meet most any condition you establish. You can set the cells to display different formatting flags—colors, font styles, shading, patterns, underlining—with each custom format identifying a specific financial condition. For example, you can program Excel to flag costs that are over budget by displaying them as
red; under-budget costs may appear
blue.
The Excel function that does this job is conditional formatting. What makes the function especially handy is that it’s not static—that is, when the data in the worksheet change, the cells instantly reflect that by taking on the appropriate formatting.
full article on Conditional Formating in MS-ExcelHighly Recommended Excel Spreadsheets & Templates
- Capital Budgeting Analysis (xls) - Basic program for doing capital budgeting analysis with inclusion of opportunity costs, working capital requirements, etc. - Adamodar Damodaran
- Rating Calculation (xls) - Estimates a rating and cost of debt based on the coverage of debt by an organization - Adamodar Damodaran
- LBO Valuation (xls) - Analyzes the value of equity in a leverage buyout (LBO) - Adamodar Damodaran
- Synergy (xls) - Estimates the value of synergy in a merger and acquisition - Adamodar Damodaran
- Valuation Models (xls) - Rough calculation for choosing the correct valuation model - Adamodar Damodaran
- Risk Premium (xls) - Calculates the implied risk premium in a market. (uses macro's) - Adamodar Damodaran
- FCFE Valuation 1 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with stable growth rates - Adamodar Damodaran
- FCFE Valuation 2 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth - Adamodar Damodaran
- FCFE Valuation 3 (xls) - Free Cash Flow to Equity (FCFE) Valuation Model for organizations with three stages of growth, high growth initially, decline in growth, and then stable growth - Adamodar Damodaran
- FCFF Valuation 1 (xls) - Free Cash Flow to Firm (FCFF) Valuation Model for organizations with stable growth rates - Adamodar Damodaran
- FCFF Valuation 2 (xls) - Free Cash Flow to Firm (FCFF) Valuation Model for organizations with two periods of growth, high growth initially and then stable growth - Adamodar Damodaran
- Time Value (xls) - Introduction to time value concepts, such as present value, internal rate of return, etc.
- Lease or Buy a Car (xls) - Basic spreadsheet for deciding to buy or lease a car.
- NPV & IRR (xls) - Explains Internal Rate of Return, compares projects, etc.
- Real Rates (xls) - Demonstrates inflation and real rates of return.
- Template (xls) - Template spreadsheet for project evaluation & capital budgeting.
- Free Cash Flow (xls) - Cash flow worksheets - subsidized and unsubsidized.
- Capital Structure (xls) - Spreadsheet for calculating optimal capital structures using different percents of debt.
- WACC (xls) - Calculation of Weighted Average Cost of Capital using beta's for equity.
- Statements (xls) - Generate a set of financial statements using two input sheets - operational data and financial data.
- Bond Valuation (zip) - Calculates the value or price of a 25 year bond with semi-annual interest payments.
- Buyout (zip) - Analyzes the effects of combining two companies.
- Cash Flow Valuation (zip) - Walks through a valuation of cash flows under three models- capital cash flows, equity cash flows, and free cash flows.
- Financial Projections (zip) - Spreadsheet model for generating projected financials along with valuation based on WACC.
- Leverage (zip) - Shows the effects on Net Income from using debt (leverage).
- Ratio Calculator (zip) - Calculates a standard set of ratios based on input of financial data.
- Stock Value (zip) - Calculates expected return on stock and value based on no growth, growth, and variable growth.
- CFROI (xls) - Simplified Cash Flow Return on Investment Model.
- Financial Charting (zip) - Add on tool for Excel 97, consists of 6 files.
- Risk Analysis (exe) - Analysis and simulation add on for excel, self extracting exe file.
- Black Scholes Option Pricing (zip) - Excel add on for the pricing of options.
- Cash Flow Matrix - Basic cash flow model.
- Business Financial Analysis Template for start-up businesses from Small Business Technology Center
- Forex (zip) - Foreign market exchange simulation for Excel
- Hamlin (zip) - Financial function add-on's for Excel
- Tanly (zip) - Suite of technical analysis models for Excel
- Financial History Pivot Table - Microsoft Financials
- Income Statement What If Analysis
- Breakeven Analysis (zip) - Pricing and breakeven analysis for optimal pricing - Biz Pep.
- SLG Ratio Master (exe) - Excel workbook for creating 25 key performance ratios.
- DCF - Menu driven Excel program (must enable macros) for Discounted Cash Flow Analysis from the book Analysis for Financial Management by Robert C. Higgins - Analysis for Financial Management
- History - Menu driven Excel program (must enable macros) for Historical Financial Statements from the book Analysis for Financial Management by Robert C. Higgins - Analysis for Financial Management
- Proforma - Menu driven Excel program (must enable macros) for Pro-forma Financial Statements from the book Analysis for Financial Management by Robert C. Higgins - Analysis for Financial Management
- Business Valuation Model (zip) - Set of tabbed worksheets for generating forecast / valuation outputs. Includes instruction sheet. Bizpep
- LBO Model - Excel model for leveraged buy-outs
- Comparable Companies - Excel valuation model comparing companies
- Combination Model - Excel valuation model for combining companies
- Balanced Scorecard - Set of templates for building a balanced scorecard.
- Cash Model - Template for calculating projected financials from CFO Connection
- Techniques of Financial Analysis - Workbook of 11 templates (breakeven, valuation, forecasting, etc.) from ModernSoft
- Ratio Reminder (zip) - Simple worksheet of comparative financials and corresponding ratios from Agilicor
- Risk Analysis IT - Template for assessing risk of Information Technology - Audit Net
- Risk Analysis DW - Template for assessing risk of Data Warehousing - Audit Net
- Top Five Excel Workbook 1-2 - Set of worksheets for evaluating financial performance and forecasting - Supplemental Material for Short Course 1 and 2 on this website.
- Rule Maker Essentials - Excel Template for scoring a company by entering financial data - The Motley Fool
- Rule Maker Ranker - Excel Template for scoring a company by entering comparable data - The Motley Fool
- IPO Timeline - Excel program for Initial Public Offerings (must enable macros)
- Assessment Templates - Set of templates for assessing an organization based on the Malcolm Baldrige Quality Model.
- Cash Gap in Days - Spreadsheet for calculating number of days required for short-term financing.
- Cash Flow Template - Simple cash flow model with explanations of each cash flow component - Arkansas Small Business Development Center.
- Six Solver Workbook (zip) - Set of various spreadsheets for solving different business problems (inventory ordering, labor scheduling, working capital, etc.).
- Free Cash Flow Valuation - Basic Spreadsheet Valuation Model
- Finance Examples - Seven examples in Business Finance - Solver
- Capital Budgeting Workbook - Several examples of capital budgeting analysis, including the use of Solver to select optimal projects.
- Present Value Tables (rtf) - Set of present value tables written in rich text format, compatible with most word processors. Includes examples of how to use present value tables.
- Investment Valuation Model (zip) - Valuation model of companies (must enable macros) - Excel Business Tools
- Cash Flow Sensitivity (xlt) - Sensitivity analysis spreadsheet - Small Business Store
- What If Analysis - Set of templates for sensitivity analysis using financial inputs.
- Risk Return Optimization - Optimal project selection (must enable macro's) - Metin Kilic
- CI - Basics #1 - Basic spreadsheet illustrating competitive analysis - Business Tools Templates.
- CI - Basics #2 - Basic spreadsheet illustrating competitive analysis - Business Tools Templates.
- External Assessment - Assessment questions for organizational assessment (must enable macros).
- Internal Assessment - Assessment questions for organizational assessment (must enable macros).
- Formal Scorecard - Formal Balanced Scorecard Spreadsheet Model (3.65 MB / must enable macros) - Madison Area Quality Improvement Network.
- Project Plan - Project Scheduling Template currently setup for a Balanced Scorecard Project.
- Gantt Chart - Gantt chart for project management with work plan - Jim Chapman's Web Site
- E O Q Model - Simple Inventory Models for calculating Economic Order Quantity.
- Inventory Simulation Control Model - Formal model for simulating inventory shortages, delivery times, costs, backorders, and optimal inventory levels - John McClain
- Financial Projections Model - A comprehensive financial model for forecasting a complete set of financials with breakeven and valuation tabs developed by Frank Moyes and Stephen Lawrence at Leeds School of Business.
- Option Trading Workbook - Educational toolkit for using Excel for Options - Option Trading Tips
- Financial Model - A nice clean financial model driven by different calculators (such as Company, Market, Subscribers, etc.) developed by Bill Snow.
- Forecasting Model - Step by step financial model for forecasting financials created by Sam Gui
- Economic Evaluation - Step by step workbook for evaluating the economics of a system investment - Automated Concepts
MS Excel, spreadsheets, templates, analysis, analyst, project analysis, business analysis, risk management
Labels: analysis, analyst, business analysis, MS Excel, project analysis, risk management, spreadsheets, templates
Jobs: Basel II - Credit Risk - Senior position
Long term position working a Global Financial Firm located in Warren, NJ. If you are interested, please reply to this email with current resume. Thanks so much!
- 6+ years of experience in Technology, Finance and Basel II
- Good knowledge of Basel II - Credit Risk (Wholesale, Retail, Securitization and Equities) processes and data
- Understands Basel II Regulatory Requirements
- Good knowledge of Basel II products and sub-products (e.g. OTC, Repo-style, AFS etc.)
- Experience in design of architecture of n-tier distributed system
- Understands tools and technology - ETL, Analytical/Calculator Engine, Databases, Reporting etc.
- Capable to document Basel II logical architecture, physcial architecture, process flow, information/data flow and data model
- Strong communication skills
- Create the Logical Architecture document - Logical/Component Architecture - Systems/Components Process Flow - Integration Architecture
- Create the Physical Architecture Document - Deployment Architecture - Infrastructure Architecture - Physical Architecture
- Create an end-to-end Logical Data Model document
- Create an end-to-end Data/Information Flow document
If you are qualified, available, interested, planning to make a change, or know of a friend who might have the required qualifications and interest, please call me ASAP at (212) 306-0158, even if we have spoken recently about a different position. You may also send me an e-mail or contact me via the chat box at http://www.algomod.com/. If you do respond via e-mail please include a daytime phone number so I can reach you. In considering candidates, time is of the essence, so please respond ASAP. Thank you.
Melody Gingrich melody@algomod.com
Algomod Technologies Corp.
Labels: Basel II, careers, financial jobs, jobs, risk management
Basel II complimentary seminar
Basel II complimentary seminarAre you Basel II ready? BPS is sponsoring a complimentary full day seminar on Wednesday, April 4th at the Yale Club in New York City. Listen to experts from Nomura, Merrill Lynch, Bear Stearns, Wachovia and IBM to name a few. Excellent opportunity to learn and network with peers.
Space is very limited - to sign up, email
webcast@bpsinc.com.
Basel 2, Basel II, Basel Accord, risk management, trading risk management, P/L, mark-to-market, mark-to-futureLabels: Basel 2, Basel Accord, Basel II, mark-to-future, mark-to-market, P/L, risk management, trading risk management
Weighing the risks, benefits of different risk models
Weighing the risks, benefits of different risk modelsPerhaps the first step a bank or other asset-management company must take when evaluating the role of risk management is deciding where the risk-management function should reside -- someplace under the chief investment officer, or functioning independently to support the COO. In this analysis, the author argues that there is no single solution that will work for every financial institution.
More on financial risk management modelsfinancial, risk, risk management, models, programs, IT, valuationsLabels: financial, IT, models, programs, risk, risk management, valuations
Regulatory Intelligence Compliance Risk Consulting
Regulatory Intelligence Compliance Risk Consulting founded by former NYSE Securities RegulatorsRegulatory Intelligence provides customized consulting services and offers an all inclusive web-based Branch Audit software program which can be utilized by your firm's audit group. This program mirrors the current regulatory audits and additional features (written procedures and corresponding Regulatory Rules) enable this product to also function as a training tool.
Try it today.risk management, compliance, business systemsLabels: business systems, compliance, risk management
DRIVING STRATEGIC INNOVATION:
Achieving Breakthrough Performance Throughout the Value ChainA unique executive program from MIT Sloan and IMD that integrates technology and innovation strategy with the tools and frameworks needed to help business leaders and entrepreneurs manage the innovation process from concept to commercialization.
View course agenda and registrationLabels: derivatives, innovation, management, metrics, performance monitor, risk management, strategy, technology, trading, value chain
SOA Advances Despite Confusion
SOA Advances Despite Confusion A recent report from Saugatuck Technology asserts that SOA continues to be technology-led, rather than business-led, and this may prove to be a barrier to its broad deployment. It may take as much as a decade for SOA deployments to dominate the IT landscape, but steady progress is being made. According to Oracle, while SOA is well understood at a high level, there is considerable confusion about actual implementation. Oracle's approach: Focus on building the link between business modeling and application development and enabling bi-direction communication between the two. Respected consultant Judith Hurwitz says that Microsoft "has some good pieces" in the SOA space, and "a lot on the server side," but the company is lacking when it comes to metadata and an overall strategy.
Learn more about SOALabels: architecture, derivatives, risk management, SOA, technology, technology strategy, trading
Jobs: Financial Reporting, Controllers Analyst (NYC)
Role: Analyst - Financial Reporting Controllers/New YorkCompany: Goldman SachsIndustry: Investment BankingJob Description:The intermediate/senior analyst will be an integral part of the GS Execution and Clearing financial reporting team. This includes assisting in the preparation and submission of the balance sheet and supporting schedules on monthly basis. The analyst will interact with other divisions throughout the firm with regards to Consolidated reporting, FAS 140 disclosure, Inventory and other balance sheet and P&L transactions. Also experience with FAS 109 tax reporting would be a plus.
Principal Responsibilities:- Assist with Monthly Balance sheet preparation
- Subsidiaries financial statement preparation
- Estimate to actual reviewInventory broadening and review
- Tax accruals and associated analysis
- FAS 140 calculation
- Participate in the semi-annual and year end audits
- Special projects
Experience/Skills:
- Degree in Accounting
- 1-3 years relevant work experience (preferably with a public accounting or financial services firm)
- CPA a plus, but not requiredStrong PC skills (Excel, Word, Essbase, DBS Ledger)
- Strong analytical skills and detail orientation
- Excellent Interpersonal and communication skills
Contact via email for further information.
Careers@BizAnalyst.net and gloria.chen-li@gs.com
HR, jobs, derivatives, trading, risk management
Labels: accounting, careers, derivatives, electronic trading, finance analyst, goldman sachs, hr, investment banking, jobs, risk management, trading
Citigroup plans to cut costs by $1 billion
Citigroup plans to cut costs by $1 billionCitigroup, trailing its rivals in profit growth, plans to cut $1 billion in costs this year, Bloomberg reports, citing a source with direct knowledge of the plan. The cost-cutting measures will involve eliminating some jobs, moving others to less expensive locations and overhauling the risk-management unit.
Read this articleIs this the beginning of a cost cutting wave on Wall Street? Feast or Famine.
Take a look at the new hiring trends on Wall Street.HR, jobs, derivatives, trading, risk managementLabels: careers, citigroup, cost cutting, derivatives, electronic trading, hiring, hr, jobs, layoffs, reorg, risk management, trading
Community Banking & Credit Union Technology Perspectives
Community Banking & Credit Union Technology Perspectives Business strategies for revenue growth and value creation are similar between credit unions and community banks; however, community banks have far tighter cost constraints. Understand rapidly changing trends in end-user opinion to determine your market strategy.
Full articleLabels: credit unions, derivatives, risk management, technology, technology strategy, trading
ISDA 22nd Annual Meeting
ISDA 22nd Annual Meeting
The headline event of the year for the OTC derivatives industry, ISDA's 22nd Annual General Meeting in Boston will feature the perspectives of senior industry figures on global market and public policy issues in the derivatives business.
Topics to be discussed include:
- The Role of Prudent Regulation in Fostering Derivative Use
- Evolving Risk Management Techniques in Credit and Structured Products
- The Ever-Increasing Pace of Innovation and Creativity
- The Critical Role Technology Plays in the Economy and Financial Markets
- Global Growth Opportunities for the Derivatives Business
- Investor Perspectives: New Sources of Derivatives Growth and Sophistication
- Developments in Electronic Commerce Affecting Derivatives Trading and Processing
These themes will be complemented by breakout sessions. The breakout sessions will feature presentations on developing products, documentation, equity structured products, operations, public policy and risk management, presented by industry specialists at the heart of the issues.
Labels: derivatives market practices, ISDA, market rgulation, risk management, trading
Credit Derivatives go electronic
Credit Derivatives go electronic.
Key index and single name CDS products migrate from OTC to exchange traded products.
Business Impact
- Transparent pricing, valuation and public market data on CDS products
- Potential STP for order routing, execution, margin, confirmation & settlement processes.
- Reduced processing costs via automation of key processes
- Reduced spreads per trade
- Increased trading volume & time (23hrs x 5days/week)
- Migration of OTC positions to exchange traded positions (especially on buy-side: hedge funds, asset managers)
Technology Impact
- Interconnectivity to major exchanges for CDS trading (EUREX,CME) via order routing gateways
- Real time market data (quotes, spreads, market depth, executions/fills)
- Increased trading volume
Eurex will start trading in credit futures in March 2007
European exchange Eurex will start futures trading in iTraxx index beginning March 27, 2007. Additionally, trading will start at some date in future on the segments of iTraxx index too.
The Eurex iTraxx® credit futures will closely mimic the risk structure of credit default swaps traded in the over the counter (OTC) market. Trading on Eurex will involve Eurex Clearing as central counterparty thereby reducing the counterparty and systemic risk and adding to the benefits the product will offer to users.
The contract will be based on the 5 year series, with a fixed coupon and semi annual maturity dates in March and September. The contract size is EUR 100,000; the tick size is set at 0.005 percent translating into 5 euros per tick. It will be quoted in percent with three decimal places. The product will be cash settled, with reference to the iTraxx® index values of IIC.
In the case of a credit event, cash settlement of the single name entity will be made with reference to the ISDA CDS protocol. The Eurex iTraxx® Europe futures contract will be supported by designated market makers, ensuring liquidity from launch.
This is claimed to be the world's first exchange traded credit derivative product.
In the meantime, Chicago Mercantile Exchange has also reported that it will start trading in credit event futures in the 1st quarter of 2007. Regulatory approvals, it seems, are still pending.
Questions or comments, contact our subject matter expert: Victor Smith [victor7@bizanalyst.net]
Labels: CBOT, CME, credit derivatives, credit futures, derivatives, electronic trading, EUREX, exchange, LIFFE, market gateway, OTC, risk management, technology, trading
Exploring Monte Carlo Simulation Applications for Project Management
Exploring Monte Carlo Simulation Applications for Project ManagementYoung Hoon Kwak & Lisa Ingall
Monte Carlo simulation is a useful technique for modeling and analyzing real-world systems and situations. This paper is a conceptual paper that explores the applications of Monte Carlo simulation for managing project risks and uncertainties. The benefits of Monte Carlo simulation are using quantified data, allowing project managers to better justify and communicate their arguments when senior management is pushing for unrealistic project expectations. Proper risk management education, training, and advancements in computing technology combined with Monte Carlo simulation software allow project managers to implement the method easily. In the field of project management, Monte Carlo simulation can quantify the effects of risk and uncertainty in project schedules and budgets, giving the project manager a statistical indicator of project performance such as target project completion date and budget.
Read the report.
Keywords:
Monte Carlo simulation, project management, risk analysis and management, exploratory studyLabels: derivatives, exploratory study, Monte Carlo simulation, project management, risk analysis and management, risk management, technology, trading
Risk Diversifications in Emerging Economies
Risk Diversifications in Emerging EconomiesYoung Hoon Kwak & Lisa Ingall
Monte Carlo simulation is a useful technique for modeling and analyzing real-world systems and situations. This paper is a conceptual paper that explores the applications of Monte Carlo simulation for managing project risks and uncertainties. The benefits of Monte Carlo simulation are using quantified data, allowing project managers to better justify and communicate their arguments when senior management is pushing for unrealistic project expectations. Proper risk management education, training, and advancements in computing technology combined with Monte Carlo simulation software allow project managers to implement the method easily. In the field of project management, Monte Carlo simulation can quantify the effects of risk and uncertainty in project schedules and budgets, giving the project manager a statistical indicator of project performance such as target project completion date and budget.
Read this report.Keywords:
Monte Carlo simulation, project management, risk analysis and management, exploratory studyLabels: derivatives, exploratory study, Monte Carlo simulation, project management, risk analysis and management, risk management, technology, trading
Compensation & Negotiation: Please, Sir. I Want Some More.
Please, Sir. I Want Some More. Compensation Negotiation Article by
eFinancialCareersWhat if you're not satisfied when you get your bonus? Could you negotiate for more? Success is rare, and it could be a dangerous path to pursue. "In 20 years at Goldman, I never once saw anyone renegotiate their bonus after it had been announced, ever," says one former department head at Goldman Sachs...
full storyHR, jobs, derivatives, electronic trading, trading, risk managementLabels: careers, derivatives, electronic trading, hr, jobs, risk management, trading
CDOs cubed: The first-ever triple derivative
CDOs cubed: The first-ever triple derivativeExamine the newly created ‘CDOs Cubed’, which are the first-ever triple derivative, that is, a derivative of a derivative of a derivative. Not surprisingly, CDOs Cubed are often called, ‘derivatives on steroids’. Unlike traditional derivatives, which are utilized for risk reduction and/or leveraged speculation, this innovation has created thousands of new investment assets, covering the entire spectrum of risk and return. This paper traces the evolution of this innovation, and examines how this triple derivative is created, structured, and priced.
Read this report on new CDO structured products.
Labels: credit derivatives, derivatives, regulation, risk management, trading
Exchange New Product News - Jan 2007
Exchange New Product News - Jan 2007The latest updates on global exchange traded derivatives. New products, new contract features, regulatory issues and industry initiatives.
Read this reportLabels: credit derivatives, derivatives, regulation, risk management, trading
Foreign exchange hedging in Chile
Foreign exchange hedging in Chile
Jorge A Chan-Lau
jchanlau@imf.orgPolicy makers have expressed interest in fostering the development of local foreign exchange derivatives markets with a view to reducing risks arising from currency mismatches between assets and liabilities in the corporate sector. This paper assesses foreign exchange exposure in the corporate sector in Chile, analyses the current state of the foreign exchange derivatives market in Chile, and argues that liquid and developed foreign exchange derivatives markets can help promote financial stability.
Read this articleLabels: credit derivatives, derivatives, regulation, risk management, trading
Risk management for derivatives
frances.cowell@morleyfm.com
For conjuring fear, few aspects of investments rival derivatives. This is not surprising given the history of august institutions humbled or even demolished as a result of poor risk management of their derivatives exposures. While some types of derivatives strategies certainly demand a very specific approach to risk management, it cannot be said that derivatives defy risk management.
This paper
—describes how derivatives are used in portfolio management;
—explains how to measure the effective economic value of the investment achieved by a derivative transaction and the role of collateral;
—discusses the different types of risk associated with derivatives, the interaction between derivatives and conventional instruments and, from this,
—addresses the question of how derivatives risk can be measured, managed and, where necessary, controlled.
Read this report
Labels: credit derivatives, derivatives, regulation, risk management, trading
Local politicians buy derivatives in "recipe for disaster"
Local politicians buy derivatives in "recipe for disaster"Cash-strapped public school districts and other local governments are increasingly making bets on arcane financial derivatives, often in hopes of quickly raising needed funds. But sometimes, as Bloomberg News recounts, those bets go awry. The schools suffer, but the financial firms clean up.
Read this articleLabels: credit derivatives, derivatives, regulation, risk management, trading
