A new version of this post has been published on my new site: http://easy-excel.com/?p=186
This is something that happens to all of us a little too often: Your boss asks you to make a simple report of yesterday’s sales, last month’s incoming calls, next year’s expenses or something like that.
Here’s how to do it in Excel in 5 minutes:
1. Categories (2 minutes)
Decide which categories you want to include in the report. Always start on the third row (leave room for the headlines on row 1 and 2). I have chosen the categries Product, Product ID, Units, Cost Price, Sales Price, Total Cost Price, Total Sales Price and Profit:
2. Enter the data (2 minutes)
3. Formulas (1 minute)
Now, finally the fun stuff: The formulas!
How do you calculate the total cost price of the first product? The number of units multiplied by the cost price for each unit. For the first product, the GE-1121, we would expect 3 x 560 = 1680. So then we have to multiply the value in cell C4 with the value in D4.
Important: You always start with the equal sign (=) in Excel. Here’s what you type in F4:
To calculate the sales price:
And finally the profit: That’s even easier – the profit is the sales price minus the cost price:
You only have to write each formula once! Simply move the mouse to the little square on the right side of the rightmost cell and drag down. This will copy all the formulas all the way down.
And here’s the report that shows profit per product:
In my next post I will make it look a little nicer, with some number formatting and colours. In the post after that I will calculate totals and subtotals with some very easy-to-learn functions that will impress your colleagues!