Cost Analysis Of Auntie Wuni's Costs

Your model should look like this:

Now with the cost drivers in place, we are ready to calculate Auntie Wuni’s monthly costs.

• Leave a row under the Spaghetti: Px/box row and create a subtitle ‘Total Cost of Ingredients’ in cell B28
• Hit Ctrl-B on cell B28 to render the title in bold

• Starting from cell B29 and moving downwards, populate the cells in this section simply with the name of the ingredients, i.e. Oil; Tomatoes; Rice; Pepper; Fish; Cow-leg; Beans; Spaghetti.
• Now move the cell highlighter to cell C29, which is the cell under the Jan-15 column and on the row corresponding to ‘Oil’ under the Total Cost of Ingredients section
• Start a formula by typing “‘=(”
• Use Ctrl-Up Arrow Key to move up to the quantity of oil cell
Sidebar: Note that you could also simply just have moved out of the current cell C29, after typing ‘=(‘ by hitting the Up Arrow Key once. In this instance you would have moved up one cell to C28, and to get to C9, you would have to hit the Up Arrow Key 20 times to get to C9.

With the Ctrl-Up Arrow Key combination though, 4 easy taps of the Up Arrow Key while holding down the Ctrl key takes you to cell C9

Remember, Ctrl- Arrow Key in any direction, moves you to the next block of text in that direction or to the end of a contiguous block of
For example, if I wanted to calculate the total cost of Tomatoes for the month of January, I would simply need to move to Cell C30, which is the cell below C29, and multiply C10 (which is the quantity of tomatoes and is the cell below C9, the quantity of oil) by C20, (which is the price of tomatoes and the cell below C19, the unit price of oil)

Likewise if I wanted to calculate the total cost of Oil for the month of February, it would simply be D9, (the cell one step to the right of C9 in the first formula we typed), multiplied by D19, the cell one step to the right of C19. See the symmetry in movements of both of these calculations starting from cell C29?

Now if only there was a way to replicate the formula in cell C19 to pick up the cells for Tomatoes and for Rice and for Pepper and so on for the month of January and all of the months without having to type them one by one

Thankfully, Excel has a rather convenient commuting feature, which means that as you copy a formula from one cell to another cell, the reference cells in the original formula move by exactly the number of columns and rows between the original cell and the destination