How to create a Project Timeline Template today in 1. Excel 2. 01. 0 – Launch Excel. If you manage projects it’s important to show how many tasks there are and when they’re due to happen. This helps to explain key events in your project’s life to team members and other stakeholders. In my experience I’ve seen project managers show project tasks using Gantt charts, but here I offer a compelling alternative – the simple project timeline tool – which you can create in Excel. A project timeline shows how events relate to each other in time. Working with Tables in Excel 2013, 2010 and 2007 Introduction. This article has been published on the Microsoft site (in Dutch) With the release of Excel 2007. In addition to showing how to create a Pareto chart in Excel, we’ll take a look at several types of modifications that can be made to the chart – such as using a. Interested in learning more about Excel VBA? MrExcel will teach you how to use Microsoft Excel VBA. Check out our site for more information about Excel VBA tutorials. Access Labyrinth Learning's Microsoft Excel Online Learning Resources, click on the link next to your book title below. Download Student Exercise Files.Although timelines don’t look as sophisticated as Gantt charts, they do a really good job at communicating the big picture. A well crafted project timeline can convey useful information relating to task progress and overall project progress. In this article I’ll show you how to make a timeline in Excel. And if you want to buy a copy you can find the purchase page by clicking here. Easy Project Management using Microsoft Excel (Series)This is the second part in a series on Project Management using Microsoft Excel. To read the introduction click here. You should also check out the follow- up article “5 bonus ideas that will make your Project Timeline Template even more useful” which you can find by clicking here. I’ll add more articles in future months so be sure to bookmark this page and come back, or subscribe to my email newsletter to get free updates – you also get a free PDF with over 1. Excel shortcuts if you subscribe on this page. Project Timeline Template Overview Video. Explore research at Microsoft, a site featuring the impact of research along with publications, products, downloads, and research careers. Share, organize, and discover information with Microsoft SharePoint. Learn about SharePoint Online, OneDrive for Business, Yammer, and Apps for SharePoint. We will get to the detailed how- to soon but first you should watch this 6 minute video. It gives an overview of the timeline template. The downloadable template contains the completed timeline, ready for you to use for your own projects. Also included are the two tutorial videos. Click image to buy timeline template. I have an excel workbook with a couple of sheets, where the first sheet is a master sheet. The master sheet has the names of all the categories and then in the first. In this excel pivot tables tutorial we will learn what is a pivot table and how to make a pivot table using Microsoft excel. Excel pivot tables are very useful. If anybody asks me what is the best function in excel I am drawn between Sumproduct and Data Tables, Both make handling large amounts of data a breeze, the only. I created the timeline template with “before” and “after” worksheets for every step in this how- to article, and the worksheets match the sections of this article to make it easier for you to follow along while reading. Note: Timeline Template & Instructions were prepared using Excel 2. The instructions in this article are written for Excel 2. Excel I used to make the template. If you have another version of Excel you should be able to follow along, but I can’t guarantee the steps will be the same. In fact if you try this out with Excel 2. Excel 2. 00. 7, please let me know if it works for you – leave a comment in the comments section below. Thanks! Watch my Video Tutorial here. I’d suggest you set aside half an hour to watch my detailed step- by- step video instructions – ideal for watching in a lunch break. This will help you picture the end result so you have a target to aim for. My finished timeline shows orange “At Risk” bars, which I won’t explain in this post. You’ll need to read my next blog post where I’ll explain how to add these (coming soon). Excel Timeline Step 2 – Create your Project Events Table. The timeline template has project events laid out as a table in Excel. I split the example project into four phases, and each phase has a certain number of sub- tasks. This image shows the pure data with no timeline chart. Each event is given these values: Phase – Phase 1, Phase 2, Phase 3, Phase 4 or “Today”. This show which phase of the project the event falls under. The rows labelled “Today” are used in step (9) “The Today Line” when we add a vertical line to show where we are today. Type – Phase, Task or “Today”. This shows the type of event, whether it is a project phase or a task. The rows labelled “Today” are used in step (9) “The Today Line” when we add a vertical line to show where we are today. Start Date – This is the event’s start date. Event name – This is the name of the event that shows up in the chart data series labels. Duration (days) – This is the duration of the event measured in days.% Complete – Shows the % complete for each task and phase. Phase % complete is an estimate and not calculated with reference to task completion. You will need to apply your own rules to assess the Phase % complete. Completion (days) – This is the duration of the event x %Complete. A task that lasts 1. Height – The height value is important because it determines how high or low the event is displayed in the timeline chart. You can set any height values you want, but it’s a good idea to stick to small numbers near to zero. I used numbers between +2. If the height is +ve the event shows above the X- axis. If the height is - ve the event shows below the X- axis. Excel Timeline Step 3 – Insert XY Scatter Chart & add your first data series “Duration”Insert XY scatter chart and add Series “Duration”It’s time to insert a chart to visualize the project event data you created in Step 2. I used an XY scatter chart as it allows us to be flexible with the position of data points on both X- axis and Y- axis. In Excel 2. 01. 0 here are the instructions you need to follow: Click on the Insert Menu. In the Charts group, click on Scatter. Select Scatter with only Markers (the first option) and a blank chart appears on your worksheet. Reposition that to cover the range B4: K2. ALT button to snap the edges to cell borders for precision alignment)Right click the empty chart and click on Select Data. Add a data series by clicking on the Add button. For series name select E3. Duration”)For Series X values select C3. C4. 6 (“Start Date”)For Series Y values select H3. H4. 6 (“Height”)Click on OK to close the Add data series dialog. Click on OK to close the Select Data dialog. This gives us a simple scatter chart with default formatting. Let’s format the event markers to green diamonds: Click on a data point to select the Series “Duration”Right click on any data point selected, and select Format Data Series. Click on Marker Options, and select the Built- in marker type diamond. Change the size to 1. Click on Marker Fill, and select Solid fill. Change the Fill Color to green. If you require detailed step- by- step instructions, watch my video from 2 min 3. Step 3). So we have created a scatter chart with one data series called “Duration”. And at the moment it only shows the start date with a green diamond marker. Change the title of the chart by double clicking the textbox – enter something like “Project Timeline”. We’ll add the actual event durations in days in step 5 “The Error Bars” by adding horizontal X- error bars, but first we should clean up the chart formatting. Excel Timeline Step 4 – Clean up the chart formatting on your Timeline Scatter Chart & Add Event Labels. Clean up the chart formatting by removing items we do not need. OK, let’s clean up our chart. You can see in the screenshot above that I got rid of some things we don’t need: Y- axis – Hide the vertical Y- axis because it does not add any information to this chart. Horizontal guidelines – Hide the horizontal guidelines because they do not add any information to this chart. Legend – Get rid of the default chart legend because we will replace it with something that looks better in step 1. The Chart Legends”. I also chose to display Data labels and position them left of the data points. In Excel 2. 01. 0 a nice way to do all the above is to use the Chart Tools menu. Many of you will be familiar with changing these options so you won’t need any further guidance. But if you want further instructions just watch my video from 5 min 3. Step 4), and I take you through each step I used to clean up the chart formatting with the Chart Tools menu in Excel 2. How to change the text on the event labels. Changing the text for every event label on the timeline is quite tricky and time- consuming. This is because you need to select each and every label in the data series, then relabel it. You can watch me to this in my video from 6 min 4. In the video I sped up the process x. Here are instructions for how to change the event label text: Click on an Event label, to select the labels for the whole data series. Don’t click on the green diamonds because that selects the data points. Click on the Event label again, to edit just that one single label. Press the = key, or click in the Formula Bar. Either type in the cell reference for the Event label, or click on the cell with the Event label. Repeat the first 4 steps for the rest of the Event labels. If you find this step too difficult or cumbersome, you could use VBA to make it faster – and I revisit this idea in my followup article (click here). How to Freeze Panes. I also decided at this stage that it would be useful to freeze panes at row 2. Doing this keeps rows 1 – 2. This means we’ll always be able to see the timeline chart. To freeze panes in Excel 2. Select cell A2. 8Click on the the View menu in the Ribbon. In the Window group, click on Freeze Panes. In the drop- down select “Freeze Panes“This adds a black horizontal line above row 2. The scroll bar will only move what’s below the line. Excel Timeline Step 5 – Add Error Bars to bring your Timeline Template to life. Add X Error Bars to show task / phase duration. After step 5, your scatter chart will start to look like a timeline as we add grey bars to show how long each event takes. The way to do this is using X Error Bars: Select the scatter chart. In the Chart Tools menu group in the Ribbon, click on the Layout menu. In the Current Selection group, select the Series “Duration” from the drop down. In the Analysis group, click on Error Bars, then click on More Error bar options. This brings up the Format Error Bars dialog box. Back on the Current Selection group, make sure the Series “Duration” X Error Bars is selected. Excel may bring up the Y Error Bars instead, but we want the X Error Bars for this step. Look at the Format Error Bars Dialog box and make sure it says “Horizontal Error Bars“Set the options: Direction = Plus, End Style = No Cap. For Error Amount, click on the radio button labelled “Custom“, then the click on the button “Specify Value“For Positive Error Value, select the range E3. E4. 6 and click OKNote: Keep the Format Error Bars dialog box open. Microsoft Excel Time Value Function Tutorial - Lump Sums. Excel (and other spreadsheet programs) is the greatest financial calculator ever made. There is more of a learning curve than a regular financial calculator, but it is much more powerful. This tutorial will demonstrate how to use Excel's financial functions to handle basic time value of money problems using the same examples as in the calculator tutorials. I will keep the examples rather elementary, but if you already understand the basics of using Excel, this tutorial will help you to understand the financial functions. An Excel spreadsheet can be downloaded that contains each of the examples shown in this tutorial. For more advanced Excel functionality, please see my Excel pages and/or my Excel Blog. If you have any questions or comments, please feel free to contact me. Analogy to Calculator Financial Keys. All financial calculators have five financial keys, and Excel's basic time value functions are exactly analogous. The table below shows the equivalency between the calculator keys and Excel functions: Purpose. Calculator Key. Excel Function. Solve for Number of Periods. NNPer(rate, pmt, pv, fv, type)Solve for periodic interest rate. I/Yr. Rate(nper,pmt,pv,fv,type,guess)Solve for present value. PVPV(rate,nper,pmt,fv,type)Solve for annuity payment. PMTPMT(rate,nper,pv,fv,type)Solve for future value. FVFV(rate,nper,pmt,pv,type)Just as you have to supply at least three of the variables to solve a TVM problem in a financial calculator, you also have to supply at least three of the arguments to each Excel function. Note that in the table, the bold function arguments are required while those in italics are optional. You don't need to memorize the order of the function arguments. You can use the Insert Function dialog box, which will prompt your for the arguments by name. Or, if you prefer to type the function directly, Excel will display a Smart Tag that shows the order of the arguments as you type. Example 1 - Future Value of Lump Sums. We'll begin with a very simple problem that will provide you with most of the skills to perform financial math using Microsoft Excel: Suppose that you have $1. How much will you have accumulated at the end of this time period? In this problem, the $1. PV), NPer is 5, and Rate is 1. Open a new workbook and enter the data as shown below, but leave B5 blank for now. To find the future value of this lump sum investment we will use the FV function, which is defined as: FV(rate,nper,pmt,pv,type)Select cell B5 and then type: =FV(B3,B2,0,- B1) and then press Enter. The answer that you get should be 1. A Couple of Notes. Every time value of money problem has either 4 or 5 variables (corresponding to the 5 basic financial variables). Of these, you will always be given 3 or 4 and asked to solve for the other. In this case, we have a 4- variable problem and were given 3 of them (Nper, Rate, and PV) and had to solve for the 4th (FV). Be sure that any variables not in the problem are set to 0, otherwise they will be included in the calculation. In this case, we did not have an annuity payment (PMT), so the third argument in the FV function was set to 0. Note that we left out the optional Type argument. In all of these functions, the Type argument tells Excel when the first cash flow occurs (0 if at the end of the period, 1 if at the beginning). This argument is identical to setting your financial calculator to End Mode or Begin Mode, and only affects the answer when there is an annuity payment. When solving lump sum problems such as this, the argument has no effect. If you had typed =FV(B3,B2,0,- B1,1) you would have gotten the same answer. Note that, unlike most financial calculators, there is no argument to set the compounding frequency. This is actually a good thing, in my opinion, because those settings on financial calculators cause all kinds of trouble when people forget to set them correctly. In Excel functions, you must set NPer to be the total number of periods, Rate to be the interest rate per period, and PMT to be the annuity payment per period. So, if this problem had said that the compounding was monthly (annual was implied), then we would have typed =FV(B3/1. B2*1. 2,0,- B1). Note that our interest rate (in B3) was entered into that cell as 0. This is different than financial calculators. In a calculator, your interest rate would be entered as 1. The calculator automatically divides the number entered into the interest rate by 1. Excel makes no adjustment to Rate, so you must enter it as a decimal. Had you entered 1. B3, the future value would have come out to $1. That's because Excel would think that your interest rate was 1,0. Notice that we entered - B1 (- 1. PV argument in the function. Most financial calculators (and spreadsheets) follow the Cash Flow Sign Convention. This is simply a way of keeping the direction of the cash flow straight. Cash inflows are entered as positive numbers and cash outflows are entered as negative numbers. In this problem, the $1. Had you entered the $1. This would be correct had you borrowed $1. We can change any of the variables in this problem without needing to re- enter all of the data. For example, suppose that we wanted to find out the future value if we left the money invested for 1. Simply change B2 to 1. B5 is 2. 59. 3. 7. Please note that it is important that you always use cell references in your formulas. Never type a number directly into any formulas or Excel functions (unless that number will never change). If you do type numbers into formulas, then you will have to remember to change each formula that relies on that number or else you will get errors. The best practice is to always have an . Then, each formula or function that you use will get its values by referencing cells in the input area. That was for ease of explanation. I should have added a row with the label . One important thing to remember is that the present value will always (unless the interest rate is negative) be less than the future value. Keep that in mind because it can help you to spot incorrect answers due to a wrong input. Let's try a new problem: Suppose that you are planning to send your daughter to college in 1. Furthermore, assume that you have determined that you will need $1. If you believe that you can earn an average annual rate of return of 8% per year, how much money would you need to invest today as a lump sum to achieve your goal? In this case, we already know the future value ($1. We want to find the present value. Create a worksheet like the one below: We need to use the PV function, which is defined as: PV(rate,nper,pmt,fv,type)So, select B5 and enter the formula: =PV(B3,B2,0,- B1) and see that you would need to invest $2. That is a lot of money to invest all at once, but we'll see on the next page that you can lessen the pain by investing smaller amounts each year. Alternatively, if you are willing to take on considerably more risk then you might be able to earn, say, 1. If you change the value in B3 to 1. Example 1. 2 — Solving for the Number of Periods. Sometimes you know how much money you have now, and how much you need to have at an undetermined future time period. If you know the interest rate, then you can solve for the amount of time that it will take for the present value to grow to the future value by solving for N. Suppose that you have $1,2. Assume that you can earn 9% per year on your investment. This is the classic type of problem that we can quickly approximate using the Rule of 7. However, we can easily find the exact answer using the NPer function. As the name suggests, this function is designed to solve for the number of periods and is defined as: NPer(rate, pmt, pv, fv, type)Create a new worksheet and enter the data shown below: Select B5 and type: =NPER(B3,0,- B1,B2). You can see that it will take 8. One important thing to note is that you absolutely must enter your according to the cash flow sign convention. If you don't make either the PV or FV a negative number (and the other one positive), then you will get a #NUM error instead of the answer. That is because, if both numbers are positive, Excel thinks that you are getting a benefit without making any investment. If you get this error, just fix the problem by changing the sign of either PV or FV. In this problem it doesn't really matter which one is negative. The key is that they must have opposite signs. Note that in this problem we wanted to know how long it will take to double your money at 9%. The exact numbers for the PV and FV don't matter as long as the FV is exactly twice the PV. You can prove this by changing the PV to 1 and the FV to 2. You will get exactly the same answer. Example 1. 3 — Solving for the Interest Rate. Solving for the interest rate is quite common. Maybe you have recently sold an investment and would like to know what your compound average annual rate of return was. Or, perhaps you are thinking of making an investment and you would like to know what rate of return you need to earn to reach a certain future value. Let's return to our college savings problem from above, but we'll change it slightly. Suppose that you are planning to send your daughter to college in 1. Furthermore, assume that you have determined that you will need $1. If you have $2. 0,0. Finding the interest rate in a time value of money problem requires the use of the Rate function, which is defined as: Rate(nper,pmt,pv,fv,type,guess)Note that the Guess argument is rarely required and is optional. In most cases, you can just leave it out. It is included for those times (only when dealing with annuities) when there are two or more solutions to the problem. Typically, this only happens when you are dealing with uneven cash flows and there are sign changes in the cash flow stream. It can occasionally happen in annuity problems, when the FV has a different sign than PMT. Create a new worksheet and enter the data as shown above. Select B5 and enter the Rate function: =RATE(B3,0,- B1,B2).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
August 2017
Categories |