Instructions - President Theater 38518DL Questions will be taken in the Student Lounge Discussion Group 1Enter...

Transcribed Image Text:

Instructions - President Theater 38518DL Questions will be taken in the Student Lounge Discussion Group 1Enter Your Name Right Here, in Cell B3 - NOW This will put your name on all the pages S22 Save the File as Yourfirstname_YourLastName_38518DL_Presidents Theater_Budget Save the File Often. It is also a good idea to make a backup copy under another name occasionally, then save it back under the original. There are three Sheets in this Excel Workbook. Move between them by clicking on the Tabs at the Bottom Only Only Only Enter in Yellow Colored Cells, Like B3 If you accidentally erase a cell with a formula, it is gone. Try to Undo with the Left Arrow. If that does not work, close the file without saving and reopen it to the last time that you saved. Only Only Only Enter in Yellow Colored Cells, Like B3 Case Description You are the Owner of the President Theater. It is also your full time job, for which you draw a salary to live on. Two years ago, the President Theater had a small loss. Last year, facing a much larger loss, you went to the Bank and borrowed an additional $100,000. The Bank, however, has said that the current amount is the maximum that your business can borrow. The business now needs to put together a Business Plan or Budget for the coming year. If you run out of cash, you will go out of business, lose the theater, and your job. Good Luck. Prepare a completing the Yellow Areas on "Budget Questions and Answers" and on "Budget Financial Forecast" There are three parts to this assignment 1) Explain some of the Computations IN YOUR OWN WORDS. 2) First Cut Budget - Based on what you think will happen to your costs and 3) The Second Cut Budget are changes that you can make to stay in business and keep the Theater open for another year. 4) What You might Change in a Third Cut Budget Success will be a Positive Cash Balance at the End of the Final Budget. If you have Cash Left (and Explained Why), you win. Due Thursday April 21 The Key Will be Released in Blackboard Class April 23 after April = Your Grade is Zero. 25 Points, last three questions 1 point extra credit potential for Outstanding Answers Project Submission Requirements Before the Due Date Submit to Blackboard. Max President Theater Step Amount Description Enter Your Name Right Here, in Cell B3 - NOW Point Value Use ONLY the Space provided for All-Do not modify font or row height. Cut answer if #aw#-ONLY enter in yellow areas. Do One Step at a time. 1.15 (50,202) Describe in Your Own Words Why the President had a GREATER Net Loss last year than the previous year 1.1A 1.1A 1.2 1.2A 1.2A 4,719 Use WORDS to describe the COMPUTATION of Budget Financial Forecast Final Budget Ending Cash Balance by looking in cell H57. 2.0 The following Cost Changes are forecast for Next Year. Enter them in the Yellow Cells on First Cut % Change and First Cut Budget 2.1 The cost paid to movie makers to show their movies is expected to increase by 7.0% per Ticket Sold next year. 2.2 The Average Cost paid to the makers of the Junk Food you sell is expected to increase by 6.0% next year. 2.3 You would like to give your hourly employees a raise of 4.0% next year 2.4 You would like to give yourself a raise of 5.0% next year. You have been living on the same salary for two years, and it is tight. 2.5 You need to make a capital investment to Fix the Leaky roof. This will cost $10,000 (enter as a Negative Number) 2.69 4,719 Not OK Check if Red OK in Cell C16: You have successfully completed step 2.5 - Save and Print Budget Financial Forecast 2.7 Look carefully at the model. Explain what the number in 2.6 on the financial forecast MEANS for President's Theater. Answer Below on one line. 2.7A 3.0 Second Cut Changes and Budget for Next Year - Your Plan - Pick the Best Options to Survive - Your Options Are: 3.11 Raise Ticket Prices by 5.0%. This will cause a -5.0% change in the number of tickets sold per movie, as some people will go elsewhere OR 3.12 Offer 10% more movies shown at -2.0% lower prices with a resulting increase in 15% more tickets per movie soldOR 3.13 Do Not Change Ticket Prices Pick and Enter The Best Option for Second Cut Ending Cash Balance - Then Your Next Options are: 3.21 Raise Junk Food Prices by 10.0%, which will result in -2.0% of the # of Ticket holders Buying Junk Food OR 3.22 Lower Junk Food Prices by -10.0%, which will result in +2.0% of the # of Ticket holders Buying Junk Food OR Do Nothing - Pick the best, then 3.31 Purchase a New Ticket System for -$15,000 (negative), which will allow you to decrease the Number of Employee Hours per Show by -10.0% OR 3.32 Do Not Purchase the New System and keep the same number of hours employees work per show Pick the best, then 3.41 Continue to Pay Employees the Same Hourly Rate as Last Year by Entering -4.0% in the Second Cut Change -or not 3.51 Continue to Pay Yourself the Same Salary as Last Year by Entering -5.0% in the Second Cut Change -or not 3.65 4,719 OK Check if Red OK in Cell C29: You positive cash left at the end of next year's plan - Save and Print Budget Financial Forecast 3.7 Review the Printed Scoond Cut Budget. In your own words, briefly explain your plan to your Banker in your own words 3.7A 3.7A 4.0 List three Changes that you might Explore in your "Third Cut Budget" below. These are your ideas that were not included in the previous lines but things you might try to plan into your budget to improve the next year. Be brief but as complete as you can - Only One Plan Change per Line 4.1A 4.2A 4.3A Total 25 President Theater First Cut Enter Your Name Right Here, in Cell B3 - NO Two Years Ago Actual Last Year Actual % Change First Cut Next Year Budget Second Cut % Change Second Cut Next Year Budget Revenue Development # Number of Movies Shown X Average Number of Tickets/Movie = Number of Tickets Sold 500 98.0 49,000 500 97.0 48,500 500 0.0% 97.0 0.0% 500 97.0 X Average Price per Ticket $ Movie Ticket Sales -Total $ 6.65 $ 325,622 $ 6.66 323,222 $ $ 48,500 6.66 323,222 0.0% $ $ 48,500 6.66 323,222 B4 Number of Tickets Sold Percent Tickets who Buy Junk Food = # of Ticket holders Buying Junk Food 49,000 70.0% 34,300 48,500 48,500 48,500 71.0% 71.0% 0.0% 71.0% 34,435 34,435 34,435 X Average Price per Junk Food $ = Junk Food Revenue - Total $ 3.70 $ 126,947 $ 3.80 130,876 $ 3.80 130,876 0.0% $ $ 3.80 130.876 Cost Development B4 Number of Tickets Sold 49,000 X Avg. Cost/Ticket to Movie Producers $ = Total Cost for Movie Tickets $ 4.35 $ 213,000 $ 48,500 4.76 231,000 48,500 0.0% $ 4.76 48,500 $ 4.76 $ 231,000 $ 231,000 B4 # of Ticket holders Buying Junk Food 34,300 X Avg. Cost per Junk Food Sold = Total Cost for Junk Food $ 1.46 $ 50,100 $ 34,435 1.75 60,300 0.0% $ $ 34,435 1.75 60,300 34,435 $ $ 1.75 60,300 B4 Number of Movies Shown X Number of Employee Hours per Show = Total Number of Employee Hours 500 24.0 12.000 X Rate of Pay per Cost per Hour = Total Cost of Employees $ 8.00 $ 96,000 $ 500 24.0 12,000 8.75 105,000 500 24.0 12,000 0.0% $ 8.75 $ 105,000 500 0.0% 24.0 12,000 0.0% $ $ 8.75 105,000 # # Your Salary - You Live on This $ 45,000 $ 45,000 0.0% $ 45,000 0.0% $ 45,000 Balance of Loan $ X# Interest Rate = Total Interest Expense $ 54,000 $ 600,000 $ 700,000 9.0% 9.0% 63,000 $ $ 700,000 9.0% 63,000 $ 700,000 9.0% $ 63,000 Two Years Financial Summary Ago Actual Last Year Actual First Cut % Change Movie Ticket Sales -Total $ 325,622 $ 323,222 0.0% $ First Cut Next Year Budget 323,222 Second Cut % Second Cut Next Year Change Budget 0.0% $ 323,222 Junk Food Revenue - Total $ 126,947 $ 130,876 0.0% $ 130,876 0.0% $ 130,876 Total Revenue $ 452,569 $ 454,098 0.0% $ 454,098 0.0% $ 454,098 Total Cost for Movie Tickets $ 213,000 $ 231,000 Total Cost for Junk Food $ 50,100 $ 60,300 Total Cost of Employees Your Salary - You Live on This Total Interest Expense $ 54,000 $ Total Expenses $ 458,100 $ $ 96,000 $ 105,000 $ 45,000 $ 45,000 63,000 504,300 0.0% $ 231,000 0.0% $ 60,300 0.0% $ 105,000 0.0% $ 231.000 0.0% $ 60,300 0.0% $ 105,000 0.0% $ 45,000 0.0% $ 63,000 0.0% $ 504,300 0.0% $ 45,000 0.0% $ 63.000 0.0% $ 504,300 Total Profit (Loss) $ (5,531) $ (50,202) $ (50,202) $ (50,202) Capital Investment Projects (Enter as Negative Numbers) 1) Fix Roof $ 2) New Ticket System to Cut Employees Ending Cash Balance $ 5,123 $ 54,921 $ 4,719 4,719 Instructions - President Theater 38518DL Questions will be taken in the Student Lounge Discussion Group 1Enter Your Name Right Here, in Cell B3 - NOW This will put your name on all the pages S22 Save the File as Yourfirstname_YourLastName_38518DL_Presidents Theater_Budget Save the File Often. It is also a good idea to make a backup copy under another name occasionally, then save it back under the original. There are three Sheets in this Excel Workbook. Move between them by clicking on the Tabs at the Bottom Only Only Only Enter in Yellow Colored Cells, Like B3 If you accidentally erase a cell with a formula, it is gone. Try to Undo with the Left Arrow. If that does not work, close the file without saving and reopen it to the last time that you saved. Only Only Only Enter in Yellow Colored Cells, Like B3 Case Description You are the Owner of the President Theater. It is also your full time job, for which you draw a salary to live on. Two years ago, the President Theater had a small loss. Last year, facing a much larger loss, you went to the Bank and borrowed an additional $100,000. The Bank, however, has said that the current amount is the maximum that your business can borrow. The business now needs to put together a Business Plan or Budget for the coming year. If you run out of cash, you will go out of business, lose the theater, and your job. Good Luck. Prepare a completing the Yellow Areas on "Budget Questions and Answers" and on "Budget Financial Forecast" There are three parts to this assignment 1) Explain some of the Computations IN YOUR OWN WORDS. 2) First Cut Budget - Based on what you think will happen to your costs and 3) The Second Cut Budget are changes that you can make to stay in business and keep the Theater open for another year. 4) What You might Change in a Third Cut Budget Success will be a Positive Cash Balance at the End of the Final Budget. If you have Cash Left (and Explained Why), you win. Due Thursday April 21 The Key Will be Released in Blackboard Class April 23 after April = Your Grade is Zero. 25 Points, last three questions 1 point extra credit potential for Outstanding Answers Project Submission Requirements Before the Due Date Submit to Blackboard. Max President Theater Step Amount Description Enter Your Name Right Here, in Cell B3 - NOW Point Value Use ONLY the Space provided for All-Do not modify font or row height. Cut answer if #aw#-ONLY enter in yellow areas. Do One Step at a time. 1.15 (50,202) Describe in Your Own Words Why the President had a GREATER Net Loss last year than the previous year 1.1A 1.1A 1.2 1.2A 1.2A 4,719 Use WORDS to describe the COMPUTATION of Budget Financial Forecast Final Budget Ending Cash Balance by looking in cell H57. 2.0 The following Cost Changes are forecast for Next Year. Enter them in the Yellow Cells on First Cut % Change and First Cut Budget 2.1 The cost paid to movie makers to show their movies is expected to increase by 7.0% per Ticket Sold next year. 2.2 The Average Cost paid to the makers of the Junk Food you sell is expected to increase by 6.0% next year. 2.3 You would like to give your hourly employees a raise of 4.0% next year 2.4 You would like to give yourself a raise of 5.0% next year. You have been living on the same salary for two years, and it is tight. 2.5 You need to make a capital investment to Fix the Leaky roof. This will cost $10,000 (enter as a Negative Number) 2.69 4,719 Not OK Check if Red OK in Cell C16: You have successfully completed step 2.5 - Save and Print Budget Financial Forecast 2.7 Look carefully at the model. Explain what the number in 2.6 on the financial forecast MEANS for President's Theater. Answer Below on one line. 2.7A 3.0 Second Cut Changes and Budget for Next Year - Your Plan - Pick the Best Options to Survive - Your Options Are: 3.11 Raise Ticket Prices by 5.0%. This will cause a -5.0% change in the number of tickets sold per movie, as some people will go elsewhere OR 3.12 Offer 10% more movies shown at -2.0% lower prices with a resulting increase in 15% more tickets per movie soldOR 3.13 Do Not Change Ticket Prices Pick and Enter The Best Option for Second Cut Ending Cash Balance - Then Your Next Options are: 3.21 Raise Junk Food Prices by 10.0%, which will result in -2.0% of the # of Ticket holders Buying Junk Food OR 3.22 Lower Junk Food Prices by -10.0%, which will result in +2.0% of the # of Ticket holders Buying Junk Food OR Do Nothing - Pick the best, then 3.31 Purchase a New Ticket System for -$15,000 (negative), which will allow you to decrease the Number of Employee Hours per Show by -10.0% OR 3.32 Do Not Purchase the New System and keep the same number of hours employees work per show Pick the best, then 3.41 Continue to Pay Employees the Same Hourly Rate as Last Year by Entering -4.0% in the Second Cut Change -or not 3.51 Continue to Pay Yourself the Same Salary as Last Year by Entering -5.0% in the Second Cut Change -or not 3.65 4,719 OK Check if Red OK in Cell C29: You positive cash left at the end of next year's plan - Save and Print Budget Financial Forecast 3.7 Review the Printed Scoond Cut Budget. In your own words, briefly explain your plan to your Banker in your own words 3.7A 3.7A 4.0 List three Changes that you might Explore in your "Third Cut Budget" below. These are your ideas that were not included in the previous lines but things you might try to plan into your budget to improve the next year. Be brief but as complete as you can - Only One Plan Change per Line 4.1A 4.2A 4.3A Total 25 President Theater First Cut Enter Your Name Right Here, in Cell B3 - NO Two Years Ago Actual Last Year Actual % Change First Cut Next Year Budget Second Cut % Change Second Cut Next Year Budget Revenue Development # Number of Movies Shown X Average Number of Tickets/Movie = Number of Tickets Sold 500 98.0 49,000 500 97.0 48,500 500 0.0% 97.0 0.0% 500 97.0 X Average Price per Ticket $ Movie Ticket Sales -Total $ 6.65 $ 325,622 $ 6.66 323,222 $ $ 48,500 6.66 323,222 0.0% $ $ 48,500 6.66 323,222 B4 Number of Tickets Sold Percent Tickets who Buy Junk Food = # of Ticket holders Buying Junk Food 49,000 70.0% 34,300 48,500 48,500 48,500 71.0% 71.0% 0.0% 71.0% 34,435 34,435 34,435 X Average Price per Junk Food $ = Junk Food Revenue - Total $ 3.70 $ 126,947 $ 3.80 130,876 $ 3.80 130,876 0.0% $ $ 3.80 130.876 Cost Development B4 Number of Tickets Sold 49,000 X Avg. Cost/Ticket to Movie Producers $ = Total Cost for Movie Tickets $ 4.35 $ 213,000 $ 48,500 4.76 231,000 48,500 0.0% $ 4.76 48,500 $ 4.76 $ 231,000 $ 231,000 B4 # of Ticket holders Buying Junk Food 34,300 X Avg. Cost per Junk Food Sold = Total Cost for Junk Food $ 1.46 $ 50,100 $ 34,435 1.75 60,300 0.0% $ $ 34,435 1.75 60,300 34,435 $ $ 1.75 60,300 B4 Number of Movies Shown X Number of Employee Hours per Show = Total Number of Employee Hours 500 24.0 12.000 X Rate of Pay per Cost per Hour = Total Cost of Employees $ 8.00 $ 96,000 $ 500 24.0 12,000 8.75 105,000 500 24.0 12,000 0.0% $ 8.75 $ 105,000 500 0.0% 24.0 12,000 0.0% $ $ 8.75 105,000 # # Your Salary - You Live on This $ 45,000 $ 45,000 0.0% $ 45,000 0.0% $ 45,000 Balance of Loan $ X# Interest Rate = Total Interest Expense $ 54,000 $ 600,000 $ 700,000 9.0% 9.0% 63,000 $ $ 700,000 9.0% 63,000 $ 700,000 9.0% $ 63,000 Two Years Financial Summary Ago Actual Last Year Actual First Cut % Change Movie Ticket Sales -Total $ 325,622 $ 323,222 0.0% $ First Cut Next Year Budget 323,222 Second Cut % Second Cut Next Year Change Budget 0.0% $ 323,222 Junk Food Revenue - Total $ 126,947 $ 130,876 0.0% $ 130,876 0.0% $ 130,876 Total Revenue $ 452,569 $ 454,098 0.0% $ 454,098 0.0% $ 454,098 Total Cost for Movie Tickets $ 213,000 $ 231,000 Total Cost for Junk Food $ 50,100 $ 60,300 Total Cost of Employees Your Salary - You Live on This Total Interest Expense $ 54,000 $ Total Expenses $ 458,100 $ $ 96,000 $ 105,000 $ 45,000 $ 45,000 63,000 504,300 0.0% $ 231,000 0.0% $ 60,300 0.0% $ 105,000 0.0% $ 231.000 0.0% $ 60,300 0.0% $ 105,000 0.0% $ 45,000 0.0% $ 63,000 0.0% $ 504,300 0.0% $ 45,000 0.0% $ 63.000 0.0% $ 504,300 Total Profit (Loss) $ (5,531) $ (50,202) $ (50,202) $ (50,202) Capital Investment Projects (Enter as Negative Numbers) 1) Fix Roof $ 2) New Ticket System to Cut Employees Ending Cash Balance $ 5,123 $ 54,921 $ 4,719 4,719

Answer rating: 100% (QA)

Here are the steps I would take to improve the finances 1 Increase ticket prices gradually over the ... View the full answer