This assignment provides a good example of using Excel to develop a cash budget.An Excel spreadsheet to calculate answers to questions related to Salt Lake Light Opera in Problem 7-43 on pages 303?305 of the text is needed

CHAPTER If C INTRODUCTION TO BUDGFTE AND PREPARENG THE MA Both displays require three components per display. Therefore. the total cost of components for 50" displays is 3240 and for 42" displays is $l65. HDLD uses a 6-month continuous budget that is revised monthly. Sales forecasts reect the expectation that unit sales of 42" displays will be 25% higher than unit sales of 50" displays. Sales forecasts for the next 8 months are as follows: 50" Displays 42" Displays October 3,200 units 4,000 units November 2.400 3.000 December 5,600 T000 January 3.200 4.000 February 3.200 4000 March 2.400 3000 April 2.400 3.000 May 2,800 3.500 Treat each of the following events in succession. I. Use spreadsheet software to prepare a table of budgeting information and an operating expense budget for HDLD for OctoberMarch. Prepare a spreadsheet that can be revised easily for succeeding months in parts 2 and 3. 2. October's actual sales were 2.300 50" displays and 3.600 42"displays. This outcome has caused HDLD to revise its sales forecasts downward by 10%. Revise the operating expense budget for NovemberApril. 3. At the end of November. HDLD decides that the proportion of 50" to 42" displays is changing. Unit sales of 42" displays are expected to be 50% higher than unit sales of 50" displays sales. Expected sales of 50" displays are unchanged from number 2. Revise the operating expense hud~ get for DecemberMay. CASES L43 Comprehensive Cash Budgeting Christine Morrison, treasurer of Salt Lake Light 'Dpera (SLLUJ, was preparing a loan request to the South Utah National Bank in December 20x4. The loan was necessary to meet the cash needs of the SLLO for year ZDXS. In a few short years. the SLLG had established itself as a premier opera com- pany. In addition to its regular subscription series, it started a series for new composers and offered a very popular holiday production. The holiday production was the most nancially successful of the SLLO's activities. providing a base to support innovative productions that were artistically important to the SLL'D but did not usually succeed nancially. Exhibit Salt Lake Assets Balance 5!: Cash December Accounts receivable of dollars) Supplies inventory Total current assets Plant and equipment Total assets Liabilities and Equities Bank loan Accounts payable Accrued payroll expenses Mortgage. current Total cunent liabilities Other payable: Mortgage payable, long-term Net assets' Total liabilities and equities 'lnducies $32,000 of around interest tThe "Net assets\" accountfora nonprot organization is similar to 'Smdtlnldm' equity" for a corporation. 'ARTZ 0 ACCOUNTING 7-1 5 - Light Opera ate-merits for the ad Detember 31 ads of dollars) FOR PLANNiNG AND CONTROL 20H 53.303 1.1111 1.202 $5.546 2DX3 54.1151] 1 .412 1 .361 $5. 833 Ti diet sales Contributions Grants and other revenues Total revenues Expenses " Production $4.071 $4.305 Uperaons 271 332 Public relations and community development Total expenses Excess of revenues over expenses 1.032 $5,424 5 122 1 .421 $6. 553 5 225 'Ettpenses indede depreciation 015355. 3310. and 5410 and general and administrative ewenses at $1.549. 51.635. and 52.142 in the years 20112. 2013. and 211341 respectively. In total, the SLLCI had done well nancially, as shown in Exhibits 7-14 and 7-15. Its protable operations had enabled it to build its own building and generally,I acquire a large number of assets. It had at least broken even event year since its incorporation, and management anticipates continued protable operations. The Cmporate Community for the Arts in Salt Lake and several private founda- tions had made many grants to the SL113, and such grants are expected to continue. Most recently. the largest bank in town had agreed to sponsor the production of a new opera by a local composer The SLLO's director of development. Harlan Wayne. expected such corporate sponsorships to increase in the future. To provide facilities for the Opera's unlicipaled growth. SLLU begun work on an addition to its building 2 years ago. The new facilities are intended primarily to support the experimental offerings that were becoming more numerous. The capital expansion was to he completed in ZDXS; all that retnained was acquisition and installation of lighting. sound equipment. and other new equipment to be purchased in zoxs. SLLD had borrowed working capital from South Utah National Bank for the past several years. To qualify for the loans. the SLLO had to agree to the following: 1. Completely pay off the loan for 1 month during the course of the year. 2. Maintain cash and accounts receivable balances equal to (or greater than) 120% of the loan. 3. Maintain a compensating cash balance of $200,000 at all times. In the past. the SLLD has had no problem meeting these requirements. However. in 2UX4 the SLLO had been unable to reduce the loan to zero for an entire month. Although South Utah continued to extend the needed credit. the loan manager expressed concern over the situation. She asked for a quarterly cash budget to justify the nancing needed for 2025. Ms. Morrison began to assemble the data needed to prepare sttch a budget. SLLCI received revenue from three main sources: ticket sales, contributions, and grants. Ms. Morrison formed Exhibit 7-16 to calculate the accounts receivable balance for each of these sources for 20.55. She assumed that SLLG would continue its normal practices for collecting pledges and grant revenues. Tidtet Sales End of lQuarter Receivables Contributions G rants End at lCl'uarter Receivables End of lJuarter Receivables Revenues Revenues Revenues First Quarter Second Quarter Third Quarter Fourth Quarter Exhibit 7-1 6 5 852 1.584 2.51]I 1.519 32.295 3.1130 3.11111}l 3,683 5 25 363 1.2113 442 5 2'94 BEE- 1.1183 1.120 5 132 4-43 1.296 528 5 1302? 1.1 31] 1.241] 1.342 Salt Lake Light Opera Erhl'ma tied Qua rterl'y Revenues and End of Quarter Receivables for the Year Ended December 31.. 20X5 fin thousands of dollars) CHAPTER 7 0 INTRODUCTION TO BUDGETS AND PREPAF Most expenses were constant from month to month. An exception was supplies, which were purchased twice a year in December and June. In 20x5, SLLO expects to purchase $200,000 of supplies in June and $100,000 in December on terms of net, 30 days. The supplies inventory at die end of December was expected to be $600,000. Depreciation expense 0f $500,000 was planned for 2036. and other expenses were expected to run at a steady rate of 5'! 10,000 a month throughout the year, of which $700,000 was payroll costs. Salaries and wages were paid on the Monday of the rst week following die end of the month. The remaining $10,000 of other expenses were paid as incurred. The major portion of the new equipment to be installed in ZUXS was to be delivered in September; payments totaling $400,000 would be made in four equal monthly installments beginning in September. In addition, small equipment purchases are expected to run $20,000 per month throughout the year. They will he paid for on delivery. In late 20x2, SLLO had borrowed $4 million (classied as a mortgage payable) from Farmers' Life Insurance Company. The SLLO is repaying the loan over 16 years, in equal principal payments in June and December of each year. Interest at 8% annually is also paid on me unpaid balance on each of these dates. Total interest payments for 20X5, according to Ms. Monison's calculations, would be $275,000, Interest on the working capital loan from South Utah National Bank was at an annual rate of 10%. Interest is accrued quarterly but paid annually; payment for 20X4's interest would be made on January 10, 20X5, and that for 20X5's interest would be made on January 10. 20X6. Working capital loans are taken out on the rst day of the quarter that funds are needed, and they are repaid ou the last day of the quarter when extra funds are generated. SLLD has tried to keep a minimum cash balance of $200,000 at all times, even if loan requirements do not require it. 1. Compute the cash inows and outows for each quarter of 20x5. What are SLLD' s loan require- ments each quarter\"? 2. Prepare a projected income statement and balance sheet for SLLO for 20X5. 3. What nancing strategy would you recommend for SLLO? T44 Cash Budgeting for a Hospital Highline Hospital provides a wide range of healdi services in its community. Highline's board of directors has authorized the following capital expenditures: Intra-aortic balloon pump $1,400,000 Computed tomography scanner 850,000 Xray equipment 550,000 Laboratory equipment 1,200,000 Total $4,000,000 The expenditures are planned for October 1, 20X'r', and the board wishes to know the amount of borrowing, if any, necessary on that. date. Heb-coca Singer, hospital controller, has gathered the following information to be used in preparing an analysis of future cash flows. Billings, made in the month of service, for 20X? are shown next, with actual amounts for JanuaryJune and estimated amounts for JulyDecember: Month Amount Billed January $5,300,000 February 5,3oo,ooo March 5,400,000 April 5,400,000 May 5,?00000 June 6,000,000 July {estimated} 5,300,000 August (estimated) 6,200,000 September (estimated) 6,600,000 October (estimated) 6,800,000 November (estimated) 7,000,000 Deco mher (estimated) 6,600,000 NOTE: ALL VALUES FOR CALCULATION ARE IN '000S 1) Q1 Q2 Cash flow from operating activities Ticket sales $3,647 $4,684 Contributions $869 $1,251 Grants $1,159 $1,578 Total $5,675 $7,513 Supplies Payroll Other expenses Total Q3 Q4 $6,024 $2,286 $2,536 $10,846 $5,202 $1,612 $1,870 $8,684 0 2100 30 2130 200 2100 30 2330 0 2100 30 2130 700 2100 30 2830 Cash flow from investing activities Equipment Small equipment Total 0 600 600 0 600 600 300 600 900 100 600 700 Cash flow from financing activities Mortgage Interest on mortgage Bank loan interest Total 0 0 162 162 125 135 0 260 0 0 0 0 125 129.6 0 254.6 Total cash inflow Total cash outflow Net cash flow 2) Equipment Purchase Minor purchase Total $5,675 2892 $2,783 5809 400 240 $6,449 Income statement for 2005 Revenues Ticket sales Contributions Grants and other Total revenue Expenses Depreciation $6,572 $2,083 $2,404 $11,059 $500 $7,513 $10,846 $8,684 3190 3030 3784.6 $4,323 $7,816 $4,899 Payroll $700 Bank interest $162 Administration $2,142 Public relations and community development $1,900 Total expenses $5,404 Net income $5,655 Balance sheet 2005 Assets Cash Receivables Supplies inventory Equipment Acc. Depreciation Net value Total asset Liabilities and Equity Bank loan Account payables Accrued Payroll Mortgage Interest payable Net Assets Total liabilities and equity 3) $200 $6,195 $600 $6,449 $500 $5,949 $12,944 $1,620 $2,698 $700 $3,240 $275 $4,411 $12,944 The current performance of the business plays a significant role in the decision-making process. Next is to an the company. The company has potential to generate better cash flow. Choosing the appropriate financing mi an increase in the use of internally generated resources and to decrease the dependence on grants. There shou collection and increase in days of making payment to manage the working capital requirement. There should debt as it will overburden the business. Working Notes Paid $625,000 Balance $3,375,000 June interest $135,000 December balance $3,240,000 Dec Interest $129,600 Loan interest 162000 Cash balance $200,000 Ticket Sales RevenuesEnd of Quarter Receivables First Quarter $852 $2,795 Second Quarter 1584 3100 Third Quarter 2617 3407 Fourth Quarter 1519 3683 -making process. Next is to analyze the financial strength and weakness of g the appropriate financing mix for the company is essential. There should be endence on grants. There should be an improvement in the receivables tal requirement. There should not be an increase in the dependence on the Contributions Grant RevenuesEnd of Quarter ReceivablesRevenuesEnd of Quarter Receivables $75 $794 $132 $1,027 363 888 448 1130 1203 1083 1296 1240 442 1170 528 1342 NOTE: ALL VALUES FOR CALCULATION ARE IN '000S 1) Q1 Q2 Cash flow from operating activities Ticket sales $3,647 $4,684 Contributions $869 $1,251 Grants $1,159 $1,578 Total $5,675 $7,513 Supplies Payroll Other expenses Total Q3 Q4 $6,024 $2,286 $2,536 $10,846 $5,202 $1,612 $1,870 $8,684 0 2100 30 2130 200 2100 30 2330 0 2100 30 2130 700 2100 30 2830 Cash flow from investing activities Equipment Small equipment Total 0 600 600 0 600 600 300 600 900 100 600 700 Cash flow from financing activities Mortgage Interest on mortgage Bank loan interest Total 0 0 162 162 125 135 0 260 0 0 0 0 125 129.6 0 254.6 Total cash inflow Total cash outflow Net cash flow 2) Equipment Purchase Minor purchase Total $5,675 2892 $2,783 5809 400 240 $6,449 Income statement for 2005 Revenues Ticket sales Contributions Grants and other Total revenue Expenses Depreciation $6,572 $2,083 $2,404 $11,059 $500 $7,513 $10,846 $8,684 3190 3030 3784.6 $4,323 $7,816 $4,899 Payroll $700 Bank interest $162 Administration $2,142 Public relations and community development $1,900 Total expenses $5,404 Net income $5,655 Balance sheet 2005 Assets Cash Receivables Supplies inventory Equipment Acc. Depreciation Net value Total asset Liabilities and Equity Bank loan Account payables Accrued Payroll Mortgage Interest payable Net Assets Total liabilities and equity 3) $200 $6,195 $600 $6,449 $500 $5,949 $12,944 $1,620 $2,698 $700 $3,240 $275 $4,411 $12,944 The current performance of the business plays a significant role in the decision-making process. Next is to an the company. The company has potential to generate better cash flow. Choosing the appropriate financing mi an increase in the use of internally generated resources and to decrease the dependence on grants. There shou collection and increase in days of making payment to manage the working capital requirement. There should debt as it will overburden the business. Working Notes Paid $625,000 Balance $3,375,000 June interest $135,000 December balance $3,240,000 Dec Interest $129,600 Loan interest 162000 Cash balance $200,000 Ticket Sales RevenuesEnd of Quarter Receivables First Quarter $852 $2,795 Second Quarter 1584 3100 Third Quarter 2617 3407 Fourth Quarter 1519 3683 -making process. Next is to analyze the financial strength and weakness of g the appropriate financing mix for the company is essential. There should be endence on grants. There should be an improvement in the receivables tal requirement. There should not be an increase in the dependence on the Contributions Grant RevenuesEnd of Quarter ReceivablesRevenuesEnd of Quarter Receivables $75 $794 $132 $1,027 363 888 448 1130 1203 1083 1296 1240 442 1170 528 1342 NOTE: ALL VALUES FOR CALCULATION ARE IN '000S 1) Q1 Q2 Cash flow from operating activities Ticket sales $3,647 $4,684 Contributions $869 $1,251 Grants $1,159 $1,578 Total $5,675 $7,513 Supplies Payroll Other expenses Total Q3 Q4 $6,024 $2,286 $2,536 $10,846 $5,202 $1,612 $1,870 $8,684 0 2100 30 2130 200 2100 30 2330 0 2100 30 2130 700 2100 30 2830 Cash flow from investing activities Equipment Small equipment Total 0 600 600 0 600 600 300 600 900 100 600 700 Cash flow from financing activities Mortgage Interest on mortgage Bank loan interest Total 0 0 162 162 125 135 0 260 0 0 0 0 125 129.6 0 254.6 Total cash inflow Total cash outflow Net cash flow 2) Equipment Purchase Minor purchase Total $5,675 2892 $2,783 5809 400 240 $6,449 Income statement for 2005 Revenues Ticket sales Contributions Grants and other Total revenue Expenses Depreciation $6,572 $2,083 $2,404 $11,059 $500 $7,513 $10,846 $8,684 3190 3030 3784.6 $4,323 $7,816 $4,899 Payroll $700 Bank interest $162 Administration $2,142 Public relations and community development $1,900 Total expenses $5,404 Net income $5,655 Balance sheet 2005 Assets Cash Receivables Supplies inventory Equipment Acc. Depreciation Net value Total asset Liabilities and Equity Bank loan Account payables Accrued Payroll Mortgage Interest payable Net Assets Total liabilities and equity 3) $200 $6,195 $600 $6,449 $500 $5,949 $12,944 $1,620 $2,698 $700 $3,240 $275 $4,411 $12,944 The current performance of the business plays a significant role in the decision-making process. Next is to an the company. The company has potential to generate better cash flow. Choosing the appropriate financing mi an increase in the use of internally generated resources and to decrease the dependence on grants. There shou collection and increase in days of making payment to manage the working capital requirement. There should debt as it will overburden the business. Working Notes Paid $625,000 Balance $3,375,000 June interest $135,000 December balance $3,240,000 Dec Interest $129,600 Loan interest 162000 Cash balance $200,000 Ticket Sales RevenuesEnd of Quarter Receivables First Quarter $852 $2,795 Second Quarter 1584 3100 Third Quarter 2617 3407 Fourth Quarter 1519 3683 -making process. Next is to analyze the financial strength and weakness of g the appropriate financing mix for the company is essential. There should be endence on grants. There should be an improvement in the receivables tal requirement. There should not be an increase in the dependence on the Contributions Grant RevenuesEnd of Quarter ReceivablesRevenuesEnd of Quarter Receivables $75 $794 $132 $1,027 363 888 448 1130 1203 1083 1296 1240 442 1170 528 1342