I set up my excel database for my products and have chosen unique stock/product numbers and based my prices on come of my competitors prices, placing them between the budget prices of The Works and the higher end stores like Cassart.
I formatted my table to give shading to the column headings, and made them bold, and added borders to clearly mark the columns from each other. The stock numbers and items sold are added to give me data to work with for charts and formulas.
I used a mathematical function to get the total sales calculated. I then used the autofill function to "pull" the formula down to the other cells, this is relative referencing in Excel and copies the above formula to the next cell.
I then used the AutoSum function to calculate the total for my sales at the bottom of my data table.
The show formulas function (below) shows that I have used mathematical functions in these three columns to get the total cost, total sales and then I have used those figures to give me the total profit for each product.
I selected my data table and then added a chart from the Insert tab. I chose a pie chart to show the percentages of products sold, however it didn't work with the profit as there are minus figures in the data and pie charts won't show those, so I had to choose a different chart for my profit.
Before I changed my chart, I wanted to show just the paints and total sales for them. I did this by using the filter button on the pie chart, selecting only the paint categories and the total sales, then clicked on apply. This then filters the chart results to those I wanted to see - the acrylic sets prove to provide the best sales.
