Organizing iTunes Financial Reports And Paying Contractors

financial reports iconManaging income from app sales and paying out expenses is arguably one of the more boring aspects of being an indie developer, but it’s something that has to be done. I’ve spent no small amount of time trying to figure out a system of organizing my iTunes financial reports and figuring out exactly what each app earned in each region.

As you probably know, financial reports come in from 7 different regions and each app needs to earn at least $150 US before you’ll receive a payment from that region. If you have several different apps, each with different labor costs then figuring out exactly what came in and how much should go out can get pretty tricky.

Numbers SidebarTo compile the reports, I tried pretty much all of the major spreadsheet apps like Excel, OpenOffice and Google Docs, but none of those offer the kind of organization I was looking for.

Then I tried Numbers ’09 which is part of Apple’s iWorks office suite – it has 2 main features that really helped speed up my workflow: The sidebar which you can fill with any number of Sheets and then add any number of Tables to each Sheet. And each Table on a Sheet is completely independent of the others so you don’t have to fight with cell widths and heights that go the entire length of the Sheet.

If you’re using Numbers, you can download my spreadsheet templates here. Even if you’re not using Numbers, you can still apply the workflow to your favorite spreadsheet app.

Download Your Financial Reports

Once the reports are in for the month, I download them them into a directory structure which has a folder for each year and then folders for each month inside them. Downloading the reports can probably be easily automated but so far I haven’t bothered to write a script since there are only 7 files to download per month and it gives me an excuse to poke around in iTunes Connect once in a while.

Importing The Reports Into A Spreadsheet

I use a single spreadsheet, Financial Reports.numbers (found in the FinancialReports.zip), that can hold all of the Financial Reports for the entire year. The template comes with several blank Sheets and Tables that you can use including 7 sheets for the month of May (1 for each Region) to get you started and 3 blank Sheets per Region that you can use for your apps (duplicate or delete sheets depending on how many apps you need to track). There’s also a blank template Sheet called “0/00 Region” – at the end of each month just duplicate that blank template 7 times and rename each duplicate Sheet to a month/year and Region like “6/11 Canada”.

  1. It’s easiest to go alphabetically so start by opening up your Australia report eg. 12345678_0411_AU.txt
  2. Select all and copy the contents
  3. In Financial Reports.numbers, go to the 4/11 Australia Sheet
  4. Paste the contents of the .txt into cell A1 of the “All” sheet.

If you have more than 1 app that you need to separate from your others, then do the following:

  1. Click the little down arrow next to the “E” column heading (Vendor Identifier) and then select “Sort Ascending” so that your apps will sort together alphabetically.
  2. Starting at Row 2, select the entire row(s) for each app, copy and paste them into each of the other Tables (which you should also name for your app).

Click here to see an example of what your sheet should look like after following the above steps.

Calculating Exchange Rates

The financial reports don’t factor in the exchange rate (or FX Rate) so that needs to be calculated. In iTunes Connect under Payments and Financial Reports, there’s a tab called “Payments” which has a summary of your payments from each Region and it also has the FX Rate (exchange rate) used to calculate those payments.

Back in Financial Reports.numbers, there’s another Table named “4/11 Payments” and a blank templates named “0/00 Payments” which you can duplicate each month, rename to the current month and then copy paste your Payment summary into it. There’s also a Table for iAd revenue in case you’re receiving any.

Copy and paste the FX Rate for each Region into the “All” Table for each corresponding Region, this is a handy place to keep it so you can reference it during calculations in the next step.

FX Rate

Now for each of your apps, sum the Extended Partner Share column and then multiply the total times the FX Rate. This will give you the actual amount paid out for that app in that Region in US dollars.

FX Rate

Keeping A Running Total Of Earnings

To keep a running total of earnings for each app in each region, there’s a 2nd spreadsheet called Disbursements.numbers (also in the FinancialReports.zip file). When an app exceeds $150 in a region, the cell is added to a total of all other regions that also exceeded the minimum. If earnings are less than $150, then carry over the amount and add it to next month’s earnings. From the total, you can figure out how much money to pay out to contractors.

Here’s an explanation of of how the sheet works. Click the image to see a full view of the spreadsheet.

disbursements small

Conclusion

This probably wasn’t the most exciting iDevBlogADay post you’ve ever read (did I really just write a blog post about spreadsheets?). Anyway, I hope you found this information helpful. I’m always looking for ways to improve my workflow so if you have any suggestions or know of anyone who’s written a similar post to this, please let us know in the comments.

This post is part of iDevBlogADay, a group of indie iPhone development blogs featuring two posts per day. You can keep up with iDevBlogADay through the web site, RSS feed, or Twitter.

Share/Bookmark
  • Ciechan

    “each app needs to earn at least $150 US before you’ll receive a payment from that region”
    I think this was changed to at least $150 from *all* the regions.

  • http://twitter.com/plicatibu Plicatibu

    Hi, Tim.

    Thanks for sharing your spreadsheet templates. I’m pretty sure it will be useful for a lot of indie developers.

  • Pweil

    Many thanks – I started a search in earnest this morning for tools to deal with the financial reports so your post is timely.  If others have comments: once I have the data, I need to do more than sort, but need to tag titles with publishers – and publishers with royalty rates.  I can do it (painfully and error prone) by hand each month, but am looking for better way.  File Maker Pro?  Some C++ programming? I don’t see that Apple allows us to tag a title with a “label” or “publisher” although it outputs those fields.  Any solutions out there?  Again, thanks!

  • http://www.rocket5studios.com/ Tim Miller

    Hi Pweil,  I’m glad you find the article useful.  Unfortunately I don’t see any way to add info to the “Label/Studio/Network/Developer/Publisher” column in the reports (other than manually entering them yourself).  Maybe someone else here has a suggestion for tagging? 

  • http://www.rocket5studios.com/ Tim Miller

    Thanks, Marcio.  Glad you like it!