| You have been asked to create expense and revenue estimates for a
picnic. Excel is an excellent tool for 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 the 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
|