UseExcel: Expenses and Revenues for a Company Picnic

You have been asked to create expense and revenue estimates for a Company Picnic. Excel is an excellent tool in which to do this. The expense and revenue estimates will be in four sections, fixed cost expenses, variable cost expenses, attendence estimates and revenue estimates.

Fixed Cost Expenses

Make a list of fixed cost expenses and enter them in column A, row 5.
Figure 1: List of fixed cost expenses

In cells B2, C2, and D2 enter column titles Unit Cost, Quantity, Total Cost. Enter Unit Costs of $50.00, $50.00, $43.00; quantities of 1, 2, 1, respectively for the 3 fixed cost expenses.
Figure 2: Enter Unit Costs

The Total Cost formula for Park Rental should go in cell D5 and should look like =B5*C5. Select cell D5 and autofill down to cell D7. Enter a Total title in cell A8, and in cell D8, click the AutoSum icon on the Standard toolbar to produce the sum of $193.00 in cell D8. Format columns B and D as currency. This is what the fixed cost expense should look like (below).
Figure 3: Total Cost formula

Variable Cost Expenses

Next, list the Variable Cost Expenses in the same manner as the Fixed Cost Expenses. Leave the Quantity and Total Cost columns empty for now. We will make this a very robust expense/revenue estimator by relying on other sections such as Attendance.
Figure 4: List of Variable Cost Expenses

Attendance at the picnic

Since this is a company picnic, we know how many people work in the company, and that figure is 250 people. Let's assume that family members are invited to the picnic too, and the average family has 2 people in it. Finally, let's assume that 60 % of all company employees will attend. Remember, the average family size and estimated percentage of employees attending can easliy be changed in the Excel worksheet. The forumla for Potential Attendance is =G4*G5; for Est. Attendance is =G6*G7.
Figure 5: Picnic Attendance

Variable Cost Expenses Quantity and Total Cost Columns

Now we can fill in the Quantity column (C) and the Total Cost Column (D) for the Variable Cost Expenses section. The formula for the Food Quantity in cell C12 is =$G$8. Notice that we make the cell reference for G8 an absolute reference by putting dollar signs ($) in front of the column number and row number. This way, when we autofill down, the Quantities in column C will always point to G8. The formula for Food Total Cost in cell D12 is =B12*C12.
Figure 6: Variable Cost Expenses

We can estimate Expenses Grand Total in cell D17 with the formula =D8+D15. Average Expense Per Person is estimated in cell D 18 with the formula =D17/G8.
Finally, we can estimate the picnic ticket price in cell G11. Rather than using a formula, which would be more precise, we enter a rounded ticket price of $6.00. We add the cell comment in yellow to explain where this rounded estimate came from. See Figure 7 below.

Figure 7: Expenses Grand Total & Unit Revenue.

In this Picnic example, we are assuming that we are trying to break even. This design is very flexible. Had we reached an Average Expense Per Person of just over $6, we could have adjusted the unit food costs a bit, or hired only 1 clown instead of 2 clowns. We could adjust the number of employess downward until we reached some ticket price ceiling that we were willing to exceed. If we were reluctant to exceed $10.00 per person, then the lower limit for number of employees is around 35.
Here is the completed Excel workbook Picnic.xls