Governmental Budgeting and Accounting
This mini-case covers a number of the major concepts that wehave covered in class this semester. Specifically, the examrequires you to perform a break-even analysis with a weightedaverage contribution margin, prepare a quarterly operating budget,and consider a capital investment using the net present valuetechnique. The answers are cumulative. You cannot skip anysection and do the next.
You may make whatever assumptions you think are necessary toanswer any question. If needed, be sure to state every assumptionexplicitly. Turn in your answers in this Word document along withthe supporting calculations in Excel spreadsheets.
All of the information that you need to answer a given questionis provided in the text that immediately precedes the question andthe answers to the questions that precede it. Read the entireproblem set before you begin. Make sure you understand thequestions before answering them!
Santa Fe Museum of Southwestern Art
The Santa Fe Museum of Southwestern Art (SFMSA) presentsrotating exhibits of the works of artists and artisans from theSouthwestern United States. Historically, the museum has derivedits support from three sources: grants, annual memberships, andvisitor revenues. For the 2017 fiscal year, SFMSA knows that itwill receive $495,000 in grants from various sources. It alsoexpects 1,375 people to be supporting members of the museum. Onaverage, supporting members give SFMSA $105.75 per year. You canthink of the support from grants and memberships as Fixed Revenues.The museum expects the following mix of visitors during 2017, eachpaying the amount shown in the right column of the schedule.
Type of Visitor
Percent of Total
Price
Regular
60%
$6.00
Group
18%
$3.00
Senior Citizen
12%
$2.00
Student
10%
$1.00
SFMSA has $965,000 of fixed expenses each year. In addition, themuseum spends an average of $0.62 per visitor for handouts thatdescribe the exhibits on display. SFMSA estimates that it hasvariable electric costs of $0.10 per visitor. Plus, the museumoffers each visitor the option of receiving an audio recordingwhich describes the featured exhibit of the month. Visitors areallowed to keep the recording as a memento of their visit.Historically, these recordings have cost the museum $1.30 per copyto produce and replicate. On average, 25% of the people visitingthe museum have taken advantage of the free audio recordingoffer.
Problem 1. The Directorof the museum has asked you to tell her the minimum number of totalvisitors that must come to the museum each year in order for SFMSAto break even. Using the information given above, what is SFMSAâsbreak-even visitor volume? (40 points)
Because of Santa Feâs location in themountains of New Mexico, the museum tends to have a seasonalpattern to its visitor flow with proportionally more peoplevisiting SFMSA in the summer than in the winter. In addition,revenue from grants and memberships tend to flow into the museumunevenly throughout the year. The seasonal flow of visitor, grantsand membership revenues is distributed throughout the year asfollows:
P11.1021 Summer Session - Replacement Assignment Number5
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%
Weighted Average Price per ticket = (Weight of Regularvisitor in total tickets sold x Price of ticket) + (Weight of Groupvisitors in total tickets sold x Price of ticket) + (Weight ofSenior Citizen visitor in total tickets sold x Price of ticket) +(Weight of Student visitor in total tickets sold x Price ofticket)
=> (0.6 x $6) + (0.18 x $3) + (0.12 x $2) + (0.10 x$1) = $4.48 per ticket
Variable cost per visitor = Cost of handouts + variableelectric costs + (Cost of audio recording x 25%)
=> $0.62 + $0.10 + ($1.30 x 25%) =$1.045
Contribution per ticket = Selling Price â Variable cost=> $4.48 - $1.045 = $3.435
Total Fixed Cost = $965,000
Fixed Revenues = Grant + Membership Revenue => $495,000 +($105.75 x 1,375) = $640,406.25
Remaining Fixed cost = $965,000 - $640,406.25 =$324,593.75
Break-even point = Fixed cost / Contribution perticket
=> $324,593.75 / $3.435 = 94,495.99 or94,496
So, total 94,496 tickets with stated percentage ofvisitor type are required to break-even.
Fixed expenses are distributed evenly throughout the year i.e.25% per quarter. The museumâs marketing director forecasts that82,000 people will visit the museum during fiscal year 2017.
SFMSAâs Director of Marketing has convinced the ExecutiveDirector that a museum shop can be operated profitably in a smallspace just off the main entrance. She agrees and the shop isscheduled to open on April 1, 2017, the first day of the secondquarter. The Marketing Director estimates that 5% of the people whovisit the museum will make purchases from the shop. Based on hisexperience, he expects the average purchase to be $50. SFMSAâsBusiness Manager estimates that the cost-of-goods-sold will be 75%of the museum-shopâs sales revenue. The shop will be staffed byvolunteers at no cost to SFMSA.
Problem 2. Using theinformation above, prepare a properly formatted operating budgetfor SFMSA for each of the four quarters in fiscal year 2017 andsummarize the budget for the full year. (45 points)
SFMSA has just been approached by the Curator of SpecialExhibits at the Smithsonian Museum. The Smithsonian has offered tolend SFMSA a rare collection of nineteenth century Navajo crafts.The collection would remain at the museum for a five-year periodafter which it would be returned to the Navajo nation. To house theexhibit, SFMSA will have to upgrade its environmental and securitysystems at a one-time cost of $300,000. This is the only cashoutflow associated with the decision.
Since this may be the last time that this collection will beexhibited in its entirety, the Executive Director is enthusiasticabout the impact that it will have on visitor volume and thereputation of the museum. The Marketing Director forecasts that 900incremental visitors are likely to be drawn to the museum eachmonth that the exhibit is at SFMSA.
The director wants you to tell her if the exhibit is financiallyself-sufficient or if she will need to get a grant to support it.You know that SFMSAâs cost of capital is 8%. You also know themarginal contribution generated by each incremental visitor to themuseum from your work on the break-even analysis. Do not count onany gift shop purchases from the incremental visitors.
Problem 3. What do youtell her? Can SFMSA afford to show the exhibit based solely on themarginal contribution from incremental visitors? If the exhibit isnot financially self-sufficient, how large a grant will SFMSA needto get to meet the projected shortfall? Support your recommendationand present your findings in a way that the director willunderstand. (40 points)
I have answered problem 1, the answer is in bold. I need helpanswering problem 2 and 3.
Governmental Budgeting and Accounting
This mini-case covers a number of the major concepts that wehave covered in class this semester. Specifically, the examrequires you to perform a break-even analysis with a weightedaverage contribution margin, prepare a quarterly operating budget,and consider a capital investment using the net present valuetechnique. The answers are cumulative. You cannot skip anysection and do the next.
You may make whatever assumptions you think are necessary toanswer any question. If needed, be sure to state every assumptionexplicitly. Turn in your answers in this Word document along withthe supporting calculations in Excel spreadsheets.
All of the information that you need to answer a given questionis provided in the text that immediately precedes the question andthe answers to the questions that precede it. Read the entireproblem set before you begin. Make sure you understand thequestions before answering them!
Santa Fe Museum of Southwestern Art
The Santa Fe Museum of Southwestern Art (SFMSA) presentsrotating exhibits of the works of artists and artisans from theSouthwestern United States. Historically, the museum has derivedits support from three sources: grants, annual memberships, andvisitor revenues. For the 2017 fiscal year, SFMSA knows that itwill receive $495,000 in grants from various sources. It alsoexpects 1,375 people to be supporting members of the museum. Onaverage, supporting members give SFMSA $105.75 per year. You canthink of the support from grants and memberships as Fixed Revenues.The museum expects the following mix of visitors during 2017, eachpaying the amount shown in the right column of the schedule.
Type of Visitor | Percent of Total | Price |
Regular | 60% | $6.00 |
Group | 18% | $3.00 |
Senior Citizen | 12% | $2.00 |
Student | 10% | $1.00 |
SFMSA has $965,000 of fixed expenses each year. In addition, themuseum spends an average of $0.62 per visitor for handouts thatdescribe the exhibits on display. SFMSA estimates that it hasvariable electric costs of $0.10 per visitor. Plus, the museumoffers each visitor the option of receiving an audio recordingwhich describes the featured exhibit of the month. Visitors areallowed to keep the recording as a memento of their visit.Historically, these recordings have cost the museum $1.30 per copyto produce and replicate. On average, 25% of the people visitingthe museum have taken advantage of the free audio recordingoffer.
Problem 1. The Directorof the museum has asked you to tell her the minimum number of totalvisitors that must come to the museum each year in order for SFMSAto break even. Using the information given above, what is SFMSAâsbreak-even visitor volume? (40 points)
Because of Santa Feâs location in themountains of New Mexico, the museum tends to have a seasonalpattern to its visitor flow with proportionally more peoplevisiting SFMSA in the summer than in the winter. In addition,revenue from grants and memberships tend to flow into the museumunevenly throughout the year. The seasonal flow of visitor, grantsand membership revenues is distributed throughout the year asfollows:
P11.1021 Summer Session - Replacement Assignment Number5
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% |
Weighted Average Price per ticket = (Weight of Regularvisitor in total tickets sold x Price of ticket) + (Weight of Groupvisitors in total tickets sold x Price of ticket) + (Weight ofSenior Citizen visitor in total tickets sold x Price of ticket) +(Weight of Student visitor in total tickets sold x Price ofticket)
=> (0.6 x $6) + (0.18 x $3) + (0.12 x $2) + (0.10 x$1) = $4.48 per ticket
Variable cost per visitor = Cost of handouts + variableelectric costs + (Cost of audio recording x 25%)
=> $0.62 + $0.10 + ($1.30 x 25%) =$1.045
Contribution per ticket = Selling Price â Variable cost=> $4.48 - $1.045 = $3.435
Total Fixed Cost = $965,000
Fixed Revenues = Grant + Membership Revenue => $495,000 +($105.75 x 1,375) = $640,406.25
Remaining Fixed cost = $965,000 - $640,406.25 =$324,593.75
Break-even point = Fixed cost / Contribution perticket
=> $324,593.75 / $3.435 = 94,495.99 or94,496
So, total 94,496 tickets with stated percentage ofvisitor type are required to break-even.
Fixed expenses are distributed evenly throughout the year i.e.25% per quarter. The museumâs marketing director forecasts that82,000 people will visit the museum during fiscal year 2017.
SFMSAâs Director of Marketing has convinced the ExecutiveDirector that a museum shop can be operated profitably in a smallspace just off the main entrance. She agrees and the shop isscheduled to open on April 1, 2017, the first day of the secondquarter. The Marketing Director estimates that 5% of the people whovisit the museum will make purchases from the shop. Based on hisexperience, he expects the average purchase to be $50. SFMSAâsBusiness Manager estimates that the cost-of-goods-sold will be 75%of the museum-shopâs sales revenue. The shop will be staffed byvolunteers at no cost to SFMSA.
Problem 2. Using theinformation above, prepare a properly formatted operating budgetfor SFMSA for each of the four quarters in fiscal year 2017 andsummarize the budget for the full year. (45 points)
SFMSA has just been approached by the Curator of SpecialExhibits at the Smithsonian Museum. The Smithsonian has offered tolend SFMSA a rare collection of nineteenth century Navajo crafts.The collection would remain at the museum for a five-year periodafter which it would be returned to the Navajo nation. To house theexhibit, SFMSA will have to upgrade its environmental and securitysystems at a one-time cost of $300,000. This is the only cashoutflow associated with the decision.
Since this may be the last time that this collection will beexhibited in its entirety, the Executive Director is enthusiasticabout the impact that it will have on visitor volume and thereputation of the museum. The Marketing Director forecasts that 900incremental visitors are likely to be drawn to the museum eachmonth that the exhibit is at SFMSA.
The director wants you to tell her if the exhibit is financiallyself-sufficient or if she will need to get a grant to support it.You know that SFMSAâs cost of capital is 8%. You also know themarginal contribution generated by each incremental visitor to themuseum from your work on the break-even analysis. Do not count onany gift shop purchases from the incremental visitors.
Problem 3. What do youtell her? Can SFMSA afford to show the exhibit based solely on themarginal contribution from incremental visitors? If the exhibit isnot financially self-sufficient, how large a grant will SFMSA needto get to meet the projected shortfall? Support your recommendationand present your findings in a way that the director willunderstand. (40 points)
I have answered problem 1, the answer is in bold. I need helpanswering problem 2 and 3.
