GETTING STARTED
1. Introduction
- a. Within the Workbook all Cells that perform calculations have been protected in order to prevent accidental deletion of formulae that would result in the SOFA & Balance Sheets not working. However the Cells are not password protected and can therefore be amended. The workbook has been designed as "a one size fits all" though and it is not envisaged that funds will need amendments to the template provided.
2. Trading Account - GPF Tab
-
a. Within the GPF/Unrestricted funds ("GPF" Tab) at cells A21, A22, D35 & D36 you will see "Trading Loss" & "Trading Profit". These need to be amended to e.g. Shop/Bar/Vending Machine etc.
-
b. Go to the "Tools" tab "Protection" and unprotect the sheet.
-
c. Go to Cells A21 & A22 and populate with the name of the trading account Loss, repeat at Cells D35 & D36 trading account Profit.
-
d. Return to the "Tools" tab "protection" and now protect the sheet again.
-
e. This is important as although it is extremely rare to report a loss within a trading account cells B21, B22, C21 & C22 need to be left blank as the "Charitable Activities" within the SOFA tab is calculated from rows 23-48 and doesn't include any reported loss.
3. Unrestricted Trading Account analysis - Previous Year Figures
-
a. Although as much as possible within the new AF N1514 has been automated it is not possible to automate the previous years figures (see Cells C131, C134 & F132 "GPF" Tab) without having the whole of the previous years "Balance Sheet" & "Percentage Profit Calculator" included in the Workbook and therefore there are no formulae in these cells.
-
b. The Work around for the first years use is as follows:
-
c. Using the last close down "Balance Sheet" populate the Previous and Current stocks on hand (Cells B15 & G15 "Balance Sheet" Tab) as recorded last year for your first trading account. Now populate the "% Profit 1" tab with last years figures.
-
d. Within the "GPF" tab the Cells B131, B134 & E132 will now be populated with the Unrestricted Trading Account figures for the previous Balance Sheet. Now copy these figures into the adjacent cells C131, C134 & F132 (Previous Period).
-
e. Repeat the above process for the second trading account using the Cells B16 & G16 on the "Balance Sheet" and the "% Profit 2" tab calculator in order to populate Cells B147, B150 & E148 "GPF" Tab. Then as before type the results into the adjacent cells (Previous Period).
-
f. Repeat the above process for the third trading account using the Cells B17 & G17 on the "Balance Sheet" and the "% Profit 3" tab calculator in order to populate Cells B163, B166 & E164 "GPF" Tab. Then as before type the results into the adjacent cells (Previous Period).
-
g. Repeat the above process for the forth trading account using the Cells B18 & G18 on the "Balance Sheet" and the "% Profit 4" tab calculator in order to populate Cells B179, B182 & E180 "GPF" Tab. Then as before type the results into the adjacent cells (Previous Period).
-
h. Repeat the above process for the forth trading account using the Cells B19 & G19 on the "Balance Sheet" and the "% Profit 5" tab calculator in order to populate Cells B195, B198 & E196 "GPF" Tab. Then as before type the results into the adjacent cells (Previous Period).
-
i. Now that the work around has been achieved delete the figures entered on the "Balance Sheet" & both "Percentage Profit Calculator" tabs..
-
j. For subsequent years accounts it will just be a case of typing in last years Current Period figures into the Previous Period cells.
-
k. Ensure that the "Title of Trading Account" in the % Profit5" Tab (Cell G6, G7, G8, G9 & G10) are populated as this then populates the name of the trading accounts above the analysis boxes.
GPF Tab
- a. The GPF tab has been expanded to cover 2 pages in order that accounts with numerous entries under the various headings will have sufficient rows to analyse their GPF in detail.
5.
Setting the End of Period Date
- a. Go to Cell C1 in the "SOFA Tab" and set the date. This will then populate the "Balance Sheet", "GPF", "Restricted", "Designated" and "Endowment" Tabs end of period dates.
6
SOFA Tab
-
a. With the exception of Cell F44 all cells are populated from the "Balance Sheet", "GPF", "Restricted" & "Endowment" tabs with the information already entered in the compilation of your accounts. The figure to be entered at Cell F44 is taken from last years accounts, being the previous periods "Total Funds".
-
b. Although there is now no requirement to submit "Restricted Funds Analysis" sheets to SPS Branch there is an analysis sheet at the "Restricted" Tab which is required to be populated in order that the SOFA captures the information with regard to all "Restricted" Funds.
-
c. There will be an extremely small percentage of funds that have "Endowment" or "Designated" Funds within their accounts. However, if this is the case then the "Endowment" or "Designated" Tabs need to be populated in order that the "SOFA" Tab captures the information with regard to all "Endowment" and "Designated" Funds.
7 Balance Sheet Tab
-
a. Debtors & Creditors totals (see Cells G14 & G23) are populated from the Debtors/Creditors lists (see paras 6 & 7) within the "Notes 5-12" Tab.
-
b. Investments at Market Value (Cells B6 & G6) within the "Balance Sheet" Tab are populated from para 5 within the "Notes 5-12" Tab (Cells G4 & G9). Within para 5 the "Revaluation Gains/Losses" (Cell G7) is populated from the Unrealised Gain on Investments & Unrealised Loss on Investments entries within the "GPF" Tab.
-
c. The reason for para 7a & 7b above is to ensure that accounts are not submitted with incorrect notes. This way if the notes are incorrect then the Balance Sheet & SOFA will not work and should be noticed prior to submission.
DOES THE CLOSE DOWN WORK?
8
a. Balance Sheet:
| Total Assets Minus Liabilities (Previous Period) | = | 40,754.40 |
|---|---|---|
| Total Funds (Previous Period) | = | 40,754.40 |
| SOFA | ||
| Cell E44 | = | 40,754.40 |
| Cell F46 | = | 40,754.40 |
| The above 4 amounts must all be the same. | ||
| Balance Sheet: | ||
| Total Assets Minus Liabilities (Current Period) | = | 35,822.59 |
| Total Funds (Current Period) | = | 35,822.59 |
| SOFA | ||
| Cell E46 | = | 35,822.59 |
| The above 3 amounts must all be the same. |
b. Balance Sheet:
If both (a) & (b) above are true then the Balance Sheet works.
9
If the Balance Sheet Doesn't Work - TIPS
----- Start of picture text -----
SIMPLES
----- End of picture text -----
a. Restricted TAB
The difference between the previous period's Total Restricted Funds and the current year's Total Restricted Funds on the "Balance Sheet" Tab should also be reflected as the difference between Income and Expenditure on the "Restricted" Tab:
i.e.
| Current Total Restricted Funds - Previous Total Restricted Funds = | -2,612.23 |
|---|---|
| Total Restricted Funds Income - Total Restricted Funds Expenditure = | -6,267.88 |
| The above 2 amounts must be the same | |
| (if not then you have missing entries in the Restricted Analysis). |
b. SOFA Tab
Check to ensure you have made an entry in Cell E44 and that it is the correct figure.
c. GPF Tab
Cross reference GPF Analysis to the GPF Tab and AB 397 to ensure all entries are included in the close down.
d. Balance Sheet Tab
Cross reference the Balance sheet to the AB 397 to ensure they agree