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, inclass 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 payrollcostperday (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? Twentysix. 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 payrollcostperday value. 
17.
To find the net profit subtract the sixmonth
payroll cost from the sixmonth 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 =F13F14. 
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 filename 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?