The Shop Tool

The Shop Tool is an Excel and Open Office tool for managing and gauging the worth of shoppes and stalls. It is a third-party tool maintained by Belgarion.

Instructions

Getting Started

First, download the tool from here. 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. 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. See below in Financial Records and Average Labor Calculator for more details. If you already opened the file, you will need to close it and re-open to enable the macros.

You should also enable automatic refresh each time you open the file to get the latest tax data.

Basic Shop Management

Each shop has a uniform look and feel for the 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 a 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 are valued at their dock-side sale price. The tool does not currently track tailor rack value. You will have to estimate this value yourself and add it to the appropriate cell. Doubloons are figured at the price entered at the top of the page. Transferred assets 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 Suggestion

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, it is important to enter "0" as the value for doubloon costs directly below the ocean selection box.

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 columns A and M, 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, Asset Transfer.

• 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 about amount that you received for the shop from the investor.
• Re-Paid: Enter the amount you have re-paid the investor. This number should not exceed the original investment amount.
• Transferred Assets: 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 transferred assets 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 return rate. Earnings are calculated by adding all numbers in the Re-Paid column and the Transferred Assets 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

For now, this tab shows a total of all Net Worth, Debts, Profits, Transferred Assets and Shop Doubloons. To get an accurate accounting, you'll need to delete the initial stock and financial record entries so they don't show up in your totals. Adding copies of existing sheets for multiple stalls (ie, an iron monger on Turtle and Alpha) should automatically update the totals with the new sheets. 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. The estate agent is also excluded from the calculations.

ShopTool: 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, the 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.

Changelog

The changelog is available in a separate article.

External Links

• Current version (5.0) (Released: February 6, 2007)
• Forum thread