PLEASE SHOW EXCEL FUNCTIONS Bri Smith and Joe Sullivan, two kinesiology students
ID: 3040416 • Letter: P
Question
PLEASE SHOW EXCEL FUNCTIONS
Bri Smith and Joe Sullivan, two kinesiology students at ASU, are considering the possibility of offering swimming lessons to children at the university pool. The pool would charge a fixed rental fee of $1500 for the 8-weeks of lessons as well as an admission and lifeguarding fee of $7 per pupil. Bri and Joe estimate an additional cost of $4 per pupil to hire additional helpers. Bri and Joe plan to charge $65 per student for the 8-week class.
a. Use the data given to create a model for the total profit. Use the Excel tool Data/What-If Analysis/Goal Seek to determine the following:
b. the break-even quantity for the number of pupils needed to enroll in the swimming class.
c. the number of pupils they need to enroll if they want to make a profit of $5,000.
d. how much per pupil they would have to charge if only 60 pupils enrolled and they still wanted to realize their profit goal of $5,000. e. Create a cost/revenue graph that shows total cost and total revenues (two lines) versus the number of pupils. (Make sure you reset the charge per pupil to $65 to determine graph inputs and use the range of the number of pupils from 0 to 150)
Explanation / Answer
a)
b) the break even quantity for the number of pupils to be enrolled by uisng goal seek in excel:
c) number of pupils to be enrolled to make a target profit of $5000
d) Charge per pupil if only 60 pupils were enrolled and a target profit of $5000 is required
Hence, aunit price of $119 would have to be charged.
Unit Price $65 Units sold Total revenue $0 Fixed costs $1,500 variable costs $11 Total cost $1,500 Profit -$1,500