How to Use Excel to Calculate Year to Date Figures

Save

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.

Related Searches

References

  • Photo Credit Jupiterimages/Photos.com/Getty Images
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!