Part 1: Prepare Year 1 Revenues
Go to the Revenues tab and compute the total revenues for each customer and the total Cost of Goods Sold (COGS) for each client.
Notice that there are two products. There is a quantity tally for each customer for each of the two products: Blue and Red. You will compute the revenues in column F and the COGS in column G.
Go to cell F2 and enter
=(D2*Parameters!B6)+(E2*Parameters!B7)
That is quantity x price + quantity x price.
The parameters appear in the Parameters Tab. Click and look at cells B6 and B7. The first one is the blue and the second is the red.
Now, in case you skipped the background box above. You need to go back now and read it. It is "TECHNICAL NOTES ON CELL REFERENCING AND ANCHORING" If you're seeing all this for the first time, it may seem offbeat, but it's one of the most important concepts in this exercise. So now add the $ as below.
=(D2*Parameters!$B$6)+(E2*Parameters!$B$7)
Now see if you can do the COGS in cell G2It is like above, but each of the factors is different.
Next, Add the anchoring with $ by typing it in, or using the shortcut: F4 key or ?+T (see box).
OK, all of the first row is done; now copy it down (see box)
Now you're done setting up the Revenues tab.
A beautiful shortcut key for anchoring + T on Macs F4 on Win platforms Since it is cumbersome to put in those dollar signs between columns and row numbers, Excel has a special key to enter $ signs for you: F4. Simply select the cell(s) you want to anchor, then press F4. On some laptops you may need to prese Function (Fn) + F4.
A beautiful shortcut for copying. Position the mouse in the lower right-hand corner of the cell until you see the plus sign, then doubleclick. Note that this option can copy the formula down as far as Excel finds data to the left.
Part 2: Prepare Employees Worksheet
Notice the two columns to the right of the Salary column: 'SalaryIncrease' and 'NewSalary'. You will use these columns to project salary increases.
Since the first year of the budget is for next year (not the current year) you can't use the current salaries in the budget. You need to use the projected salaries for next year based on the projected annual salary increase to be given to employees-- which is listed in cell B10 of the Parameters sheet.
(a) enter a formula in the first data row to get the salary increase amount (not percentage) equal to the salary times the salary increase rate
=F2*Parameters!$B$10
Note that you are anchoring the column ($B) and row ($10) because you want to make an absolute reference to the percent salary increase contained in B10 (in other words, you don't want this reference to shift when you copy cells).
(b) Copy this formula all the way down to the remaining rows.
NewSalary: Now that we have the salary increases, compute the corresponding salaries for (i.e., Year 1 in the budget).
Enter a formula in the first data row equal to the Salary plus the SalaryIncrease (no need to anchor here). The correct answer is below.
Copy this formula all the way down to the remaining rows (all cell references should have shifted when you copied the cells).
Part 3: Build DeptSalaries worksheet
While the Employees worksheet informs management about the salary for each employee, it does not allow to see salaries for all employees in each Department. Therefore, we need DeptSalaries.
At first glance, it might seem that all you must do is sort Employees by DeptName ascending; then use SUM to add the range of cells that corresponds to each department.
However, there are two problems with this manual approach. First, you need to manually specify the cell range for each department. After sorting the salaries for ACCT (Accounting), you would have entered the formula =SUM(J2:J7) but if a new employee was hired, then everything is messed up. Second, our example is just a small example (6 departments, 40 employees); but what if there were 50 departments and 5,000 employees?
The solution is to use the SUMIF command, which sums a range only if certain criteria are met. This solves both sorting problem (users can sort Employees any way they like and the results are unaffected) and the scalability problem (if there is lots of data).
The way SUMIF works is, using the logic for the first department ACCT: "Check the column containing DeptName and every time it equals ACCT, sum the range of NewSalary for employees who work in ACCT."
In Excel, the syntax is: =SUMIF(range, criteria, [sum_range]).
This formula will require a mix of anchored cells and relative addressing. Here's why. Excel needs to check the same ranges for all departments and for all salaries; however, we need to use relative addressing so that when we copy the formula down, Excel checks for the next Dept in the list (i.e., ACCT, FIN, etc.). Likewise, to account for the possibility the number of employees could change, you should select the entire columns for DeptName (C:C) and NewSalary (H:H); note: since you selected all possible rows, anchoring is unnecessary. The SUMIF statement for the ACCT department is given below; you need to complete the others on your own.
Part 4: Build Year 1 Expenses
- Salaries: in cell D2, sum all salaries for all departments from the DeptSalaries worksheet using the SUM formula.
- Benefits, bonuses: these expenses are a percentage of salaries. For benefits, in cell D3, multiply the salary sum figure from cell D2 by the benefit rate in the Parameters worksheet cell $B$13. Enter similar formulas for bonuses.
- Business trips, local travel, and computer equipment: these expenses are a fixed amount per employee, so you first need to count the number of employees. Switch to the Parameters sheet and in cell B21 you need to enter a formula to count all employees. To do so, we will use the COUNT formula, which counts the number of cells containing numbers. This way if the number of employees goes up or down, Excel will automatically update all the formulas.
=COUNT(Employees!A:A)
For Business Trips in cell D5 enter the formula
=Parameters!$B$21*Parameters!B15
which multiplies the number of employees by estimated business travel expenses per employee. Do the same for local travel and computer equipment.
Part 5: Prepare Revenues for Year 1
Go to the BudgetMain tab and go to cell B6. You will get started there.
All revenues for year 1 have been estimated already in the Revenue worksheet, but the rows are mismatched. This means you must use the SUMIF command. You will have Excel check ClientCategory (Column B in Revenue - anchored because we need to check the same range for all values) to see which cells match the 4 client categories in BudgetMain (Column A in BudgetMain)
The SUMIF will add up all the amounts for Universities. It inspects each row, one row at a time.
The formula in B6 is:
=SUMIF(Revenues!B:B,BudgetMain!A6,Revenues!F:F)
Notice that A6 is not anchored. That's because when the cell is copied, you want Excel to copy that on a relative basis.
Do a similar formula in B7, B8, B9. Do this by copying - not retyping.
Finally, enter a formula in cell B10 to compute the total projected revenues for year 1, use the SUM formula. It should look like this:
=SUM(B6:B9)
Part 6: : Prepare Revenues for Years 2-5
Estimate the revenues for Year 2 based on the budget you just prepared for Year 1 and revenue increase projections per below:
- In cell C6 of the BudgetMain: calculate the projected revenue from university clients using =B6*(1+Parameters!$B$2). - That is, the university revenue in Year 1, times 1 + the university revenue increase rate, which is the number in the Parameters sheet. - Do similar formulas for Government, Institutions, and Businesses - Copy the total from B10 into C10
Now using the power of cell anchoring, you can build the projected budget very quickly for Year 2 and into the future. This is when knowing which cells to anchor and which ones not to anchor really pays off.
If all your formulas for Year 2 are properly constructed, all you need to do is copy all of them to Year 3 and beyond. To do so, highlight C6 to C10 and then follow the box to copy.
When you're done with this you should have 5 years of revenues in Row 10.
Part 7: Prepare your first chart
Prepare a quick Revenue Chart, the first of several charts that you will prepare. Create a simple bar graph to display trends to visualize the company's revenues.
First, make sure you're on the BudgetMain worksheet.
Highlight Columns B-F in Row 10.
Now, Set up the new chart. To do this, choose Insert in the Ribbon. Then Select the "2D column" chart from the picklist.
A chart appears with values it extracts from the sheet. But the X axis lists 1, 2, 3, 4, 5 instead of Year. To fix that you need to tell Excel to point to the years in the table. Select the chart you just created. Then, on the ribbon, choose: Select Data. In the new window, choose: Horizontal Axis. Click Edit. Highlight the year labels in Row 4.
Attachment:- Instrction lab.zip