Assignment
Instructions: This is an open-book exam, but you are NOT allowed to use the Internet connection during the exam except when you download the spreadsheet template and upload your solution spreadsheet to Blackboard.All calculations must be done using Excel (formulas or functions), using no other method of calculation (calculators, hand-calculations). When you have completed the exam, please do the following:
1) Raise your hand and inform the instructor that you are uploading your exam file to Blackboard. At this point you can turn your wireless connection back on.
2) Turn in your exam paper. Be sure your name is on it.
3) Please save your exam file on your computer in case there is a problem with Blackboard.
You have been hired to construct a spreadsheet that will help decide if TV shows that you produce should be continued or cancelled. The decision to cancel or continue is based on the following threerevenues sources and threecosts:
a) Network (revenue)
b) Advertising (revenue)
c) Canadian rights (revenue)
d) Director and Hosts'costs(cost)
e) Show Production (cost)
f) Location (cost)
Download the file 2701-Fall15-Exam1 TV DR Template.xlsx which contains the data in the table below. This spreadsheet contains the data you'll be using in this data. You should save the file now to a known location on your computer.Remember that I should be able to change the values and your spreadsheet will still work as described below.
|
Show Name
|
Network
|
Type of Show
|
# Episodes Per Year
|
# of Hosts
|
Viewers
|
Location
|
|
The Review
|
USCBS02
|
Talk
|
12
|
6
|
1,200,000
|
NY
|
|
Kitchen Fixit
|
USHBO14
|
Home
|
32
|
2
|
2,100,000
|
CA
|
|
Bernie Jones Show
|
UKPBS07
|
LateNight
|
10
|
1
|
3,600,000
|
NY
|
|
Home Reno
|
USSHO35
|
Home
|
45
|
3
|
2,500,000
|
TX
|
|
The Morning Show
|
USNBC04
|
News
|
24
|
5
|
5,700,000
|
CA
|
Revenue:
a) Network fees - if a show plays on either HBO or Showtime (SHO), there is a $36,500 annual fee paid to you. Shows that play on HBO or Showtime are indicated by having HBO or SHO as the third, fourth and fifth characters of the Network name.
b) Advertising - If the number of viewers is above 5,000,000, then annual advertising revenue is $2,400 per ten thousand viewers.Otherwise, the annual revenue is $1,650 per ten thousand viewers. For example, the show "The Review" has advertising revenues of $198,000.
c) Canadian Rights - The fees for annual Canadian TV Rights are determined by the table below:
|
Viewers
|
Canadian Rights
|
|
0 -1,600,000
|
220,000
|
|
1,600,001 - 2,999,999
|
370,000
|
|
3,000,000 - 4,999,999
|
555,000
|
|
5,000,000 +
|
780,000
|
Costs:
a) Director and host's Costs - Each episode has one director and the number of hosts as specified in the spreadsheet. Each director is paid $15,000 per episode and each host is paid $2,500 per episode.
b) Production Cost - The table below lists the costs to produce an episode of a show based on the Type of Show.
|
Type of Show
|
Production Cost per Episode
|
|
News
|
$375,000
|
|
Home
|
$250,000
|
|
Talk
|
$525,000
|
|
LateNight
|
$360,000
|
You will pay for the production cost of all the episodes for one year with a loan that is paid monthly, at a 4.1% interest rate over 11 years. The Production Cost is the cost of a year's worth of payments.
c) Location Cost - Programs produced in New York are charged an additional fee. NYin the Location column indicates that there is a fee which is2% of the total annual revenue of that show. However, the maximum feeis $12,000.
Continue or Cancel Rule
If the number of viewers is greater than 4,000,000 or the profit of the show is greater than $500,000, you will continue to run it. Otherwise you will cancel the show.
Construct your spreadsheet so that the following values are calculated. The values in parentheses show how much the question is worth.
a) Annual Network Fee
b) Annual Advertising revenue
c) Annual Canadian rights
d) Total Annual Revenue
e) Annual Director and Hosts Cost
f) Total Annual Production Payments
g) Location Cost
h) Total Annual Cost
i) Annual Profit
j) Display the word Continueor Cancel based on the rule above.
Make sure your spreadsheet is laid out in an organized, logical manner and has the following:
1) Include a one row title at the top that says "TV Shows" and that is 18 point Times New Roman font, with blue letters on a light green background. The title should be centered across all the columns that have values.
2) Each column should have a heading with a description that is text wrapped.
3) Values for percentagesshould be displayed with two decimal places; all othervalues should be displayed with zero decimal places.
4) Be sure to have an assumptions section. An example of a constant that should be in this section is the $2,500 host's cost per episode. Determine values that should be assumptions.
5) Use absolute addressing where appropriate.
Post your spreadsheet to Blackboard.Do not leave until you have checked with me that I can download your exam.
Attachment:- Template.rar