Question

Exercise 2:

Create a spreadsheet to calculate your projected total costs, total revenues, and total profits

for giving a seminar on cost estimating. Make the following assumptions:

• You will charge $600 per person for a two-day class.
• You estimate that 30 people will attend the class, but you want to change this input.
• Your fixed costs include $500 total to rent a room for both days, setup fees of $400 for

registration, and $300 for designing a postcard for advertising.

• You will not include your labor costs for this estimate, but you estimate that you will spend at least 150 hours developing materials, managing the project, and giving the actual class. You would like to know what your time is worth given different scenarios.
• You will order 5,000 postcards, mail 4,000, and distribute the rest to friends and colleagues.
• Your variable costs include the following:

1. $5 per person for registration plus four percent of the class fee per person to handle

credit card processing; assume that everyone pays by credit card

2. $0.40 per postcard for printing if you order 5,000 or more
3. $0.25 per postcard for mailing and postage
4. $25 per person for beverages and lunch
5. $30 per person for class handouts

Be sure to have input cells for any variables that might change, such as the cost of postage

and handouts. Calculate your profits based on each of the following numbers of people who

might attend: 10, 20, 30, 40, 50, and 60. In addition, calculate what your time would be worth

per hour based on the number of students. Try to use the Excel data table feature to show

the profits based on the number of students. If you are unfamiliar with data tables, just repeat

the calculations for each possibility of 10, 20, 30, 40, 50, and 60 students. Print your results

on one page, highlighting the profits for each scenario and what your time is worth.

Answer 1

Exercise 2:

Table:

S. No.	No. of Attendees	Fixed Cost (in $) (= $ 500 + $ 400 + $ 300)	Registration and Processing Fee (in $)	Printing Fee (in $) (= $ 0.4 x 5000)	Mailing and Postage Fee (in $) (=$ 0.25 x 4000)	Beverages and Lunch Cost (in $) (=$ 25 x No. of Attendees)	Class Handout Cost (in $) (=$ 30 x No. of Attendees)	Variable Cost (in $) (= D + E + F + G + H)	Total Cost (in $) (= Fixed Cost + Variable Cost)	Total Revenue (in $) (= $ 600 x No. of Attendees)	Total Profit (in $) (= Total Revenue - Total Cost)	Time Worth per hour (in $) (= Total Profit / 150 hours)
1	10	1200	290	2000	1000	250	300	3840	5040	6000	960	6.4
2	20	1200	580	2000	1000	500	600	4680	5880	12000	6120	40.8
3	30	1200	870	2000	1000	750	900	5520	6720	18000	11280	75.2
4	40	1200	1160	2000	1000	1000	1200	6360	7560	24000	16440	109.6
5	50	1200	1450	2000	1000	1250	1500	7200	8400	30000	21600	144
6	60	1200	1740	2000	1000	1500	1800	8040	9240	36000	26760	178.4

The Excel Sheet is given below: