Get 50% OFF QuickBooks for 3 months*

Buy now
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements
Work smarter and get more done with advanced tools that save you time. Discover QuickBooks Online Advanced.

Reply to message

View discussion in a popup

Replying to:
jeffhhpllccpas
Level 2

Reply to message

I found a workaround kind of pain in the butt to setup, but it works for now I hope. 

1. Export your product/service list report to excel, delete the qb header and then make a table out the column headers and listings of it once in excel.  Keep this open.

2. Copy estimate to purchase order (chose this because it doesn't hit financials if you mess up) from estimate you want to see markup.

3. In vendor box on the purchase order type in "Estimate Markup" and add as new vendor.  In the purchase order number box put something like the estimate number or something you can associate with the estimate you are wishing to see markup and then save and close.

4. Run an open purchase orders detail report.  I customized it to group by vendor, report period of month to date (you can change this), and then just have date, num, vendor, product/service, memo/description, account, qty, rate, and total amount as the coumns.  Save this customization and name it whatever you want.  I named mine "Estimate Markup Report - Export to Excel".

5. Export to excel and then copy and paste into a new tab in the excel you still have open from earlier, named the tab Estimate Markup.

6.  Add two extra column headers to the right of total amount named sales price and markup percentage.

7.  In the cell immediately underneath the sales price input a vlookup formula for the table on the "Product Service List" Tab.  The formula that worked for me was =VLOOKUP(E7,'Product Service List'!$A$1:$E$7844,4,FALSE)  

8. Once the formula is entered click on the cell in which it is entered, should be K7, and hover over bottom right until you get a little cross.  Once you get the cross, left click and drag down a bunch of rows (that way when you copy and paste a future estimate markup report into it it should just fit right in.

9.  In column L on the same tab (Markup Estimate), change the formatting to percentage, then create a formula for the markup percentage.  I used =(K7-I7)/I7

10. You should then have everything needed to see the markup percentage by item on your estimate.  The good thing about doing it this way is you can see multiple estimates at one time as long as they are all have the vendor as Estimate Markup Report.  Once you are good with what you see you can go back in and delete the purchase order and create a new one with correct vendor or just change the vendor, but you can then print and save as pdf for future reference.

11.  The last thing is if you have a new product or service, you could add it manually to the excel and update the formula for the additional row or you can import the product/service list again into the excel and do as we did above.

 

Hope this helps!

Need to get in touch?

Contact us