Problem 1: Profit of Ticket Sales by Goal Seek Use Excel)

The Goal Seek technique of What-If Analysis can also be used in cascading computations, meaning the formula of the goal data cell may involve a data cell whose value is calculated or derived from one or more data cells that are not directly used in the formula. Below is one such type of application.

Suppose there is a simple football game ticketing application implemented on the Ticketing data sheet of the above Excel file.

This application is supposed to quickly calculate the revenue (B3), total cost (B7), and profit (B9) of each game if the following three formulas are entered into their corresponding cells.

Revenue (B3) = Total tickets sold (B1) x Ticket price (B2)

Total cost (B7) = Fixed cost (B5) + Total tickets sold (B1) x Ticket cost (B6)

Profit (B9) = Revenue (B3) - Total cost (B7)

In other words, you must enter "=B1*B2" as a formula in cell B3, "=B5+B1*B6" in B7, and "=B3-B7" in B9 in order to automatically calculate the revenue, total cost, and profit of a game.

To simplify this problem, we assume a flat price of $160 for all tickets and, no matter how many tickets are sold, there is a constant expense (i.e., fixed cost) of $451,902.45 associated per each game opeartion. Once the above three formulas are set in B3, B7, and B9 of the given spreadsheet, their values should be calculated automatically. Based on 35,000 tickets sold as entered in the file, the revenue, total cost, and profit would be $5,600,000, $2,021,652.45, and $3,578,347.55.

Note that, when the ticket revenue is not higher than the sum of fixed cost and the cost of tickets sold, the profit could become a negative value, i.e., a loss. All data cells in the file except B1 have been already formatted to display in red and enclosed by parentheses with no minus sign to display a negative value like ($12,500,98).

Use the data values given in the file and the Goal Seek method of Excel to solve the two problems below.

1. In order to reach a profit of ten millions dollars, what should be the minimum number of total tickets sold? This must be a whole number. For instance, you cannot sell 256.18 tickets, which should be rounded up to 257 tickets. For grading, you also need to copy the number of tickets found and enter it directly in cell E1 (then, you can use the same datasheet for problem B below.)
2. Due to the fixed cost of $451,902.45, if you don't sell enough tickets, your profit becomes negative (i.e., loss).
   1. 1. To avoid negative profit, what should be the minimum number of total tickets sold? Again, the answer must be a whole number.
      2. With these tickets sold, what is the profit (i.e., lowest possible profit)?
      3. In case one less ticket is sold than the number you answered in B.I, what will the profit become? This must be a negative value.

Please type your answer of the above questions I ~ III directly in cell E2, E3, and E4. Then, hit OK in the Goal Seek dialog to save all numbers in the file. Here the profit should be a negative value.

\begin{tabular}{|l|r|} \hline Tickets sold & 35000 \\ \hline Ticket price & $160.00 \\ \hline Revenue & \\ \hline & \\ \hline Fixed cost & $451,902.45 \\ \hline Ticket cost & $44.85 \\ \hline Total cost & \\ \hline & \\ \hline Profit & \\ \hline \end{tabular}