The spreadsheet may contain numbers, text, dates and formulas. Each cell, or a block cells may be formatted so that the contents of the cell is displayed in different ways. When a cell is selected, the contents of the cell are shown on the editing bar but the results of any calculation are displayed in the cell. The power of a spreadsheet comes from its ability to do calculations with numbers. The contents of one cell can be calculated from other cells in the sheet.
SITUATION: -
The Tasty Bakery LTD is a small independent bakery operating in Finchley. Currently all sales information and prices are calculated and processed manually. The management has decided that this is not the most efficient way of keeping track of price increases, unit sales, total sales and total revenue.
Currently the company’s sales information and prices are calculated and processed manually. The disadvantages of the present method adopted by the company are numerous. Firstly, the manual method takes up valuable space, as files would be stored in filling cabinets. This method would prove difficult in locating specific files and records. Secondly, amendments to records cannot easily be done as they are written on paper, and the paper can easily be damaged or lost. Consequently transportation from place to place of the files would be difficult and tedious.
I aim to solve the bakery’s problem by constructing and designing a spreadsheet that shall assist the bakery in keeping track of price increases, unit sales, total sales, and the total revenue. The spreadsheet will allow the bakery to store data, such as items being sold, and the selling and buying price of products. It will also store daily and weekly sales and profits for each of the products and calculate the total revenue of the bakery. The spreadsheet will make it simplistic to update, change variables, and calculate profit and losses of the bakery efficiently.
The results of the spreadsheet has made it simple for the Bakery to make decisions about which of their products are high in demand and yield the most profit for the bakery.
From the results of the spreadsheet it can be seen that the bakery are buying their products for more than they are selling them. This loss can easily be spotted on the spreadsheet in contrast to bakery’s previous manual method. Any decision to rectify this loss can be done on the spreadsheet.
The spreadsheet contains many patterns, with various parts of the spreadsheet being related. The price relates to the cost; there is a constant proportionality rate between the cost of the product and its selling price. Hence, if the cost of a product is high, so too is it’s selling price.
Another pattern would be the relationship between the revenue and sales. This means that the more sales that the company can make, the higher the revenue made.
The revenue also relates to the cost to determine the bakery’s profit. This profit can be obtained by subtracting the revenue from the cost.
The model operates when the cost of the product has been ascertained. This then affects the selling price of the product, depending on the bakery management. The total sale of the products has to be entered in under the various days of the week. The sales are then cumulated together to give the total weekly sales.
The spreadsheet calculates the Total cost by multiplying the cost of the product by its number of sales. This information is then cumulated together to ascertain the total weekly costs.
A similar operation is done to obtain the total revenue. The multiplication of the products selling price and its number of sales, gives the total revenue. This is then cumulated to give the total weekly revenue.
Finally, the spreadsheet calculates the profit by subtracting the revenue from the cost. This is the cumulated to ascertain the total weekly profit.
Each cell has formulas, to which any data entered must comply. Changing the sales section in the spreadsheet to suit present requirements will cause an immediate change in the total cost, total revenue and total profit to comply with these changes. These automatic changes are due to the cells complying with their formula.
I tried a number of ‘What if’ scenarios on my spreadsheet. These were to increase the cost by 20, 40 and 60 per cent respectively.
Before I carried out the ‘What if” scenarios I decided to make some predictions about what I thought the outcomes would be after the increases.
- Because of the increases i
20% INCREASE:
As can be seen from a 20% increase of the cost, in the total sales section, the spreadsheet has automatically altered the other sections accordingly. The new total weekly revenue is now £743.47 in contrast to the total weekly revenue prior to the increase, which was £619.56.
40% INCREASE;
From the 40% increase of the cost, in the sales section the new total weekly revenue is now £867.39, this is in contrast to the total weekly revenue prior to the increase, which was £619.56.
60% INCREASE:
As can be seen from a 60% increase of the cost, the total weekly revenue is now £991.30; this is in contrast to the total weekly revenue prior to the increase, which was £619.56.
