excel homework

ITEC-200 Excel Budget Report Lab

1

Technical Learning Objectives

• Manipulate data and add formulas.

• Use: Anchoring, SUMIF, COUNT, copy- and-paste.

• Build, use, modify and nicely format a "results" worksheet for presentation to management.

• Create charts.

Business Learning Objectives

• Learn to prepare one of the most-useful spreadsheet of them all: a pro forma budget, which has applications in Applications: Accounting, Finance, Entrepreneurship, and International Business.

Scenario

You will play the role of a business analyst who has been charged with the task of developing a pro forma 5-year budget (pro forma means based on financial projections; these budgets are common in nearly all businesses). You work for Eagle Corporation, a well-known conglomerate, which revenues and costs related to four different types of clients: universities, government, institutions and private businesses. It also has other costs such as salaries and travel.

This is a two-part assignment: Lab 1 will be completed in class – only cells highlighted in blue

are part of this lab – all others will be completed in a subsequent homework.

Preparation

Begin by downloading the file from Blackboard ITEC200 Lab 1.xlsx and rename it according to the following convention: ITEC200-00X_last name_first name_Lab1 (where 00X = your section number).

Notice that the spreadsheet has 7 worksheets; click on each to get to get familiar with the data.

1. BudgetMain – this is the main summary sheet for presentation to management 2. Revenues 3. Expenses 4. Employees 5. DeptSalaries 6. Depreciation 7. Parameters – all the parameters are concentrated here 8. Charts – this is empty

Read Technical Notes on cell referencing and anchoring Mastering this "anchoring" technique is essential when building spreadsheet models—or else you will have errors.

TECHNICAL NOTES ON CELL REFERENCING AND ANCHORING.

ITEC-200 Excel Budget Report Lab

2

A cell is referenced by its row and column (e.g., D6). You enter data in a cell simply by typing on it. For example, go to cells C3, D3 and C4 and enter 100, 200 and 300 respectively. To edit data in a cell or to modify a formula, simply highlight the cell and either double-click, press the F2 key, or simply by correct it in the formula box above the worksheet. If you enter a formula in a cell (e.g., D6) that refers to another cell (e.g., =C3*2), the spreadsheet will take the contents of that cell (i.e., C3), multiply it by 2, and place the results in D6.

• Excel formulas are preceded by the = symbol, otherwise Excel will treat what you enter as plain text, not formulas. For example, if you type C3*2 in cell D6, you will get text in that cell that reads "C3*2" (try it). But if you type =C3*2 (with the = sign in front), excel will treat it as a formula and execute it.

Relative Referencing (Excel default)

• If you copy this formula to the adjacent cell to the right (e.g., E6), the copied formula will also be shifted one column to the right (e.g., =D3*2). If you copy the formula to the cell immediately below (e.g., to D7), the copied formula will also be shifted one row down (e.g. =C4*2). This is called "relative referencing" because the referred cells change correspondingly when you copy cells. This row and column shifting when you copy cells is done automatically. It's a convenient way to copy/apply formulas consistently across the workbook.

• Anchoring (or Absolute Referencing)

• If you don't want this shifting to occur, you need to use "anchors" denoted by the $ sign. You can anchor the row (e.g. =C$2*2), the column (e.g. =$C2*2), or both (e.g. =$C$2*2). This is "absolute referencing" because what you anchor does not shift when you copy cells. Important: anchoring does not affect the formula itself (e.g., =C2*2 will yield identical results to =$C$2*2), only how the formulas are copied to other cells.

• Multiple Sheets

Formulas can refer to cells in other sheets. For example, if you have a sheet called "Main" (you can double-click on the sheet tab to rename the sheet) and a sheet called "Data", you can type formulas in the "Main" sheet that refers to data in the "Data" sheet. So, if you enter the values 100, 200, and 300 in cells C3, D3, and C4 of the "Data" sheet, you can enter a formula in the "Main" sheet, say in D6, like this =Data!C3*2. This will take the contents of cell D3 in the Data sheet (denoted by Data!), multiply it by 2, and place the results in cell D6 of the "Main" sheet. Try it. Then copy it to cell E6 and D7 and see what happens.

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.

ITEC-200 Excel Budget Report Lab

3

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.

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.

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 double- click. Note that this option can copy the formula down as far as Excel finds data to the left.

ITEC-200 Excel Budget Report Lab

4

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?

ITEC-200 Excel Budget Report Lab

5

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.

ITEC-200 Excel Budget Report Lab

6

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).

ITEC-200 Excel Budget Report Lab

7

• 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. Likewise, the title "Chart Title" is not useful so you must click inside the title and make it read something intuitive like "Revenue by Year". Your final chart should look like the image to the right. Upload your completed Excel file to Blackboard.

ITEC-200 Excel Budget Report Lab

8

Lo o

k s

a re

im p

o rt

a n

t!

Ensure that the BudgetMain worksheet is formatted in a business-like manner (e.g., bold faced subtotals, consistent use of fonts, even spacing, nice and clear titles on top, and visually attractive overall). Most of the other sheets are less important for looks: because they are working sheets and they serve as your backup documentation in case managers have questions.

ITEC-200 Excel Budget Report Lab

9

Background History on Spreadsheets and Microsoft Excel According to University of Maryland Computer Science Professor D. J. Power's web essay "A Brief History of Spreadsheets" the idea of a computerized spreadsheet for business accounting came in 1961 from an accounting professor at the University of British Columbia named Richard Mattessich. However, it took Harvard MBA student Dan Bricklin to make it happen in 1978, when computers got better. Bricklin was working on a case study report that required significant financial calculations and was faced with the prospect of having to do it by hand. Instead, he thought there must be a better way. He developed a program in which people could visualize the spreadsheet as they created it. This led to the development of the first, successful electronic spreadsheet known as "VisiCalc" (short for "Visible Calculator"). The success of Visicalc and the rise of personal computers in the 1980s prompted competition for the growing spreadsheet market and led to the first business "killer app" - Lotus 1-2-3, which introduced now-common features such as naming cells, cell ranges and macros. Lotus 1-2-3 dominated the spreadsheet market until 1987 when Microsoft Excel came along and featured a graphical user interface (GUI, pronounced "gooey") with now-familiar options like menus and point and click commands. Since the mid-1990s, Excel has been the most popular spreadsheet and analytics program on the planet (although Google Sheets is gaining popularity).

Bill Gates, Microsoft founder, with an early Excel version