about these examples...
On this page we offer some free examples of how to how to design efficient Microsoft Excel workbooks . These are offered free of charge Please note that the examples are all zipped so you will possibly need an unzip program such as Winzip
Note: Click on the images to enlarge
FIFO Inventory Valuation
Excel has several worksheet functions that are useful for asset valuation, however there is no function to calculate FIFO (First In First Out).
This workbook can be used as a Template for such calculations. It contains a User Defined Function (UDF) that is used like this;
=FIFO(product_code,units_sold)
I have create a template because the UDF only works if the data is set out in this layout and relies on the Dynamic Named Ranges
|
ProductCode |
=Stock!$B$6:OFFSET(Stock!$B$6,COUNTA(Stock!$B$6:$B$65536)-1,0) |
|
PurchaseUnits |
=Stock!$D$6:OFFSET(Stock!$D$6,COUNTA(Stock!$D$6:$D$65536)-1,0) |
|
StartCount |
=Stock!$C$6:OFFSET(Stock!$C$6,COUNTA(Stock!$C$6:$C$65536)-1,0) |
|
UnitCost |
=Stock!$E$6:OFFSET(Stock!$E$6,COUNTA(Stock!$E$6:$E$65536)-1,0) |
|
UnitsSold |
=Stock!$F$6:OFFSET(Stock!$F$6,COUNTA(Stock!$F$6:$F$65536)-1,0) |
I have demonstrated how to use the UDF in two ways –
The sheet STOCK is used to record stock movement using the table to enter stock movements. Current valuations can be seen by selecting a product in C2. The Sales are then determined using SUMIF and the UDF calculates the FIFO valuation of the selection.
The sheet Lists is used to List all the products and is the source for the Data Validation list. and the stock valuation is also shown by using the FIFO Function.
Click here to download the example .
You can now buy the unprotected version to add into your own project for £9.99. Click you will be able to download the file immediately after payment has been confirmed
Simple Inventory/Stock Control Workbook
This example demonstrates how to create a simple inventory and stock control
workbook
A catalogue is maintained in the marked sheet, in which Re-Order Levels (ROL)
and Re-Order Quantities (ROQ) and product details are recorded.
The Inventory sheet is maintained by entering products received and used.
Products can be selected from the drop down list (created by Data Validation)
and information on that product auto fills by using VLOOKUP formulas. Enter
either quantity used or received and the stock level is updated using SUMIF
formulas. The formulas also check if stock needs re-ordering. Note: only
complete the Inventory Input line (Row2), then click the Button to update the
entry into the Inventory database below.
Add new items to the Catalogue as required.
The workbook uses Dynamic Named Ranges so all tables & columns referred to in
the various formulas automatically expand as data is added. This makes the
formulas easier to enter and more efficient.
Note: all formulas are highlighted in light blue and should not be overwritten,
avoid this by protecting the worksheet.
Click here to download the example .
Create a summary sheet...
This example demonstrates how to create a summary sheet of data from a number of sheets. Relevant data should be in the same position on each sheet.
Many MS Excel
users maintain sheets of data for various categories, maybe a chain of shops has
a sheet for each shop. It sheet should be laid out exactly the same. The data
sheets should be enclosed by a two sheets, in the example called Top and Bottom.
The Summary sheet mirrors this layout, but data is summarised by formulas like:
=SUM(Top:Bottom!B2)
The total created by this formula will include all amounts in B2 on all the
sheets from the one named Top to the one called Bottom.
By moving a sheet out side of the Top & Bottom tabs, that sheet's data is no
longer included in the Summary sheet.
To move a sheet, simply click on the relevant Worksheet tab and drag it from
between the Top & Bottom tabs.
Following a request I have added a macro that adds a sheet in the specified position between Top & Bottom. The sheet is based on a hidden Template sheet.
The code also demonstrates one way to get user input with an Excel VBA MessageBox, and also how to add a template sheet that is based on a sheet hidden within the Excel Workbook.
Note: all our examples are offered as is with no warranty. J & R Excel Solutions accept no responsibility for any loss arising from your use of them