Expand all | Collapse all

Excel for Business Use

  • 1.  Excel for Business Use

    Registered Piano Technician
    Posted 03-26-2020 18:47
      |   view attached

    This is a continuation of "preparing for post covid-19".  That discussion has gotten too out of control with off topic digressions so I'm starting this separate thread.  Please don't get into covid policy discussions here.  Let's keep the discussion uncluttered.  Start your own thread or go to facebook or some such place if you can't help but want to talk about it.  Sadly I cannot edit out off topic posts so please think before you hit the send button.

    Attached is an excel spreadsheet for use with business.  It's laid out with some sample data so you can see how it works and data is transferred between sheets (in excel each separate page is called a sheet, the entire collection of sheets is called a book).  Save this sheet to your computer and then rename it and save it again in case you screw it up so you have an unaltered copy with which to start over.

    If you click on any cell with data you can see how they are programmed.  Letters refer to columns and numbers refer to rows.  So G20 means column G, row 20.  If there is nothing in the cell but a number or a name then that means it's a direct entry cell and there is no programming.

    The programming is mostly of two kinds on these sheets: summing numbers, or transferring data from one sheet to another sheet.  We can cover the most basic functions like naming cells, making or inserting rows and columns (or deleting them), what are rows and columns, etc.  There are no questions which are too basic or unwelcome.  Consider this an opportunity to learn the program and take advantage of it.  I am not an expert on excel but I know enough to create sheets for a variety of applications including calculations for use with touchweight and some engineering applications as are used in soundboard design.  We can get there too if you want to.  The sheets are as follows and can be seen as tabs at the bottom of the page.

    Sheet 1: Income

    Here all receipts are logged with taxable amounts in a separate column and a tax rate column with the taxable amount.  Some of us live in areas where there are multiple tax rates and so you have to keep track of that.  If that's the case you will have to add a column.  I can describe how to do that later if you don't know how.

    On the right side of that sheet is a place to log mileage.  If you scroll down to row 374 you will see the totals as well as the totals for each quarter that are used for sales tax reports.  Since the number of deposits will vary in any given quarter some programming may have to change.  It's not hard to do.  If you have questions then ask me on or off line.  For now I've set it up so that through row 100 is totaled for the 1st quarter, row 101-200 is totaled for the 2nd quarter, row 201 - 300 is totaled for the 3rd quarter, and row 301 to 369 is totaled for the 4th quarter.  Adding rows and changing the programming is easy once you know how to do it.  Again, click on the cells with the totals and you can see how the programming is written at the top of the sheet.  Total deposits are transferred to Sheet 3 (Report) which I'll cover below.

    Sheet 2: Expense

    This is pretty self explanatory.  Date of payment, payee, method (check, eft, credit card), and the amount in the appropriate column are entered.  The totals are all the way down at row 543.  You can shorten the page if you want, it's a bit long, but I keep track of every expense no matter how small.  These totals are also transferred to the Report sheet.

    Sheet 3:  Report

    Here everything is collected from the other sheets and totals are given.  At the end of the year all you have to do is print this out and hand it to your accountant and they will have everything they need.  The groupings for the most part follow turbotax deductions for business.  You'll see I have separated out the office in home expenses which are calculated as a percentage of total costs versus direct costs.  That makes it easier for you or the accountant to make those distinctions.

    The mileage part on the right side transfers the actual mileage you enter from the income page.  It also has a place to enter you beginning mileage and ending mileage on the car and then it will calculate the total mileage and the percent of business mileage.  The costs for parking and tolls are entered on the Expense sheet and those are transferred to the report page with their own section.

    At the bottom of the Report sheet I have a place to log your estimated tax payments which you or your accountant will also need.

    This sheet is also good if you need at any time during the year to provide a profit loss report for applying for a loan or line of credit.  This is basically the info they will need.

    Feel free to contact me on or off the list if you have questions and I will be happy to answer them when I can.  I do have some work right now so may not get back to you instantly but I will.  If you want to discuss the issues or offer suggestions in the thread that's fine too.  But I really ask people to stay on topic.

    If there are ideas related to things we can do to prepare for the eventual reopening of our businesses to get them up and running as fast as possible then those suggestions are also welcome.  I won't be commenting on the original thread anymore.

    I do think it's important for us to plan ahead.  It's quite possible that round two of shutdowns could take place again in the fall if the virus decides to make a comeback even if it if slows over the summer.  I don't want to get into the pseudo science of whether and why that may or may not happen.  But it is worth considering how we can prepare for that eventuality.  If it doesn't happen great, if it does then we don't want to be caught unprepared.

    Folks who are incorporated may have different requirements and I'm sorry I can't address those as I am not one of those people but data collection principles are similar.

    Thanks.  Take some time to explore and feel free to post any and all questions or contact me directly.

    David Love RPT
    415 407 8320


  • 2.  RE: Excel for Business Use

    Posted 03-26-2020 19:17
    Dear David

    Thanks for this template - working templates like this are gold-dust and save us having to reinvent wheels.

    For anyone who doesn't have Excel, Open Office is brilliant  https://www.openoffice.org/download/ 

    Best wishes

    David P 

    - - - - - - - - - - - - - - - - - - - - - - - -
    David Pinnegar, B.Sc., A.R.C.S.
    - - - - - - - - - - - - - - - - - - - - - - - -
    +44 1342 850594