Excel: Create a calculation sheet
In this practical tip, we will show you how to create a calculation scheme in Excel.
How to create a spreadsheet in Excel
Note: This practical tip is based on Excel 2013 under Microsoft Windows 7.
- Start Excel and write "List purchase price" in the first field.
- Switch two columns to the right and write "Net purchase price" in this field.
- Under the field of "list purchase price" write the "supplier discount" and next to it the amount of the discount in percent. If no discount is granted, simply write a zero.
- Then switch to the right field next to the percentage of the supplier discount. Click on "Auto Sum" above and first write a minus. Then click on the field of the list purchase price (for example B3).
- Add "/ 100 *" to this equation and select the field of the percentage of the list discount (for example, C5). Confirm with the Enter key.
- Write "target purchase price" in the third line and then form the sum of the results of the first and second line two columns to the right. To do this, click again on "Auto Sum" and then on one of the two results. Drag the field to the second number and confirm with Enter.
- For the "cash purchase price", the percentage of the "supplier account" is deducted from the target purchase price.
- Then add the "subscription costs" to the cash purchase price and determine the "subscription price".
- Add the "handling costs" to the "subscription price". This sum then results in the "cost price".
- To form the "net selling price", add a percentage "reseller discount" to the "target selling price".
- As a last step, you add the current value added tax of 19 percent to the "net sales price", so that the final "store sales price" results.