Financial Management

Homework Assignment #3

Dearborn Center for the Performing Arts

The director of the Dearborn Center for the Performing Arts has asked you to prepare a variance report comparing the centers budgeted and actual revenues, expenses, and profits for FY 2015.

a)In the variance column of the Excel template, calculate each variance in dollars. Remember that favorable variances should be positive numbers and unfavorable variances should be negative numbers.

b)In the U/F column, label unfavorable variances U and favorable variances F using an IF formula. For the revenue ticket sales, the formula would be: =IF(E7>=0,F,U).

c)Use conditional formatting to make the Us red. Select cells F7:F25, then choose conditional formatting, then highlight cell rules ---> text that contains, then enter U. From the Format with drop down menu, select red text and press OK.

The Dearborn Centers director wants to know why the center spent more on program printing than anticipated in FY 2015. Use the following information to help her answer this question. The budget was based on the assumption that the center would put on 225 performances throughout the year, but it actually put on 217 performances. The expected cost of printing was 50 cents per program, but the actual cost was 60 cents per program. The center gives one program to each patron. (Hint: use the information in this question and in the variance report to find the budgeted and actual numbers of programs.) Calculate the volume, quantity, price, and total variances, and then label each variance U or F. Use the information in the variance analysis to respond to the following prompts:

a)Write a sentence explaining the quantity variance without using financial jargon.

b)Which variance most contributed to the unfavorable total variance? How do you know?

c)Were any of the variances favorable? If yes, explain why the variance(s) is/was favorable.

The Dearborn Center puts on two types of performances: theatrical and musical. The director is trying to estimate the annual cost of the theatrical performances. Several costs are attributable directly to one type of performance or the other. For the theatrical performances, equipment rental costs $22,500, advertising costs $19,000, and program printing costs $17,640. The same personnel including performers, staff, and crew work on both the theatrical and musical performances. The total cost of salaries and wages for personnel was $753,165. In total, personnel spent 4,000 person hours on the theatrical performances and 6,000 person hours on the musical performances. Rent and utilities cost $166,840 in total. There were 140 theatrical performances and 77 musical performances. Calculate the total annual cost of the theatrical performances. Use person hours as the cost base for allocating personnel salaries and wages, and number of performances as the cost base for allocating rent and utilities.

Do the reading assignment for the next class (Chapter 5 up to p. 176, Appendices 5-A and 5-B, Excel & Calculator Tips 2 handout, and New York State Lottery handout) and then answer the following reading comprehension questions:

a)The director is considering reupholstering the theaters seats. She says that since the reupholstered seats would last 10 years and would yield an additional $10,000 in revenues per year, she should be willing to pay up to $100,000 for the renovation. Does her argument make sense? Why or why not? (You do not need to do any calculations.)

b)The director has a choice of investing the theaters funds at an annual interest rate of 4% compounded semiannually or quarterly. Which should she choose, and why?

Dearborn Center for the Performing Arts
Variance Report
FY 2015
	Budget	Actual	Variance	U/F
Earned revenue
Ticket sales	825,000	863,850
Contributed revenue
Foundations	200,500	192,852
Corporations	160,000	173,966
Individuals	225,000	209,475
Total revenue	1,410,500	1,440,143
Expenses
Performer fees	304,500	317,890
Staff salaries	279,000	283,275
Crew salaries	158,000	152,000
Rent	122,500	122,500
Utilities	42,250	44,340
Equipment rental	50,000	51,233
Advertising	37,750	32,862
Program printing	21,375	27,342
Total expenses	1,015,375	1,031,442
Profit/(Loss)	395,125	408,701
Variance Analysis
	Volume	x Quantity	x Price	= Total
Original Budget					Volume Variance
Flex Budget					Quantity Variance
VQA Budget					Price Variance
Actual Costs					Total Variance
(2a) Write a sentence explaining the quantity variance without using financial jargon:
(2b) Which variance most contributed to the unfavorable total variance? How do you know?:
(2c) Were any of the variances favorable? If yes, explain why the variance(s) was/were favorable: