the Santa Fe Museum of Southwestern Art (SFMSA) presents rotating exhibits of the works of artists and artisans from the Southwestern United States. Historically, the museum has derived its support from three sources: grants, annual memberships, and visitor revenues. For the 2023 fiscal year, SFMSA knows that it will receive $450,000 in grants from various sources. It also expects 1,500 people to be supporting members of the museum. On average, supporting members give SFMSA $100 per year. You can think of the support from grants and memberships as Fixed Revenues. The museum expects the following mix of visitors during 2023, each paying the amount shown in the right column of the schedule.

Type of Visitor	Percent of Total	Price
Regular	64%	$6.00
Group	14%	$3.00
Senior Citizen	10%	$2.00
Student	12%	$1.00

SFMSA has $925,000 of fixed expenses each year. In addition, the museum spends an average of $0.64 per visitor for handouts that describe the exhibits on display. SFMSA estimates that it has variable utility costs (electricity and water) of $0.10 per visitor. Plus, the museum offers each visitor the option of receiving an activity pack, which supports more in-depth interaction with the featured exhibit of the month. Visitors are allowed to keep the activity pack as a memento of their visit. Historically, these activity packs have cost the museum $1.40 per copy to produce. On average, 25% of the people visiting the museum have taken advantage of the free activity pack offer.

Problem 1. The Director of the museum has asked you to tell her the minimum number of total visitors that must come to the museum each year in order for SFMSA to break even. Using the information given above, what is SFMSA's break-even visitor volume? (35 points)

Because of Santa Fe's location in the mountains of New Mexico, the museum tends to have a seasonal pattern to its visitor flow with proportionally more people visiting SFMSA in the summer than in the winter. In addition, revenue from grants and memberships tends to be earned by the museum unevenly throughout the year. The seasonal flow of visitor, membership revenues, and earned grants are distributed throughout the year as follows:

P11.1021 Summer Session - Replacement Assignment Number 5

	Quarter 1	Quarter 2	Quarter 3	Quarter 4
Visitors	20%	25%	40%	15%
Membership Revenue	40%	20%	20%	20%
Grant revenue	25%	50%	10%	15%

Fixed expenses are distributed evenly throughout the year (25% per quarter). The museum's marketing director forecasts that 85,000 people will visit the museum during fiscal year 2023.

SFMSA's Director of Marketing has convinced the Executive Director that a museum shop can be operated profitably in a small space just off the main entrance. She agrees and the shop is scheduled to open on April 1, 2023, the first day of the second quarter. The Marketing Director estimates that 7% of the people who visit the museum will make purchases from the shop. Based on his experience, he expects the average purchase to be $50. SFMSA's Business Manager estimates that the cost-of-goods-sold (the expenses associated with the gift shop revenue) will be 80% of the museum-shop's sales revenue.

Problem 2. Using the information above, prepare a properly formatted operating budget for SFMSA for each of the four quarters in fiscal year 2023 and include a column to summarize the budget for the full year. (35 points)

SFMSA has just been approached by the Curator of Special Exhibits at the Smithsonian Museum. The Smithsonian has offered to lend SFMSA a rare collection of nineteenth century Navajo crafts. The collection would remain at the museum for a five-year period after which it would be returned. To house the exhibit, SFMSA will have to upgrade its environmental and security systems at a one-time, up-front cost of $300,000. This is the only cash outflow associated with the decision.

Since this may be the last time that this collection will be exhibited in its entirety, the Executive Director is enthusiastic about the impact that it will have on visitor volume and the reputation of the museum. The Marketing Director forecasts that 950 incremental visitors (visitors that otherwise would not have visited) are likely to be drawn to the museum each month that the exhibit is at SFMSA.

The director wants you to tell her if the exhibit is financially self-sufficient taking into account the time-value-of-money or if she will need to get a grant to support it. You know that SFMSA's cost of capital is 5%. Assume the contribution margin, or net income, generated by each incremental visitor to the museum is $4.50.

Problem 3. What do you tell her based on the net present value (NPV) and internal rate of return (IRR) of the proposed collection? Can SFMSA afford to show the exhibit based solely on the marginal contribution from incremental visitors? If the exhibit is not financially self-sufficient, how large a grant will SFMSA need to get now to meet the projected shortfall? (30 points)

can you help with this please. I will specifically need detailed help with problem 2 as I will be making it into an excel sheet? Thank you for the help