MGST 217 Final Exam Study Notes (University of Calgary) PDF

# MGST 217 Final Exam Study Notes ## Management Studies (University of Calgary) ### Ratios * Current ratio * Profit Margin * Total Debt Ratio * Asset turnover ### Excel Functions * `=PMT(rate, nper, pv, [fv])`: Total payment per period (effective interest rate) * `=PPMT(rate, per, nper, pv, [f...

# MGST 217 Final Exam Study Notes ## Management Studies (University of Calgary) ### Ratios * Current ratio * Profit Margin * Total Debt Ratio * Asset turnover ### Excel Functions * `=PMT(rate, nper, pv, [fv])`: Total payment per period (effective interest rate) * `=PPMT(rate, per, nper, pv, [fv])`: Principal paid this period * `=IPMT(rate, per, nper, pv, [fv],)`: Interest paid this period * `=PV(rate, nper, pmt, [fv])`: Present value (used for annuities or equal sums) * `=FV(rate, nper, pmt)`: Future value of investment * `=RATE(nper, pmt, pv, [fv])`: Finds interest rate per period * `=NPV(rate, values (excluding initial investment})`: Net Present Value for uneven sums, found by subtracting initial investment. Positive NPV is a good sign * `IRR(values {including initial investment})`: Internal Rate of Return, when NPV=0. If IRR is higher than the discount rate perform an NPV to determine whether to invest in the project * `=NPER(rate, pmt, pv, [fv])`: Finds number of payments for an investment, based on periodic, constant payments and interest rate. * `=IF(Logical test, [value if true], [value if false])`: Used for taxes, if EBT >0, multiply it by tax percent, if not then 0 * `=SUMPRODUCT(array 1....) `: Finds the sum of the products of corresponding ranges or arrays ### 35. DUFFLAC Insurance Company DUFFLAC Insurance Company knows from your historical driving record and car repair costs that the following distribution describes the possible damages to your vehicle for the upcoming year. **How much does DUFFLAC expect to pay in damages if they choose to insure your vehicle this year?** | Damages | Probability | Calculation | |---|---|---| | $0 | 0.50 | $0 x 0.50 = $0 | | $500 | 0.30 | $500 x 0.30 = $150 | | $2000 | 0.10 | $2000 x 0.10 = $200 | | $5000 | 0.06 | $5000 x 0.06 = $300 | | $10 000 | 0.04 | $10 000 x 0.04 = $400 | **Sumproduct = $1050** ### What Excel function could we use to calculate this same value in Excel? =SUMPRODUCT(array 1: ….) ## Loan Balance =(Loan amount) + Sum($first principal payment$:First principal payment) ## Profit in Excel * Profit = (Price x Quantity) – (fixed Costs + variable costs) ## GST In Excel/Income Statement * GST collected= Revenue * GST rate * GST paid = (sum of expenses where GST was applied) *GST rate * GST owing = GST collected – GST paid ## Financial Ratios * **Current Ratio:** Current assets/Current Liabilities * Liquidity Ratio that measures short term debt paying ability. HIGHER IS BETTER * **Profit Margin:** Net income/Sales * Profitability Ratio that measures net oncome generate by each dollar of sales. HIGH IS BETTER * **Total Debt Ratio:** Total Liabilities/total assets * Debt(Leverage) ratio that measures percentage of total assets provided by creditors (debt). LOWER IS BETTER * **Asset Turnover:** Net Sales/Total Assets * Efficiency (activity) Ratio, shows how efficiently a company is using its assets to generate revenue. HIGHER IS BETTER ## Excel Errors * **#REF:** When a formula refers to a cell that is invalid ## Effective Interest Rate Per Period * APR/Number of payment periods per year ## R2 - Coefficient of determination - Measures amount of variation in y that is explained by the variation in x - Closer to +-1 the better - Goodness of fit ## Goal Seek - **Set cell:** Has a formula, cell where wanted value is placed - **Changing cell:** Always a number - **Breakeven:** When profit = 0 ## Scenario Manager - **Result Cell** is ALWAYS a formula. - **Changing Cell** CANNOT be a formula. ## VLOOKUP - `=VLOOKUP(Lookup Value, Table array, col_index_num)` - **Table array** must be ascending - **Absolute reference** table array - **TRUE/1:** Approximate match - **FALSE/O:** Exact match ## EAST: - Easy - Attractive - Social - Timely ## Five (5) Types of Risk: * **Environmental:** Due to impact from the environment * **Political and Economic:** Due to political/economic environment where the economy is operated * **Credit:** Exposure to loss from financial transaction * **Market:** Exposure to volatility in the market, commodities, currency. * **Operational:** problems with operations or management information ## MODULE 6 Data Tools ### Digital Analytics (Web Analytics): - The collection, measurement, analysis, visualization and interpretation of digital data illustrating user behaviour on websites, mobile sites and mobile applications - An important component of digital intelligence, digital analytics enables brands and website owners to understand how their sites an apps are being found and used. - Using digital analytics data, companies can optimize the customer experience on their website, mobile sites, mobile apps, their marketing ROI, content offerings and overall business performance ### Filters * In Google Analytics, filters are used by Views to segment the data into smaller groups. * Filters can be used to include only specific subsets of traffic, exclude unwanted data, or to search and replace certain pieces of information * In Google Analytics, filters cannot be applied to your previous historical data; they can only be applied to your data moving forward. Filters are processed in order, so make sure you arrange the filters in the correct order. * In Excel, you are only filtering historical data and you can apply and clear filters in order to view data that meets certain criteria or go back and view the data in its original format at any time * To add a filter, just select a cell in a contiguous range of data (a table of data), click on Sort and Filter -> Filter ### Presenting Data * Excel Charts * Presentation Best Practices * Data Visualization ## What is Important When Communicating? * **Goals of communication:** * Be simple * Be clear * Be concise * Be "blatantly obvious" ## Data Analysis Presentation: * Before presenting data and analysis: * Make sure you understand the data * Make sure you use a suitable analysis * Match the analysis to the type of data you have ## Chart Types and Purposes | Purpose | Chart Type | |---|---| | Shows parts of a whole | Pie | | Compare two or more items | Horizontal or Vertical Bar Graph | | Show changes over time or over other ordered quantity | Line Graph | | Show frequency distribution | Histogram | | Show Correlation | Scatter plot | ## What to Show an Audience: **Show This:** * Results and insights of the analysis (not the data) * Clear labels * Key numbers * Trends **Don't Show This:** * Crowded data * Filtered data without telling audience. * Unsupported information * Make assumptions that you don't state ## Effective Recommendations: * Make a definite *recommendation* based on the data. * Give the *recommendation* first. * Lead with the *conclusion*. * Be confident. * Use strong words. * Explain how the data supports the *recommendation*. * Use a visual to summarize the data. * Tie the *recommendation* to the issue you are addressing. * Stick to the facts. ## An Example of a Good Recommendation: We recommend that a marketing campaign promoting adult helmet use in Alberta should target those living in rural Alberta. Our analysis showed both male and female respondents in rural Alberta were far more likely to say that they never or rarely wear a helmet than those in Calgary and Edmonton. Increased awareness in rural Alberta should improve bicycle helmet compliance. ## Three Guiding Presentation Principles: * **Simplicity:** Is what we're presenting easy to understand for a person with a million things on their mind? * **Creativity:** What can we do differently from the other that will make us memorable? * **Energy:** Are we presenting in a way that will keep the audience awake? ## Good Slides Outline: * **Good slides** are billboards: * Use pictures over words * Are understood in 3 seconds * Less is more * **Present your insights, not your analytic process.** * **5 Elements that should be achieved:** * Flow: Takes you to the *important elements* * Contrast: Helps convey the *message* * White space: Sharpens viewers' focus by *isolating elements* * Hierarchy: Allows you to determine the *most important elements* * Unity: Makes the *message* feel cohesive * Structure your presentation like a story ## MODULE 7: Databases ## What does a Database Do? * Preserves data integrity * Assures that data is consistent, correct and accessible * Eliminates data redundancy * Unnecessary repetition of data that slows data processing * Limits data views (query, report) * Users only see what they need to see, as cleanly and clearly as possible * A database is a self-describing collection of integrated records ## The Structure of Databases ### Entity Relationship Diagram: * Like a blueprint for a database application ### Field (Column or Attribute): * A characteristic about a single item ### Record (Row): * A single item in the collection ### Table (file): * Collection of related items * Title * Contain all the data in the database ### The Database Blueprint: * Entity Relationship Data (ERD) Model * **Data Model:** * A model built before building a database that is a logical representation of database's data. * Describes the data and the relationships to be stored in the database. * **We use an Entity-Relationship (ER) model:** * A popular technique for creating data model. * Graphical * Powerful ## Database Relationships: * Relational databases store data in tables that represent relationships using *keys* (primary and foreign). ### Primary Key: * Column or group of columns that makes *each row unique* in a table * Can be a group of fields * Can be a single field * Cannot have a null value * Should not be an intelligent key * May be multiple choices * Needs to be a unique identifier (attribute whose value is associated with one and only one entity instance (record)) ### Foreign Key: * Non-key column in one table *that links to a primary key* in another table ### Attributes: * Describe the other characteristics ## How to Choose a Primary Key: * Rules for primary key selection: * Choose an attribute that will not change its value. * Choose an attribute that will never be null (empty) * Avoid using intelligent keys (eg. UCID used to be year of application + application number) * Consider substituting single value surrogate keys for large compound keys (>3 or 4 attributes) ## Relationships and Cardinality * **In the Entity Relationship Diagram or database (ERD)** * 1 to 1 (1:1) * 1 to many (1:N) * Many to many (M:N) * **In the Relational Database:** * Primary key linked to primary key (1:1) * Primary key linked to foreign key (1:N) * Foreign key linked to foreign key (M:N) * **MANY TO MANY IS NOT ALLOWED BECAUSE THERE IS NO UNIQUENESS IN THE RELATIONSHIP** ## What is a Compound Key? * When *more than one attribute* forms the primary key * **Needed when:** * A single attribute does not create a unique identifier * When you breakdown a many to many relationship ## Other Definitions: * **Parent Table:** Table to which a foreign key refers to. * **Child Table:** Table which contains a foreign key to another table. * Note: A table can be a parent table in one relationship and a child table in another relationship. * **Orphan Records:** Are records in the child table that have no record in the parent table. * **Referential Integrity:** Enforces that all records in a child table refer to a record in the parent table. *In* other words, *no orphan records* are allowed. ## Scrubbing the Database: * The process of removing or fixing orphan records ## What can we do with a Database? * Retrieve data and ask *questions* of the data * Query = Question * **Query by example (QBE)** * **Structured Query Language (SQL) sometimes pronounced see-kwul** ## Finance and Investment ## Managing Personal Finances: * Set goals, *taking into account* your career, major expenses, and vision for retirement. * Short term goals: next year or less * Medium term goals: 2-5 years * Long term goals: 5+ years * **Control finances and prepare for emergencies.** * Individuals should look for *expenses that can be removed* and opportunities to *increase income*. * **Emergency Fund**: Savings account containing enough money to help one pay for unexpected expenses *without going into debt*. Requires certain changes to *control the finances*. ## Evaluate Alternatives * Different investments have different risks and potential returns. * Investments that best fit the goals must be chosen. ## Implement, Monitor, and Revise * Major life events have the ability to change the *trajectory of plans.* * Individuals should be ready to adjust their plans accordingly. ## Key Takeaway * **Primary steps** to creating a financial plan: * Set goals * Control your finances and *prepare for emergencies* * Evaluate alternatives * Implement, monitor, and revise ## Important Factors for Selecting Investments: * **Investment Horizon**: How soon do you need the money? * Young people have a *longer investment horizon*. * **Risk Tolerance**: How much risk can you handle? * Higher risk *investments tend to be volatile*. * Investors with *limited knowledge of the market* should avoid *riskier investments*. * Influenced by economic conditions. * **Liquidity Needs**: Can I sell this investment quickly? * **Liquidity**: Ease of *converting an asset/investment into cash.* * The easier to convert, the more liquid the asset. * Decisions about liquidity are connected with risk tolerance. * **Asset Allocation**: Process of *spreading money across several different types of investments* to *reduce risk* (diversification). * Expressed in percentages. * **Classes of investments:** * Stocks * Bonds * Cash ## Investment Alternatives * **Capital Preservation:** Investment goal aimed at *avoiding loss of funds* * Bank accounts * Money market accounts * Term deposits or Guaranteed Investment Certificates (GICs) * **Income Investments:** Provide *stead interest or divided payments* and are less volatile. * Government bonds * Corporate bonds or bonds * Shares or stocks (an equity investment) * **Growth Investments:** Investments *that will increase in value* * Shareholders - Investors who buy company shares * Growth Investing - Selling company stocks after it becomes more valuable at a *higher price than the purchase*. * Well diversified stock portfolio: Contains stocks representing a mix of *company sized, countries, and sectors.* ## Mutual Funds and ETFs to Provide Diversification: * **Options to achieve instant diversification:** * **Mutual Fund:** Pools money from *many investors* to purchase a variety of different *securities*. Managed by a portfolio manager. * **Exchange Traded Fund (ETF):** Invests in shares or *other securities* to track the movement of an index. * **Index:** Select list of securities that reflects the performance of a *specific type of investment*. * Most famous *index* - Dow Jones Industrial Average * **Blue Chip Stocks**: Select number of *large cap shares* of industry leaders. Provides shareholders with *stable earnings* over a number of years. * **Large Cap Stocks:** Company shares with a *market capitalization* of over $10 Billion. ## FIGURE 20.2 COMPARISON OF MUTUAL FUND INVESTMENTS | **Saving fund** | **Income fund** | **Growth fund** | |---|---|---| | * Provides low risk investment* | *Provides low to medium risk* | *Higher risk* | | *Provides steady income while preserving the value of the initial investment* | *Opportunity to provide more income than saving funds* | *Potential for higher return on the investment* | | *Flexible term plan* | *Some capital growth over the long term (increase in the initial investment)* | *Long-term investment* | ## Speculative Investments: * Investor hopes to earn a *large profit* in a *relatively short period of time* with *high risk* * Short selling * Buying shares on margin * Stock options and derivatives * Commodities and collectibles ## Investment Returns * Investor goals are based on the *projections of rate of return* * **Rate of return**: Performance of an *investment* measured by taking the *dollar amount of investment gains* and dividing it by the original amount invested, expressed as a percentage. * Higher investment returns result in *higher financial gains*, due to the power of compounding ## Investing Wisely: * Individuals *must have a mix of investments* that matches their *risk profile* and goals. * Investment profile *needs to be updated regularly*. ## Sources of Information: * **Professional Advisory Services:** Available for a fee, Canadian financial institutions such as TD Waterhouse, CIBC Wood Gundy * **Newspapers and Business Magazines:** The Globe and Mail; Report on business, Canadian Business, The Wall Street Journal, Barron's, Bloomberg Business News * **Brokerage Firms:** Financial analysis * **Company Annual Reports** * **Websites:** Interest Rates for CDs, current price information for shares and bonds, experts recommendations ## Five Popular Websites to Help Establish a Successful Investment Program | Sponsor and Web Address | Description | |---|---| | The Motley Fool Web (www.fool.com) | Light-hearted financial advice and step-by-step activities to help you start *an investment program*.| | CNN/Money (www.money.cnn.com)| Current financial news and investment information that can help investors *sharpen their investment skills*.| | MarketWatch (www.marketwatch.com) | Price information about *stocks and funds* along with information about investing, the economy, and personal financial planning.| | Yahoo! Finance (http://finance.yahoo.com)| Current price information and general research information for stocks, mutual funds, and bonds. | | SmartMoney (http://smartmoney.com)| Information about how to save, invest, and spend, along with easy-to-use financial calculators. | ## Time Value of Money (TVM) * **Present Value:** Earlier money on the timeline. * **Future Value:** Later money on the timeline. * **Interest Rate:** "Exchange" Rate between earlier and later money. * **Compounding:** translating earlier money into later money. * **Discounting:** Translating later money into earlier money * **Future Value (FV)n= Present Value (PV)*(1+** ## Net Present Value (NPV) * The financial (non-EXCEL) definition of *Net Present Value* is the present value of *all future cash flows LESS the initial investment*. * Note that the excel function is *only the first part of the calculation* and *that you must subtract the initial investment* to *calculate the true NPV of a project*. * If the NPV of an investment is **POSITIVE**, *then the project is worth undertaking.* ## Internal Rate of Return (IRR) * The *Internal Rate of Return (IRR)* is a discounted rate that makes the present value of estimated cash flows *equal to the initial investment* (NPV=0). * It is the interest rate at which the *net present value* of all the cash flows (both positive and negative) *from a project or investment equal zero*. * *Internal rate of return* is used to evaluate the *attractiveness* of a project or investment. * If the IRR of a new project *exceeds a company's required rate of return*, that project is desirable. * If the IRR *falls below the required rate of return*, the project *should be rejected*. * IRR is when NPV = 0. ## Anatomy of a Loan: Loans: Creating an Amortization Table for Loans * **Typically**, monthly payments are made to cover both the interest and to repay a portion of the principal. * Loans usually state an Annual Percentage Rate (APR) but *the actual rate will depend on the number of compounding periods*. * **Effective Rate = APR / Number of Compounding periods in the year** * Loans can have *fixed or flexible rates*, but we will assume that rates are *fixed for the duration of the loan*. * Can be *secured* (mortgage, car loan) or *unsecured* (credit card). ## Calculations: - **=PMT()**: Total payment each period - **=IPMT()**: Interest paid this period - **=PPMT()**: Principal paid this period - **Loan amount** = purchase price LESS: down payment - **Payment** is found by =PMT Function - **Rate**: effective rate - **Nper**: (number of payments) found by multiplying the *term* by the *number of payments per year*. - **Pv**: (present value) which is the *loan amount*. - **Principal payment** is found by =PPMT Function - **Per**: the current period - **Interest Payment** is found with the IPMT function * Can be generally be *copied across* but the "per” function needs to be *changed to the correct cell* because of *relative cell referencing*. - **Total payment**: Found by adding principal and interest payments. - **Remaining loan**: Found by *taking the loan amount* and *using the SUM() function* with an absolute reference on the principal payment: *the principal payment* so it can be copied down. * Ex. =$C$4(loan amount) +SUM($F$3:F3) ## Sensitivity Analysis Data Tables * Column is up and down. * Row is left to right. * If looking at interest rate changes, reference APR in table. ## Amortization Table: * Shows total payment each month, the amount that goes towards interest and the amount that repays principal. * After each payment the total liability is reduced, resulting in less interest, the following payment period and more of the total payment going towards principal. * For any period, you can find the amount of interest paid (expense) and the principal remaining on the loan (liability). * Absolute cell referencing is needed in order to copy formulas correctly down each column. ## Financial Functions ### Effective Interest Rate Per Period Vs. Annual Interest Rate * Often, the interest is compounded more frequently than annually, even though the rate is stated as an annual rate. | **Compounding Period** | **Yearly** | **Monthly** | |---|---|---| | Nominal Interest Rate, (i)(sometimes called the APR) | 5% | 5% | | Payment Periods (n) | 1 | 12 | | Interest Rate per period (i/n) | 5%/1=5% | 5%/12 = 0.417% | | Effective Annual Interest Rate | = 5% | = 5.116% | ### Canadian Mortgages: * By law, all Canadian mortgages are compounded semi-annually (every 6 months) but man consumers chose to pay monthly, semi-monthly, or bi-weekly. * Do not operate like loans. * Compound period is twice a year while payment period can vary. * **Effective Rate = (1 + r/2) ^ (2/n) - 1** ## Net Present Value (NPV) in EXCEL * The formula is for uneven cash flows. * `=NPV(rate, values)` where *values* is the uneven cash flows. * To find mathematical NPV the initial investment must be subtracted. ## Present Value (PV) in EXCEL * For annuities (equal cash flows) * `=PV(rate, nper,pmt)` ## Internal Rate of Return (IRR) in Excel * `=IRR(Values)` ## Some Excel Argument Definitions: - **Rate:** The interest rate per period. - **Nper:** the total number of payment periods in an annuity. - **Pmt:** The payment made each period and cannot change over the life of the annuity. - **Fv:** Future value or cash balance you want to attain after the last payment is made. - **Type:** 0 or omitted: payments are due at the end of the period, if 1 the payments are due at the beginning of the period. ## Negative Cashflow Cycle: * When a company must pay manufacturing expenses *before receiving payment* from customers. ## Interest Spread: * Refers to the difference between the *rate banks charge for loans* and the *rate paid on deposit accounts*.