Excel 2010/2011/2013 –
Budget 1
Two projects to practice basic Excel skills
Instructions:
Read through the set
of learning objectives. This is what you should will 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.
This table of sample formulas might be
useful when you are asked to create formulas in your worksheet.
Type of Formula: 
Example 

1 Addition of Two Cells 
= A2 + B3 

2 Addition of a Constant 
= B1 + 25 

3 Subtraction of a
Constant 
= C1 – 10 

4 Subtraction of a Cell 
= B2 – B1 

5 Multiplication by a
Constant 
= A3 * 20 

6 Multiplication of Two
Cells 
= B3 * C3 

7 Multiplication by a % 
= A1 * .40 = A1 * 40% 

8 Division by a Constant 
= C1 / 5 

9 Division by a Cell 
= A2 / C2 

10 Exponentiation
(Squaring) 
= B3 ^ 2 

11 Exponentiation
(Cubing) 
= A3 ^ 3 

12 Square Roots 
= B3 ^ 0.5 = B3 ^ (1/2) 

13 Increasing by a Percentage (4%) 
= A1 + (A1 * .04) = A1 * 1.04 = A1 + (A1 * 4%) =A1 * 104 % 

14 Decreasing by a
Percentage (8%) 
= A1 * 0.92 = A1  (A1 *.08) = A1 – (A1 * 8%) =A1 * 92% 




Summary of Key Points:
Use icons and
shortcuts to format cells.
Enter formulas into
cells.
Use the AutoFill
handle to copy formulas into adjacent cells.
Use the AutoSum icon
to add columns or rows of numbers.
Use a cell reference
in a formula.
Fun Video Java Juice
Sales Forecast Project
For this assignment, you will create a Coffee
Shop Sales Forecast for a six month period. Before you begin creating a
worksheet, it is a good idea to create a drawing or rough outline of how you
want it to look. As you work on the assignment, refer to this sample.
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 
Directions:


Use the
Merge and Center icon [ALT H M] in Excel 2013 [ALT H
M C] to merge cells A1 to H1. Type “Java Juice Café  Sales
Forecast” into the merged cell. Make the text bold with point 16 font. Note: The instructions in Green are for MAC (Office 11) users.
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. 







Enter formulas in column C to show the change in
sales values from January to February. Use cell references to refer to the
values in column B. As you create each formula, use the AutoFill handle to
copy the formula columns D through G. If you need help
with the formulas, look at the table at the top of the assignment!


*
Row 7: Sales of Desserts will increase 5% each month. 
*Row
11: Sales of Coffee will increase by 3% each month. 
*Row
12: Sales of Tea stay the same. 
*Row
13: Sales of Beer/Wine will decrease by 3% each month. 
*Row
14: Sales of Juices will increase by 15% each month. *Row
15: Sales of Sodas will increase by $1360 each month. 
12. Use the AutoSum icon [ALT H U] in Excel 2013 [ALT H U
S] to enter a formula in cell B8 to show the total of the range
B5:B7. Copy the formula into the
range C8:G8. To use AutoSum in Microsoft for Mac:
select cell b8 and then click on formulas on the top (ribbon) bar. In the bar
should now be a button that says AutoSum. Click it and then press enter. 






Payroll
Forecast Project
Prepare
an estimate of daily payroll expenses for the Java Juice Cafe. The cafe is open
16 hours per day, Monday  Saturday, from 6 a.m. to 10 p.m. The manager works 8 hours per day and is
paid $20 per hour. Benefit costs (insurance and taxes) for all employees are
estimated to add another 35% to the wage expenses.

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 
$ 


The café serves both food and drinks. It has 12 tables and can
seat up to 48 customers at one time. The café is busiest during the
first four hours each morning and for two more hours each night and will need
extra staff during those hours.
Critical Thinking Piece: Estimate the total hours
worked by employees performing each type of work during one 16hour day. There
is no single right answer; you are using your own judgment. Here are some
examples to get you started.
·
For cashiers there would be at least one person working each hour so the
total hours would be at least 16.
·
If you had any periods when you had more than one cashier working you
would have to add those additional hours to the 16.
·
Cooks might be 10 hours with one cook and 6 hours with 2 cooks, so the
total would be 10+12 = 22 hours.
·
Bussers would only work during busy hours so the total might be 6.








29. Change the
format of the numbers in the range C2:F10 into Currency Style. On
the Ribbon bar, make sure you are in the home tab. Under the number section
is where you change it from General to Currency. 



Benefit
Costs
Our benefit cost was
assumed to be 35% and we used that number in our formulas in column E.
Let’s create a table showing how we got that 35% number.
Benefits 
Cost

Health
Care 
12 % 
Holidays

1 % 
Medicare 
3 % 
Retirement 
1 % 
SDI 
1 % 
Social
Security 
6 % 
Unemployment
Ins 
3 % 
Vacations 
1 % 
Workers
Comp 
7 % 
Total 
35 % 
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 1
Y/N I can use icons and
shortcuts to format cells.
Y/N I can enter formulas into
cells.
Y/N I can use the AutoFill
handle to copy formulas into adjacent cells.
Y/N I can use the AutoSum
icon to add columns or rows of numbers.
Y/N I can use a cell
reference in a formula.
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?