Excel – Budget 2

In this assignment we will learn about some common problems with Excel formulas and how to fix them.

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Use a cell reference instead of a constant in a formula.

Fix a multiplication formula which gives a value of zero

Use a dollar sign as part of a cell reference.

Fix a division formula that shows a #div/0! error message.

Write a cell reference to a cell in a different worksheet.

Payroll Forecast Project, Part 2

1.       Open the Budget file you created in the previous assignment.

2.       In the previous assignment we renamed sheet2 to Payroll. Select this worksheet; it should contain the payroll forecast.

 

 

A

B

C

D

E

F

1

Job Title

Hours

Pay Rate

Wages

Benefits

Total

2

Manager

8

  $20.00

  $160

  $56

  $216

3

Baristas

 

  $

  $

  $

  $

4

Bussers

 

  $

  $

  $

  $

5

Cashiers

 

  $

  $

  $

  $

6

Cooks

 

  $

  $

  $

  $

7

Dishwashers

 

  $

  $

  $

  $

8

Janitor

 

  $

  $

  $

  $

9

Servers

 

  $

  $

  $

  $

10

Totals:

 

 

  $

  $

  $

11

 

 

 

 

 

 

12

 

 

 

Six Months Sales

$800,000

13

 

 

 

Six Months Payroll

 

14

 

 

 

Net Profit

$

3.        

4.       Insert a new row 1 by right clicking on the number 1 in the row numbers column and selecting Insert.

5.       Merge cells A1 to F1. Enter the title “Java Juice Café - Payroll Forecast” into the merged cell. This is the title cell.

for MAC/EXCEL 2011 Select the cells that you wish to merge and then right click them. Then select format cells on the screen that pops up. Click on the Alignment tab and then check the box that says merge cells. When you go back to your spreadsheet, those cells should be merged.

for MAC/EXCEL 2011 -Alternative. If you are on the Home tab in the ribbon bar, there should be smaller sections and one named Alignment. There should be a drop box under it named merge and all you have to do is click and choose the appropriate merging style.

6.       Use the “middle align” icon [ALT  H  A  M] to format the title cell so the text is centered up and down.

for MAC/EXCEL 2011 To middle align your cell, select it and then go to the ribbon bar and again look under the Alignment tab. Under it to on the left are two rows of three icons. In the first row, select the middle icon and in the second row select the middle icon also.

7.       Change the height of row 1 to make it about 50% taller.

8.       Add a fill color to the title cell. Add a different color to cells A2:F2 and cells A13:B13.  Add a third fill color to cells A3:A10 and cells A14:A23.

9.       Apply “all borders” to the ranges A2:F11 and A13:B23.

for MAC/EXCEL 2011 In the Home tab of the ribbon under the font tab, there is a small drop box next to the underscore icon that shows a square. Click on that and a drop box will appear and you can select all borders.

10.   Use the borders icon [ALT  H  B] to apply a “thick box border” to the title cell.

for MAC/EXCEL 2011 Repeat previous step to access drop box for borders and then select thick border.

11.   Merge cells D13 with E13, D14 with E14, and D15 with E15.

12.   Enter “Six Months Sales” into cell D13, “Six Months Payroll” into cell D14, and “Net Profit” into cell D15.  Widen columns D and E if you can’t see all of the text.

13.   Click on cell F11.  Copy the format of cell F11 into cells F13, F14, and F15.

14.   In cell F13 enter 800,000 (eight hundred thousand. Do not include the comma) for the Sales amount.

15.   You can multiply the payroll-cost-per-day (cell F11) times 156 days to find the total payroll cost for six months. Why 156? How many days will the café open be open each week? Six. How many weeks in six months? Twenty-six. So the café will be open 6 days times 26 weeks during the six month period. 6*26 = 156 days.

16.   Use the information in step 14 to enter a formula in cell F14 to calculate your payroll cost for six months. Use a cell reference for the payroll-cost-per-day value.

17.   To find the net profit subtract the six-month payroll cost from the six-month revenues. Enter a subtraction formula in cell F15, using two cell references for the values.

18.   What happens to your profit if you change the hours for one of the job categories? Try it.

19.   Which cells are affected? Undo the change (Control and Z)

20.   What happens to your profit if you change the pay rate for one of the job categories? Try it.

21.   Which cells are affected?  Undo the change (Control and Z)

How does the payroll cost for six months compare to your estimated revenues? A restaurant’s payroll would typically be between 25 and 35% of revenues. If the sales revenue is $800,000 the payroll should be between $200,000 and $280,000. Your profit should be between $520,000 and $600,000.

22.   If needed, make some changes to hours and pay rates to get the profit into the desired range of $520,000 to $600,000. If you are not seeing changes in cells F13, F14 and F15, check your formulas in those cells. They should be 800,000, =F11*156, and =F13-F14.

23.   What happens if you change one of the items in the benefits table? Try it.

None of the values in the E column changed because when we created those formulas we used a constant number, 35%, to represent the benefit cost. We should have used a cell reference, B23, instead. The benefits costs can go up and down, so the 35% is a variable, not a constant. Whenever you use a number (a constant) in a formula to represent a variable, your formula will give the wrong result when the value of the variable changes.

24.   Try editing the formula in cell E3 to replace 35% with the cell reference B23.

Did the total chance in that cell and in cell F3? That change should have fixed the problem in row 3. We still need to fix the other rows.

25.   What happens if you copy the formula from E3 into the range E4:E10? Try it.

Did you get zeroes in those cells? What went wrong? Excel automatically adjusts the cell references when you copy a formula into a new row. The formula in cell E3 was = D3 * B23. When we copied it down it became = D4 * B24. The D4 part was what we wanted but we didn’t want the B23 to change. We need to multiply everything in column E by B23. Cell B24 had nothing in it so Excel treated it like a zero.

26.   We can fix the problem by editing each cell in E4:E10 to change all the incorrect row references back to B23. Do that now.

That is not too hard to do, there are only a few cells affected. But it would take a long time to fix this problem if we had a lot of cells that needed correcting. Fortunately there is an easier way to handle this. We can simply write the cell reference in our original formula in a special way that tells Excel to not adjust the row numbers in the cell reference. If you include a dollar sign as part of a cell reference the dollar sign tells Excel to not adjust whatever comes after it. The dollar sign means “don’t change the …”

For example = D3 * B$23 would mean that when the formula is copied into other rows we want to change the row numbers in the first cell reference but we don’t want to change the row number in the second one.

27.   Try changing the formula in cell E3 to = D3 * B$23 and then copy it into cells E4:E10.

Did you get numbers in those cells instead of zeroes?

28.   Switch to formula view (Control and ~) and look at the formulas in cells E3 to E10. Did the dollar sign do its job?

29.   Switch back into the regular view (Control and ~).

30.   Try making a few changes to the numbers in the benefits table. Did the numbers change in the payroll section?

Sales Forecast Project, Part 2

Java Juice Café - Sales Forecast

Prepared by:  Your Name

 

 

 

 

 

 

 

January

February

March

April

May

June

Total

Food Sales

 

 

 

 

 

 

 

Sandwiches

$41,500

$42,500

$43,500

$44,500

$45,500

$46,500

$264,000

Soups

$19,000

$20,710

$22,574

$24,606

$26,820

$29,234

$142,943

Desserts

$22,000

$23,100

$24,255

$25,468

$26,741

$28,078

$149,642

Total Food Sales

$82,500

$86,310

$90,329

$94,573

$99,061

$103,812

$556,585

 

 

 

 

 

 

 

 

Beverage Sales

 

 

 

 

 

 

 

Coffee

$34,000

$35,020

$36,071

$37,153

$38,267

$39,415

$219,926

Tea

$15,000

$15,000

$15,000

$15,000

$15,000

$15,000

$90,000

Beer/Wine

$12,000

$11,640

$11,291

$10,952

$10,624

$10,305

$66,811

Juice

$5,000

$5,750

$6,613

$7,604

$8,745

$10,057

$43,769

Soft Drinks

$7,000

$8,360

$9,720

$11,080

$12,440

$13,800

$62,400

Total Beverage Sales

$73,000

$75,770

$78,694

$81,789

$85,076

$88,577

$482,906

 

 

 

 

 

 

 

 

Ingredient Costs

 

 

 

 

 

 

 

Food Costs

$24,750

$25,893

$27,099

$28,372

$29,718

$31,144

$166,976

Beverage Costs

$10,950

$11,366

$11,804

$12,268

$12,761

$13,287

$72,436

 

 

 

 

 

 

 

 

Net Revenue

$119,800

$124,822

$130,120

$135,722

$141,657

$147,959

$800,080

Let’s practice using the dollar sign.

31.   Select the worksheet that has the sales forecast.

32.   We want to find the percentage that each category contributes to the total food sales.

33.   Add the word Percent into cell i4.  A percent is always a fraction, using the grand total as the denominator.

34.   Format the cells in the ranges i5:i7 and i11:i15 as percent style. [ALT  H  P] Make sure to show two decimal places.

for MAC/EXCEL 2011 Select the cells in the ranges i5:i7 and i11:i15 and then right click and open the format cell box. Click on the number tab and then select the percentage button. When it opens make sure to change the decimal places to two decimals and then click ok.

35.   In cell i5 enter the formula = H5 / H8. This will divide the category total in H5 by the grand total in H8.

36.   Copy the formula in cell i5 down into cells i6 and i7.

What happened?  Do you get the #DIV/0! Error message? That’s because when you copied the formula in i5 into cells i6 and i7 Excel changed the row numbers in the H8 cell reference to H9 and H10. Those cells are empty so Excel treats them as zeroes. You can’t divide anything by zero so we get an error message.

37.   Edit the formula in celli i5 to add a dollar sign in front of the 8. (=H5 / H$8). Now copy it down into cells i6 and i7.

What happened?   Did you get numbers in the cells? You should be ok now.

38.   Click on cell i8 and then use the AutoSum icon [ALT  H  U] in Excel 2013 [ALT  H  U  S] for MAC/EXCEL 2011 or the shortcut [Command+Shift+T] to show the total of cells i5, i6 and i7. Does it show 100%? It should.

39.   Try entering the number 200 into cell i5. Did the value in cell i8 change? If it did your formula is working correctly.

40.   Undo the change by pressing Control and Z so cell i8 goes back to showing 100%. 

41.   Copy the formula from i7 into i8.

Does the value in i8 still show 100%? The formula in cell i8 is now = H8 / H$8. A number divided by itself is always going to equal 100%. The formula is not actually adding up the values in i5 to i7, it just looks like it.

42.   Try entering the number 200 into cell i5. Did the value in cell i8 change?

43.   Undo the two changes you just made.

Now try it on your own.

44.   Enter a formula in cell i11 to find the percentage for the first beverage sales category. Include dollar signs where needed so you can copy the formula into cells i12to i15.

45.   Copy the formula into cells i12 to i15.

46.   Enter a Sum function to cell i16 to check that the percentages add up to 100%.

Now take a look at the food ingredient costs formula in cell B19. Does it say = B8 * 30%?

We used 30% as the percentage of the revenue that went to pay for the ingredients needed to make the food. The cost of the ingredients vary from month to month so we made a mistake when we used 30% as part of the formula. We will get wrong answers. Variables should always be entered as cell references. Let’s fix that now.

47.   In cell A25 enter the label Food Cost %.  In cell A 26 enter the label Beverage Cost %.

48.   Format cells B25 and B26 as percents. In cell B25 enter 30 and in B26 enter 15.

49.   In cell B19 change the formula to = B8 * B25. Did it work?

50.   Copy the formula from B19 into the range C19:G19. 

Did it work or did you get zeroes?  This is due to a similar problem as the one we ran into when we copied the percent formulas into new rows. This time we are copying formulas into new columns and Excel is adjusting the letter part of the cell reference. The formula changed from = B8 * B25 into = C8 * C25, then into = D8 * D25 and so on.

Why did we get zeroes?  The adjusted formulas in C19:G19 are multiplying by cells in the range C25:G25. These cells are empty so Excel treats them as zeroes. Multiply anything by zero and you get zero as the result.

To avoid this problem we need to write our formula in B19 in such a way that the second cell reference, B25, stays the same every time we copy the formula. We wanted =B8 * B25 to change into = C8 * B25, then = D8 * B25 and so on. To fix this we can use a dollar sign to tell Excel not to change the B in the second cell reference.

51.   Change the formula in B19 to = B8 * $B25.  Change the formula in B20 to = B16 * $B26.

52.   Copy the revised formulas into the range C19:G20.

Now take a look at our grand total, net sales revenue in cell H22. That value is 800,080. We used a slightly different value, 800000, in cell F13 of the Payroll forecast. We could enter 800080 in cell F13 to make them match. But the next time we make changes to the sales forecast the revenue number in the payroll forecast will be wrong again. We want those numbers to always match. We can fix this problem by writing a formula in cell F13 in the payroll forecast worksheet that copies the number from cell H22 in the sales forecast worksheet. 

How? To write a formula referring to a cell in another worksheet, start with the name of the worksheet, followed by an exclamation mark, and then type the cell coordinates.

53.   In the Payroll worksheet click on cell F13 and type = Sales!H22.

Save the file using the file-name Budget2. You will be building charts based on these worksheets.

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Budget 2

Y/N I can use a cell reference instead of a constant in a formula.

Y/N I can fix a multiplication formula which gives a value of zero.

Y/N I can use a dollar sign as part of a cell reference.

Y/N I can fix a division formula that shows a #DIV/0! error message.

Y/N I can write a cell reference to a cell in a different worksheet.

 

Effectiveness Questions to discuss with your team

What do I know that I didn’t know this time last week?

What can I do now that I couldn’t do this time last week?

What could I teach others to know or do that I couldn’t teach them last week?