The Shop Tool
|
|
Players use third party tools at their own risk. For a full list of third-party tools, see this category list. For official rules and regulations regarding the use and acceptable functionality of third-party tools, see this page. |
Download Version 2014 Now!.
The Shop Tool is an Excel tool for managing and gauging the worth of shoppes and stalls. It is a third-party tool Previously maintained by Marrt. Belgarion is the original creator; neither he nor previous developers Megami or Haywoodx now works on maintaining this tool. The current version was updated by Fannon of Cerulean, and may or may not be updated in the future.
Instructions
Getting started
First, download the tool from http://www.schlitt.net/yohoho/Shoptool%202014.xlsm . Special thanks to Algol for hosting it!
In order to easily view some of the features, you must first enable macros in Excel. This can be done by going to Tools/Macros/Security. Choose the "Medium" security level to prompt you about whether or not to enable/disable macros every time you open a file with macros; if you already opened the file, you will then need to close it and re-open to enable the macros. CAUTION: Some malicious files contain macros that can hurt your computer. Only run macros you know to be safe. Setting your security level below medium is not recommended as macros will automatically be enabled in all files.
If you choose not to use the macros, you can still access all the features, except automatic rolling of the totals and the migration aid. See below in Financial Records, Average Labor Calculator, Financial Analysis Tab, and Migration Aid for more details. European users will need to change the sheets for the oceans they use to use the English decimal separator ("." in Tools..Options..International..Decimal Separator, and deselect Use system separator).
You should also enable automatic refresh each time you open the file to get the latest tax and Doubloon cost data and to check for updates to the Shop Tool. Otherwise, your price information will start to diverge from the shop's prices as the taxes and Doubloon requirements are adjusted by the game. To do a complete manual update, for Doubloon oceans, you must update both the Commodities table (select A1, right-click, Refresh data) and Doubloons table, if applicable, (select D1 & refresh) separately in each ocean you use, as well as the Title Sheet to check for updates (unhide sheet, select B12 & refresh).
Basic shop management
Each shop has a uniform look and feel for data entry. It was designed to match the interface from the game in order to make updating prices as easy as possible. The "buy" price corresponds with your dockside buy price or the cost to you for commodities. The charge price matches the "use" price in-game. The "stock" column is your current amount of commodities on hand.
Once you have entered the pricing data for commodities and labor, you will be able to see the price for finished goods to the right in the pricing area. For swords and clothing, there are drop-down boxes to allow you to choose the color you want to view. If you have set all your prices correctly, the advertised price should match the price listed in-game.
There is also a section for setting prices on finished goods for sale dockside. The cost for these sales are computed as your "buy" price plus the tax on the goods. On doubloon oceans, you can also choose to add in the delivery costs by putting the current price of doubloons at the exchange in the box right below the ocean type.
Rent
Each shop has a section to enter the rent and labor hours worked in a 24 hour period. If you choose to enter rent and labor used numbers, the tool will automatically factor the cost of rent into your costs for the product. The advertised price remains unaffected, but the cost will go up based on how many hours of labor you are using. In order to add this cost, the tool takes rent divided by the amount of labor you entered, resulting in a rent/labor hour cost. This cost is then added to your costs for items. The more labor you use by percentage will lower this cost. To disable this, enter 0 as your rent.
Estimated net worth
The tool will generate an estimated net-worth report on your shop, based on the current stock levels as entered in the pricing section. Commodities are valued at their tax value, not your current buy or charge prices. Finished products that are stored in inventory for dockside sale are valued at their dock-side sale price. The tool does not currently track tailor rack or other pre-made items' (mugs, swords, bludgeons, and furniture) value automatically. You will have to estimate this value yourself and add it to the appropriate cell on that shop's page. Doubloons are figured at the price entered at the top of the page. Investor Earnings are included in the Profits line, but are not available to the shop to spend. Think of the profit line as all profits to date since starting the stall.
Ocean selection
There is a drop-down box at the top of the pricing section for each shop. Tax values are automatically adjusted based on the ocean you choose in this drop-down box. Make sure you select the correct ocean! If you are playing on a subscriber ocean, you may enter "0" as the value for doubloon costs directly below the ocean selection box.
To support all of the current YPP oceans, with 2 downloads now for Doubloon oceans, the Automatic Refresh now will do quite a few downloads when you open the sheet. If this delay bothers you, you can delete the tabs for oceans you never use from your working copy of the tool. Be sure that all shop tabs have oceans that you keep selected. If you leave at least 2 oceans, then should you need an ocean you had deleted, you can copy that tab from the original downloaded tool between those two remaining ocean tabs.
Language selection
Language selection is independent of Ocean selection. This means that if you play Opal AND want German as your language, you must select both Opal and German. Most things in the tool, other than the title page, are ready for multi-language support, but some elements such as the tab names and button text simply can't be converted automatically. Formatting is done using the English language. Some cells may need to be resized for other languages. Instructions for changing the macro button text are included at the top of the Languages tab.
Doubloon Oceans
If you are playing on a doubloon ocean, you can automatically figure in the cost of doubloons when delivering items for racks or pre-made items. Enter the current price of doubloons at the exchange in the cell directly below the ocean selection box. All doubloon-related calculations will now use this price as the cost for doubloons. You can also enter the number of doubloons the shop owns to have them included in the net worth calculations.
Financial records
To access the financial records area of the sheet, click the button labeled “Financial Records” at the top of the page. If you have not enabled macros, this feature is contained in columns G-L. Highlight the first 2 columns (A and O for most shops), then right-click the column header and choose Unhide to see them.
This section is a simple implementation for deciding on whether or not you are making any PoE on your shop. There are 5 columns labeled Date, Investor, Amount, Re-Paid, Investor Earnings.
- Date: Enter the date poe was received by the shop from an investor.
- Investor: Enter the name of a pirate or investment group that is investing in your stall. Any PoE you put into the stall, including the opening costs, should be recorded here. If you find you are short on PoE in the shop and go pillaging to add more, make sure you add it to these records.
- Amount: Enter the amount plus the value of any commodities the shop has received from the owner or investor. If you are including Pre-mades in the Estimated Net Worth, you should include here the cost of pre-mades received from the investor (owner), less the value of any pre-mades sold.
- Re-Paid: Enter the amount you have re-paid the investor. This number should not exceed the original investment amount. If part of the repayments go to purchase commodities, deduct the value of the goods delivered. If you are doing weekly accounting, you may filter the Building Records for the week for "access" and "transfer" to add up these figures (download older entries until you go past the last week's entries - I pay the rent when I do this to know where the line is between weeks, and to keep one rent payment from the Coffers per week). You may also need to filter on "Added" or "Taken" if you want to reflect furniture,
- Investor Earnings: Enter the amount the shop is paying to investors as their share of the profits. You can include it on the same line as an investment or start a new line. Starting a new line allows you to set a new date so you see when the payment was made. This column should be used for any PoE you take from the shop coffers to give to a pirate, even your own. The investor earnings are counted in total profits of the shop in the net-worth calculations, but this PoE is not available to the shop to spend because it has been given to investors.
Above these 5 columns are 10 rows of self-calculating cells. Enter each investor from the bottom section into a cell at the top. The formulas will automatically calculate how much PoE has been received from an investor over time, how much that investor has earned, and their total return on investment. Earnings are calculated by adding all numbers in the Re-Paid column and the Investor Earnings column for that investor.
Average labor calculator
You can show the average labor calculator by clicking the button marked "Expand Labor Calc" in the labor used section. (If you have not enabled macros, this feature is contained in columns R-W. Highlight columns Q and X, then right-click the column header and choose Unhide to see them.) The average labor calculations are meant to give you a better picture of how much labor you are using by averaging numbers from the previous week. Change the dates to match the past 7 days and then enter the amount of labor used in the table. The averages are computed and shown in the cells contained in the Labor Used Calculator box.
Minimum stock levels
Below each of the recipe sections, you will find two rows labeled Upper Limit and Minimum Stock. Upper Limit shows the highest number of commodities used in a single product. For example, in the distiller, Sugar cane is used in Swill, Grog and Rum. Rum uses the most—15—so 15 appears in the Upper Limit row. Minimum Stock is a recommended stock level based on this upper limit. You can adjust the Minimum Stock calculations by changing the cell marked "Stock Multiplier" below the ocean selection box. A value of 100% will show stock levels equal to the Upper Limit values. If you want to always be able to make 2 of every item the shop can produce, set your Stock Multiplier to 200% and the Minimum Stock row will show you how much of each commod you need to do it.
Financial analysis tab
This tab shows totals of Net Worth, Debts, Profits, Investor Earnings, Shop Doubloons, Commodities, Finished Goods, and all of the items in the Estimated Net Worth section of each spreadsheet, as well as counts of the items for sale and Shop Doubloons. (The estate agent is no longer excluded from the calculations.) Follow the instructions that are on the sheet.
This tab is also set up to calculate the weekly profits for each shop and in total, by calculating the difference in Estimated Net Worth (shown as Profits at the bottom of the sheets). You should update your data at least weekly, since if you go much longer one of your best sources of data, the shop log, will be lost. Starting in version 8.0, you can press the "Copy This Week's Data" button to roll the data down to the Weekly Tallies section. If you don't want to enable macros, you can roll the data manually fairly easily if you unhide rows 10-11.
Adding copies of existing sheets for multiple stalls (i.e., an iron monger on Turtle and Alpha) should automatically update the totals with the new sheets; unprotect the sheet and unhide columns AE thru AU and rows 22 thru 40, and follow the instructions at the top of column AE). These duplicate sheets MUST be placed BETWEEN the apothecary and the furnisher. Copies added before the apothecary or after the furnisher will not be included in the calculations (this may, however, be a good way to handle one or two shops that are on a different ocean from the rest, if you don't want them in the totals due to the different POE values and don't want to run 2 spreadsheets). It is suggested that you then Re-hide the remaining unused rows and columns, and re-protect the sheet.
If you rename any shop tabs (including copied ones), you need to put the new tab names into the table in $ Analysis as well, so that the new column can be included in the profit calculation and the tallies, and so that the Island name and Date Opened in the $ Analysis sheet will show up automatically in the shop sheet.
The Shop Tool: Linked
A new feature that was added with Version 4.5 of the Shop Tool is a linked version of the tool. In v4.5, it was included in the same zip file as the main tool, but carried the "-- Linked"" identifier in the file name. In version 5.0, the Linked version was reworked and included as the default version of the tool. You can "unlink" your copy by changing the buy values for commodities on the individual shop sheets. To keep using the linked version, only update commodities on the "Commodities" sheet.
The linked version of the tool assumes that you run one of each type of stall on the island and that your cost for all finished goods are made at the lowest possible place in the supply chain. For example, if you are running an Iron Monger and an Apothecary, it is assumed that you make your enamels yourself at your apothecary. The "Cost" amount for enamels in the Monger is pulled directly from the per unit cost of enamel in the apothecary sheet as found in the dock-side sales section of the apothecary. It is also assumed that all profits/markups are made at the top of the supply chain. This means you profit from your enamels not in the apothecary, but when you sell a sword with enamel in it. Your apothecary provides enamel for cost (neither profiting nor losing PoE) on the sale to the monger.
Working with Open Office
Because of differences in how OOo and Excel handle data linked on different sheets you have to change all "!" (exclamation points) into "." (periods) on each sheet or many fields will be filled with "Err:502" and certain functions will not work correctly. Simply go to Edit > Find & Replace (or hit Ctrl+F) and enter "!" (without quotes) into the "Search for" field and "." (without quotes) in the "Replace with" field, click "Options" and change "Within:" to "Workbook", make sure that "Look in:" says "Formulas", and hit Replace All. After doing this the tool should work as expected. [1]
Migration aid
Beginning in version 8.1, a tab labeled "Migration Aid" follows the Title tab. If you enable Macros, this tab may be used to rapidly and easily migrate your data from one version of the shop tool to the next. Please follow the instructions on the sheet. After pressing the button, if are set up properly to migrate, you will get a prompt for debugging; reply Yes to proceed normally, No if you have a problem and want help from the developers, or Cancel if you are not yet ready to migrate.
In general, the migration aid will copy over the values from white, enterable cells. It will also pick up data entered in lieu of the formulas in the Labor Used Calculators, and (from version 8.0 on) manually entered Shop Names (at the top of the Shop sheet). If you want to go back to original functionality, you can zero out the numbers in the Labor Used Calculator or blank the shop name before migrating. Starting in version 8.1.1, Buy Prices that are formulas will also migrate (unless they are references to another sheet), both on the Commodities tab and on the Shop Sheets if you have unlinked. Buy prices are either linked or unlinked in groups on each Shop sheet, e.g. all herbs are either linked or unlinked. You can re-link by zeroing all entries in the group. For Basic Commodities, there are separate groups for the bought commodities and for each made commodity source (apothecary-made, etc.).
If a sheet brings over no data, check to see if it had been renamed. When migrating from version 7.1 or 8.0, if you have copied or renamed any shop tabs, you need to set that up in the new sheet before migrating, as described in the Notes at the bottom of the Migration Aid sheet. From 8.1 on, if the sheet and tab names appear correctly in the $ Analysis tab, then the renaming and copying will be done automagically.
The 8.1 button for now is in case you mess up the sheet. You can try bringing your data over to a fresh download of the sheet without having to type everyting in again. I expect to also be able to use it if I need to make a minor bug release that doesn't make major changes.
Historical notes
Version 5.1 thru 5.15 had an experimental version of the "Rack Pricer" table included with the tailor sheet. It was removed and postponed from versions between 5.15 and 6.0 due to technical errors. It was fixed and returned in Version 6.0. Beginning with version 6.0 Megami took over maintenance of the Shop Tool from HaywoodX. Marrt took over the tool with HaywoodX's blessing for Version 8.0.
Changelog
The changelog is available in this article.
External links
- Current Version - 2014
- Forum thread
