Sunday, April 17, 2011

Spreadsheeting the Love

iWork Numbers Invoice
[Edit: I've added a link to the original Numbers document in addition to the Template version. Enjoy!]
Hand-Made Mac Tip No. 15
Taking the Spreadsheet to the Next Level - Spreadsheeting the Love

Did you know you can create a customized template with iWork Numbers?

(Please note: there is no 's' on the end of iWork. The application that preceeded it was called AppleWorks and that did end with an 's.' Sorry, pet peeve of mine.)

Did you know that Apple's iWork Numbers is the equivalent to Microsoft's Office Excel?

Did you know that Numbers can do more than just create a  plain old spreadsheet? You can actually design something extremely usable with Numbers!

I've been working on this project for some time now and I'm pretty pleased with the results. I'd like to share it with you and would love some feedback if you decide to use it and modify it to suit your own needs. I've created a customizable invoice that will calculate time and pricing using a series of sliders and cells where you enter variable information.

Sure, Numbers does come with its own ready-made templates for which there are invoices. I took a look at them first to be sure I wasn't reinventing the wheel. I didn't like any of them. They just didn't do what I wanted my invoice to do for me. Like with many of our technological advances, when you can't find it, you build it.

Here is a screen shot of what one of my
TheMacMommy invoices looks like.

Click to view larger.
So, this is what I've built: a client invoice to send to my clients showing them how much they owe me and how I arrived at the total. I also use it to document what we worked on during our sessions together. Tech support is not cheap. I at least try to make it affordable for my clients so they call me back again and again.

Until I find a better solution, I've been using PayPal to invoice my clients so they can pay me online using their credit card or bank account. Most of my elderly clients still prefer writing out a check, which is fine and I can deposit it to my bank account using my iPhone camera. That method is free, and takes a bit to clear, while PayPal is somewhat convenient and helps me keep better accounting for my taxes. I still don't like the cut PayPal takes though (even though I realize I'm paying for convenience) and pretty soon I'll be needing to hire some part time childcare and will need that money for a babysitter.

The way it's working for me is I first create an invoice for my client using the Numbers document I've built. I plug in all the variable information and type up my session notes. Then, I log in to PayPal and use the custom invoice template I created there and plug in the same information regarding fees, rates, hours and dates (not enough room for session notes though). If the totals match up, then I know I've got everything accounted for. That makes me feel more confident to bill the client.

I saved a copy of my customized invoice and created a generic version so you can make changes to it that suit your needs like using your own company colors, fonts and logo. Basically, all the variable text or cells you need to interact with have blue text. Other cells where the text is black or a shade of grey are automatically calculated based on what you modify in the cells with the blue text. When you click on the cells with blue text, you will be presented with either a slider or a drop-down menu with different choices. Adjust the values and watch other cells update automagically! Try it! If you use the Inspector tool, you can adjust those settings and enter your own values. I thought this was such a cool feature!

Here is where you can download the Numbers Template File I created using iWork Numbers '09 version 2.0.5. If you want to original Numbers document, you can download it here.

I'd love it if someone could try this out on an iPad and let me know if it works!

Once I've created an invoice for a client, I then save it as a PDF file using the nomenclature of YYYYMMDD_LASTNAME (of the client). The date I use is the date of the invoice to make it easier.

This screen shot shows you the path of where you should save the template file. Once the file is placed in your Home/Library/Application Support/iWork/Numbers/Templates/My Templates folder, you can then launch Numbers and select File/New from Template Chooser/My Templates (from the sidebar)/Generic Consulting Invoice.

Click on the image to enlarge it so you can read the path at the bottom.
Development
This all started with a spreadsheet I created in Numbers. Then one night, I asked my science teacher of a husband to help me adjust the formulas to make the cells calculate time and present me with a  total. We both kept tinkering with the settings and features in the Numbers Inspector and finally beat it into submission.

Every couple of days I would just keep tweaking it and moving cells around and adjusting formatting until I felt it could be a viable tool to use in my consulting business. Sure, I KNOW there is an app for this...I know there are probably over 50 that might do it on my iPhone easier. I just had much more fun creating this on my own. I can only imagine this might be somewhat similar to what application developers might experience when they create an app. I enjoy working with the user interface of iWork products. I think they are beautifully designed and very intuitive.

Every time I use Numbers or Pages, I'm always amazed at what these applications have to offer. (I wish I had more use for Keynote because it has a gorgeous interface!) Having come from a printing, production and graphics background using Pro applications like Quark Xpress and Adobe InDesign, that's saying a lot. I used to consider myself a real snob and couldn't be bothered with the inferior AppleWorks or other lowly "desktop publishing" applications. I loathed converting files from programs like "Punisher" — which was my pet name for MS Publisher. I just hated the interfaces and it rose my blood pressure whenever I had to take some layperson's "design" and make it print-ready. Despite being damn good at it, I didn't like being a Production Artist.

Years ago, I cringed whenever I heard the words "desktop publishing." I used to correct people whenever they asked me if I was a "desktop publisher." I would correct them by saying, "No, a desktop publisher would be my little brother making greeting cards on his PC. I use a Mac and have a Bachelor of Fine Arts degree in Communication Design, therefore I am a designer." (and you have to say it in a really snobby voice and elongate the "i" sound in deeeee-siiiiiiigner.)

Much of that changed when I exited the printing industry and started honing my technical skills. I realized that maybe it wasn't all that bad being a Production Artist and started focusing more on my skills in the areas of production, training and troubleshooting. When I started working in the education field as a site technician, I no longer had the luxury and access of using Adobe products like Photoshop or Illustrator. I had to make do with whatever the schools could afford and so I forced myself to become proficient in using the Apple products that came with the machines or could be had for educational pricing. Even though I could still use my creative side, it was the enjoyment I found in teaching staff and students how to use creative applications like AppleWorks or even TuxPaint. I learned how to embrace desktop publishing and ironically, became an advocate for it. Working in a school setting, in my opinion, really teaches you how to be frugal. So, if a teacher wanted fancy certificates for her 5th grade class, then I took pride in creating a turn-key workflow for her using the AppleWorks database feature combined with the built-in fancy borders. It made them happy and I felt like a rock star.

I'm still rather picky about certain things that have been programmed into my brain from college professors. I still think some of them might be proud of me if they could see some of the things I've designed over the years. I've gotten very creative when it comes to tweaking user interfaces and pointing out to consumer level users the intricacies that they might never have noticed on their own. My eyes are trained to see details that others might not see and I utilize that in my teaching much of the time.

I wish more people used iWork and really learned how to make it a replacement for Microsoft Office. It always pains me when I watch my husband use Word. He gets so frustrated when it just won't do what he'd like it to do. Having a former typesetter for a wife hasn't helped him much as I've berated him over the years about using single spaces and properly formatting text the way I was trained as a designer.

I understand people's needs to just use what they know. I just wish for people to step outside of that zone a little bit and try something that might alleviate some pressure and strain. You know, um, Think Different. I would like for others to experience the satisfaction I get from my computing experience. Creating this invoice for my business use was technically work, but I enjoyed working on it and it felt rewarding to create something that would later save me time and do the work FOR me.

Instead of swearing at your computer because it's just not computing the way you told it to, give iWork a chance and use a product that doesn't actually make you feel like you're doing, well, work. You might be surprised or even delighted.

Please leave me a comment if you've found this useful. I enjoy getting feedback :) Happy tinkering!

Thanks for supporting my site!

icon