Filetype Excel XLSX | Posted on 15 Aug 2022 | 3 years ago
Authentication
digital resources
188x Filetype XLSX File size 0.56 MB Source: www.undp.org
Sheet 1: Indications
| Annex 5: Cost analysis and balance point tool | ||||||
| Instruction sheet | ||||||
| Introduction. This tool provides a brief analysis of business money management. For example, grocery stores can handle more than 80 different products, however, there is always a small group of products that correspond to the highest sales. As such, 20% of products often generate 80% of sales. This tool is designed to keep track of the 15 products that are most frequently sold on a monthly basis. | ||||||
| Description. Enclosed are six spreadsheets, which will help develop business money management practices. 1. Indications.- On the first sheet, there are general instructions for using the tool. 2. Variable cost.- Enter information about products bought and sold here. This is called variable cost because it changes with the amount of products sold. 3. Fixed monthly costs.- On this sheet, enter information regarding total spend regardless of items sold, such as the payment of wages or basic services. 4. Balance point in money.- This contains information on how much must be sold to cover fixed costs and variable costs, that is, neither win nor lose. The only section that must be completed is on how many days per week the business is open. 5. Balance point in products.- This provides information on how many units of each product must be sold to cover the variable costs of each product and its portion of the fixed cost, or, to reach the point of neither gaining nor losing. It is calculated automatically and no information will need to be entered. 6. Sales budget.- On the last sheet, there is the option to set sales goals for each of the main products based on their balance point. The further the goal is from the equilibrium point, the more there is to gain. |
||||||
| Instructions.- Each one of the fields to be completed has an explanation of the information that needs to be entered, it is displayed when the mouse is placed over boxes that have red marks in the upper right corner. | ||||||
| Only fill in the fields in light blue with a thick border. | ||||||
| The other cells contain formulas and as such, these cells have been locked since they should not be modified. | ||||||
| Products | Unit of measurement | Units sold per month | Unit purchase price | Unit sale price | Total Purchase | Total sale | % Cost of Sale | % Contribution margin | % of Product Share |
| Rice | Pound | 1000 | $0.35 | $0.50 | $350.00 | $500.00 | 70% | 30% | 32% |
| Sugar | Pound | 1000 | $0.25 | $0.32 | $250.00 | $320.00 | 78% | 22% | 21% |
| Eggs | Units | 1500 | $0.09 | $0.15 | $135.00 | $225.00 | 60% | 40% | 15% |
| Olive oil 1 litre | Bottles | 200 | $0.70 | $1.00 | $140.00 | $200.00 | 70% | 30% | 13% |
| Olive oil 1/2 litre | Bottles | 400 | $0.50 | $0.75 | $200.00 | $300.00 | 67% | 33% | 19% |
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| $- | $- | #DIV/0! | #DIV/0! | 0% | |||||
| Totals | $1,075.00 | $1,545.00 | 70% | 30% | 100% | ||||
| Contribution Margin = (Sales - purchase price) = | $470.00 | ||||||||
| Contribution Margin % = (Contribution margin / sales price) * 100 = | 30% | ||||||||
| Variable Cost% = (Variable Cost / Sales Price) * 100 = | 70% | ||||||||
no reviews yet
Please Login to review.
