Goal:
- Determine the pro-rated overhead expenses for a large comuter assembly warehouse.
- Using a spreadsheet, demonstrate proper usage of ' relative referencing ' vs ' absolute referencing '.
Lecture:
You are the manager of a large warehouse that assembles computer components.
Our building contains 27,000 sq ft of floor space. Each division in our company shares the 'overhead' expenses, such as electricity, water, insurance, etc.
Your task is to determine how much each division is responsible for.
- The concept you will need to use is called " absolute referencing" .
- If you look at the chart below, notice the Total Overhead Expenses are $13,500.
- Each division is responsible for a little part of the $13,500.
- In particular, they are responsible for the fractional part that represent their 'floor space'.
- For example, the business office is 15' x 20', or 300 sq ft.
- Therefore they are responsible for 300/13500th of the total bill. How much is this?
- Your challenge is how to copy the formula down so you don't have to repeat the calculation over and over.
- Normal copying will not work. You will see why later.
- The correct copying process is described below.
Procedure:
You may wish to look at the 'finished product' below (prior to viewing this procedure).
- Be sure to save your spreadsheet as: "projf-overhead.xls"
- First determine the 'Square Feet', and copy the formula 'down'. This is called relative referencing.
- Calculate the total square feet.
- Next, calculate the "% of total floor space" .
- Hint: This is a fraction. The business office is 300/D20. Would you agree?
- Notice how we used D20 rather than 27000.
- Copy the formula down. OOPS , it didn't work.
- That is because you used 'releative referencing' and the answer was correct only in the first cell.
- In order to calculate (and copy down) your "% of total floor space" , you will need to utilize a new concept called absolute referencing .
- Try using the <H>elp feature built into Excel and see if you can do by yourself.
- You will wish to repeat the process for the last column, pro-rated expenses .
- Please include a relevant 'pie' chart in a separate sheet ..
Grading Requirements:
Select this link to see the grading requirements for this semester.
