Your boss wants to know how well your division is doing and you need to give her the answers. Calculating year-to-date numbers is common in business because people want to know how well, or poorly, things are going at a particular moment in time. Figuring the YTD requires using the date calculation features in Microsoft Excel to figure the difference between two dates.
Add up all the data you want to see in a YTD format. Use the "SUM" feature in Excel to add all the cells in a range. For example, if you entered all of your yearly sales in column "B" by month, you would want to sum up cells "B1" through "B12" so you would click in cell "B13" and type: "=SUM(B1:B12)" then press "Enter." This will provide a total for all the sales to this point in the year.
Create two date fields on your speadsheet. One is the last day of your fiscal year, for example enter "12/31/2011" the other is the current day. Say that cell "C1" is the last day of the year and "C2" is the current date. Compare the two dates by using date arithmetic in Excel. Find the number of days between the two dates by entering the following formula in a blank cell: "=DAYS360(C2, C1)" then press "Enter." Find the percentage of the year currently gone by entering the formula: "=360-DAYS360(C2, C1)/360" then press "Enter."
Analyze your YTD number against the date information you generated. For example, to find your YTD percentage to your goal you would need to add a cell for your yearly goal. In this example, put the yearly goal under the YTD sum, so cell "B14" would contain the yearly goal. Enter the following formula in an empty cell to find the raw percentage from YTD to your goal: "=B13/B14" then press "Enter."
Compare the percentage of YTD to goal with the percentage of the year currently gone in another cell. For example, say your percentage of the year passed is in cell "C3" and your percentage of YTD sales to your goal is in cell "B15" you can enter the following formula to compare the two: "=B15/C3" then press "Enter." This will give you the percentage over or under your goal you are at this point in time, so if you are doing better YTD than your yearly goal you might see, "127%" but if you aren't meeting your YTD goal you might see, "62%" displayed.
- Photo Credit Jupiterimages/Photos.com/Getty Images
How to Sum a Column in an Excel Spread Sheet
Many complex tables or spreadsheets that you may create are based on the basic principle of summing a column or row of...
How Do I Calculate YTD Income?
How you calculate YTD income differs depending on whether you are calculating it for a business or for an individual.
- How to Calculate a Fiscal Year