Use catalog_product.list to retrieve all products
Then foreach loop through them
Call catalog_product.info on each, this will retrieve an array with a product type, you want just the ‘simple’ and not the ‘configurable’. The array will also have ‘cost’ in there and the quantity in stock. Multiply the two and add to total.
Print out this information to a csv file with quotes around your fields and column headers for sku, product, cost and rrp. You can then just open this in Excel.
Since the above method takes ages you may want to create the report with a cron job, so it is made overnight.