Organizing iTunes Financial Reports And Paying Contractors
Managing 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.
To 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”.
- It’s easiest to go alphabetically so start by opening up your Australia report eg. 12345678_0411_AU.txt
- Select all and copy the contents
- In Financial Reports.numbers, go to the 4/11 Australia Sheet
- 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:
- 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.
- 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.
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.
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.
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.