As store manager, you want to determine the result of running your business. We begin by creating a table in which we have a monthly column and online receipts, expenses (fixed and variable costs) and total. You can download the following document, to do the exercises
Text or Number ?
The most important thing in Excel is to “see” if a cell contains Text or Numbers. This is very important especially for the date and the calculation. It is indeed not possible to make calculations on the text (in this case Excel returns #VALUE! In the cell)
A figure is always located to the right of the cell (by default) and a text is always located to the left of the cell (see data in column A).
Arithmetic Calculation for January
In January, you think the amount of sales will be 1500. Enter this value in B2 and press enter . Then you estimate variable costs of January to 600. Enter this value in D2. You find that the figure is automatically positioned to the right of the cell.
These data will be used to make your first calculation formulas.
Fixed costs are equal to 9000 for the year. To spread that amount over the year, you will be dividing this value by the number of months in the year (12).
- Go to the cell B3
- Write the = sign (this is how Excel understand that it must make a calculation)
- Then fill the calculation 9000/12.
When you validate, you see the result 750 displayed in cell B3.
In the formula bar you see the calculation that has generated this result. You can copy this formula till December and for each month you will get the same value in each cell.
It is very important when you use Excel for the first time to check if a cell contains a formula or a figure. Often formulas are deleted because the user did not see that the value in the cell was the result of a formula.
You can view all formulas in your worksheet by pressing Ctrl + ‘. This combination switches to the formula view. To return to the normal mode, repeat the same combination.
To calculate total expenses,
- Go to the cell B5
- Write the sign = (to start your formula)
- Select the reference of fixed costs (cell B3)
- Press the sign +
- Select the reference of variable costs (cell B4)
The formula is presented simply as follows
Similarly, you can determine the outcome of January in cell B6, by subtracting the amount of sales (in cell B2) with the total charges (cell B5).
Your formula is :
Calculation of fixed costs
The formula in cell B3 can be copied till the cell M3 (December). Because there is no reference in the formula, the copy will duplicate 11 times the same formula.
Use one of methods seen in this page to copy the formula.
Calculation of sales
In February, the manager believes sales will be again 1000. In March sales will follow the trend of February.
For the month of February you write the value 1000 in C2 but in D2, you create a formula link to the cell C2.
Now, if sales of February is 1100, you can see that this value is automatically reflected for March.
In April, with the summer fruits, the manager expects a turnover equal to 1800 euros.
In May and June, sales increased by 50% compared to April. Here, the reference of the cell E2 must be used 2 times in formulas of F2 and G2.
For May, we increase sales of April by 50%. The formula is:
For the formula in G2 (June), do not copy the previous formula in cell F2. If you did this, the reference of E2 will be next G2 (offset of 1 cell). Or, in this example, for June, the entry point is the value of April (cell E2). So we have two possible for a similar result:
In July, sales must be 3300.
The forecast for August is up 50% compared to July. You must write your formula like this 2 examples :
In September, sales will drop :(. Reduce your sales of 30% compared to July. Your formula is then:
In October, the income decrease of 20% in reference of September. You can write:
In November, sales are estimated to 1200.
In December, an increase of 15% is expected compared to November, the formula is:
As you can see, it is very easy to make calculations, especially when you re-used the result of previous cells.
Calculation of variable costs
Now, let’s focus on the variable cost.
In February, variable costs of February are 550. Variable costs of March are equivalent to February. So your formula in cell D4 is:
In April, a seasonal worker is hired. The salary is estimated to 1000. Variable costs of the previous month are carried forward to more. In the same formula in E4, we have a fixed parameter (1000) and a variable (D4). Let the formula
In May charges associated with heating are removed (200), but stocks have increased. The increase in stock is 20% of variable costs from the previous month. The formula is simply written as follows
In June, July and August, stocks have increased steadily by 25% from one month to another. For June will be equal to the formula
For months of July and August, you can just copy the formula in June (copy / paste). You see that with copy / paste references change respecting the shift column and online. For July, we copy the formula G4 H4 – so there is a lag of one cell in a column – and of the copy / paste will also meet this gap by pointing when the cell G4. The formula after copy / paste will
And for August
Variable costs for September are equal of those of May
In October, the seasonal worker is gone and heating was rescheduled to October 15 (200 / 2, half a month). The decline in stocks is 10% of total expenses of the previous month.
In November, charges – excluding the heater – are equal to the previous month. It is expected to heat the entire month. So we will add a half months of heating
In December, the drop in the stock for a total of 10% over the previous month.
Calculation of total
To calculate the total of expenses and incomes for each months, we will simply copy / paste the cell C5 till the cell M5 and the same for the cell C6 till M6.
The result in your spreadsheet is the following spreadsheet.