Excel offers formatting and layout options that allow the creation of an illustrated price list across multiple pages, with prices that automatically update as the price lists of the various suppliers change.
Excel offers formatting and layout options that allow the creation of an illustrated price list over multiple pages, with prices that automatically update as supplier price lists change.
If we have a company, big or small, we still have to manage multiple suppliers for the products we resell, and one point on which every entrepreneur unanimously agrees is that managing supplier price lists and subsequently calculating the markup for the final customer can be a real Babel.
Excel price list
Also on this occasion, to our great relief, Microsoft Excel can be of great help to us, even if we are beginners in its use. Let’s open Excel and from the File menu select New… On the right-hand side panel, click on Blank Workbook and immediately save the file with the name listinoprezzi.xls.
The first thing to do is to prepare the spreadsheets by renaming the tabs at the bottom left (as shown in the figure). Double-click on the tab named Sheet 1 and rename it to Customer Price List. Similarly, rename the tab named Sheet 2 to Supplier 1 and the tab named Sheet 3 to Supplier 2.
Of course, if we wish to immediately use this spreadsheet for our price lists, nothing prevents us from writing the names of our actual suppliers directly on the tabs. If we need to add other sheets to the workbook, just go to the Insert menu > Worksheet and Excel will insert a new worksheet before the active one.
Let’s go to the sheet labeled Supplier 1. In cell A1, write this phrase again (or the supplier’s own name) to give the sheet a title and press Enter. Now let’s start creating the table where we will insert the product data. Move two rows down, to cell A3, and write CODE.
Move to cell B3 and write PRODUCT DESCRIPTION. Here we may probably need a wider space to describe the product. To widen the column, place the cursor between the column headers where the letters are, and when it changes its shape, click and drag until you have the desired width.
In this example, we are trying to create a hypothetical price list that can fit the largest possible number of companies. So let’s add to our table the entries MANUFACTURER, MODEL, SIZE, VARIANTS and, finally, PRICE, which for the moment we will consider excluding VAT. However, a special note must be made about the entries SIZE and VARIANTS.
It often happens that products with the same item code, description, model, and price differ only in size or variant (for example color), and this occurrence can happen for twenty or more rows! In this case, just write it the first time, select it, click on the little square in the selection box, and drag to copy it into all the needed rows.
Now that we have created our nice table, the first thing to do is to protect ourselves from risks and ensure that Excel treats the column with the item codes as text. Otherwise, it might arbitrarily interpret alphanumeric codes and/or those with leading zeros, display them differently from how we wrote them, and we might cause some mess.
Click on the column header to select the entire column and from the Format menu select Cells (alternatively right-click on the entire selection and choose Format Cells from the context menu). In the Number tab, under Category, activate the Text option and click OK.
Similarly, select column G, and from the Format menu activate Cells. In the Number tab, under Category, activate Accounting; if it’s not already selected, choose the Euro currency, set 2 as decimal places, and press OK. By doing this, we have prepared the column to receive and align monetary data.
At this point, our product list is done. To copy the created layout to the second sheet, click the intersection square of the row and column headers as shown in the figure to select everything, press CTRL+C to copy to the clipboard, go to the Supplier 2 sheet, select everything again, and press CTRL+V.
Another method to create copies of our product tables is to position the cursor on the sheet tab, right-click and from the context menu select Move or Copy. In the dialog window that appears, check the Create a Copy box as shown in the figure and click OK.
But what to do when our price lists have been operational for a long time, our sheets with purchased products are fully completed, and a new supplier is added? No worries, Excel can handle this too. Go to Insert menu > Worksheet and name it Added Supplier.
Move to one of the already completed sheets, click the intersecting square of row and column headers to select everything and go to Edit menu > Copy. Return to the Added Supplier sheet, also select everything and go to Edit menu > Paste Special. In the dialog box, select Formats to copy only the formatting.
Recommended web pages xnxxyouporn.com, romeoporno.com, xnxxit.com, broporno.org, zzzporno.org, xxx1.link.
Now that we have finished creating the sheets with the price lists sent to us by our suppliers, let’s move on to creating the “retail” price list, that is, the final one for our customer, including both the markup that we will add to the price for our profit and the expense item showing the VAT’s impact on the total paid. Go to the Customer Price List sheet.
First, we create our page header by importing our logo via Insert menu > Picture > From File and typing our data next to it. Then press the Print Preview button in the Toolbar. With this operation, Excel will subsequently show us the dashed page breaks, so we always clearly see the space constraints for pagination.
Now let’s create boxes with photos for the promotional products on the first page and layout the other data in a table. Make a horizontal selection of cells and from Format menu > Cells, apply a border. Import the product photo via Insert menu > Picture > From file. Write the product name and various descriptions, exactly as in the picture.
For the customer cost, type = and click the cell where the supply price is written: for a 30% markup type *130/100 and press Enter, same procedure for the price including VAT. In the table, type = in the first cell, click and then Enter in the supplier sheet cell. Same calculations for the price, but only in the first row: the rest, data and calculations, will be filled by dragging.
Be the first to comment