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

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:

  1. Open a new Excel file. In this assignment, working in sheet1, we will create the Sales Forecast shown above.
  1. Put the cursor between the A and B column letters. Click and drag to double the width of column A.

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.

  1. Merge cells A2 and B2. Enter “Prepared by:” and your name, in the merged cell.
  1. Enter the names of the months January to June in cells B3 to G3. Enter “Total” in cell H3. Center the cell contents in B3:H3 and apply a red font.
  1. Add text to cells in cells A4 to A22, as shown in the sample picture at the top. Center the text.
  1. For the labels Food Sales, Beverage Sales, and Ingredient Costs cells, change the font color to blue and add underlining.
  1. For the Sandwiches, Soups, Desserts, Coffee, Tea, Beer/Wine, Juices, and Soft Drinks cells change the font color to light green.
  1. For the food and the beverage categories, enter the numbers shown in the picture at the top as sales values for January in B5:B7 and in B11:B15. Leave the other cells in column B empty.
  1. Format the cells in the range B5:H22 as Currency with no decimal places. Note: Clicking on the $ icon formats cells as Accounting style, not Currency style.

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!

  1. Row 5: Sales of Sandwiches sales will increase by $1000 each month.  This formula would go in cell C5. Enter =B5+1000.
  1. *Row 6: Sales of Soups will increase 9% each month.

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

  1. Use the AutoSum icon [ALT  H  U] in Excel 2013 [ALT  H  U  S]  (repeat previous steps  on step 1 for AutoSum on Mac) to enter a formula in cell B16 to show the total of the range B11:B15.  Copy the formula into the range C16:G16.
  1. Enter a formula in cell B19 to show the January Food Sales total * 30%. Use a cell reference for the January Food Sales Total. Copy the formula into C19:G19.
  1. Enter a formula in cell B20 to show the January Beverage Sales total * 15%. Use a cell reference for the January Beverage Sales Total. Copy the formula into C20:G20.
  1. Enter a formula in cell B22 to calculate the Net Revenue. This will include the January totals for Food Sales, plus Beverage Sales, minus Food Costs, and minus Beverage Costs. Copy the formula into C22:G22.
  1. Use the AutoSum icon [ALT  H  U] in Excel 2013 [ALT  H  U  S]  (repeat previous steps  on step 1 for AutoSum on Mac) to insert formulas into column H. Show the total for each row (5 through 22) that contains numbers.
  1. Right-click on the worksheet tab (Sheet1) and change the name of the worksheet to Sales.

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

$

 

  1. Switch to sheet2. Right-click on the worksheet tab (Sheet2) and change the name of the worksheet to Payroll. Enter the column headings as shown.
  1. Enter job titles in column A: Manager, Baristas (drink preparers), Bussers (table cleaners), Cashiers, Cooks, Dishwashers, Janitor and Servers.

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 16-hour 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.

  1. Enter your estimates for the number of hours per job in column B.
  1. Decide on an hourly pay rate for each job and enter it in column C. Assume minimum wage is $8.00 per hour.
  1. Enter a formula in cell D2 to find the wages for the manager by multiplying the pay rate times the number of hours. Use two cell references, not numbers, as the values in the formula.
  1. Enter a formula in cell E2 to find the benefits for the manager by multiplying the wages times 35 percent. Use a cell references for the wages and 35% as the value.
  1. Enter a formula in cell F2 to find the total wages for the manager by adding wages plus benefits. Use two cell references, not numbers, as the values in the formula.
  1. Use the AutoFill handle to copy the formulas in cells D2, E2 and F2 into rows 3-9.
  1.  Switch to formulas view (Ctrl and ~) and notice how the row numbers in the cell references automatically changed. Switch back to the normal view (Ctrl and ~).
  1. In row 10 use the AutoSum icon [ALT  H  U] in Excel 2013 [ALT  H  U  S]  to enter the total of columns D, E, and F.

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.

  1. Use the decrease decimals icon [ALT  H  9] to change cells D2:F10 to show no decimal places. Each time you click it one numeral on the right is hidden.
  1. Click on the centering icon [ALT  H  A  C] to center the contents of the cells in the range B2:F10. 
  1. Use the borders icon [ALT  H  B] to add a thick box border to cell F10. Use the fill colors icon [ALT  H  H] to add a light yellow color to cell F10.

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 %

 

  1. Enter this table into your Payroll worksheet, starting in cell A12.
  1. Select the cells A12 and B12. Make the font bold [ALT  H  1]  and center the text horizontally [ALT  H  A  C].
  1. Select the cells in the range B13:B21. Center the numbers. [ALT  H  A  C]  Format the numbers in B13:B21 by clicking on the % icon [ALT  H  P]. Excel will multiply all your data by 100. This is a bug in Excel. To avoid this problem format cells as percent style before you enter numbers. Delete the numbers and enter them again. They should now appear correctly.
  1. In cell B22 use the AutoSum icon [ALT  H  U] in Excel 2013 [ALT  H  U  S] to find the total of cells B13:B21.
  1. Save the file using the file-name Budget. We will be using this file for the next assignment.

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?