Question

Unique Sinks Master Budget Unique Sinks is a family-owned manufacturing business specializing in sinks; it does not produce any of the faucets or plumbing needed for installation. The company focuses on the new housing market, which has been quite strong for over ten years. Unique Sinks' core competencies include a relatively low- cost but high-quality manufacturing process for sinks. During the last several years, demand for large houses with three to four bathrooms was strong. And homebuyers were particularly interested in sinks that were distinctive yet utilitarian. Jordan Kovacik, the company's owner, is beginning to plan for the next year. First, he reviews past performance, reads industry publications, and discusses the new housing market with several contractors. Although some economists have warned of an impending decline in the housing market, Jordan's customers believe that the boom in the new housing construction is far from over. Jordan concludes that high sales volumes are likely to continue. He also decides that no changes in vision or strategy will be needed for the next year. As in prior years, Jordan will submit budgeted financial statements to his bank as part of his annual line-of-credit loan application. He also plans to compare quarterly results to budgeted revenues and costs so that he can monitor whether operations are under control. Jordan provides the company's accountant, Ana Fernandez, with the following housing start estimates and asks her to begin developing a master budget for the next period. Quarter Housing Starts Average Sink Per House First 6,000 3.00 Second 24,000 2.90 Third 12,000 3.00 Fourth 4,000 3.10 Ana prepared the revenue budget first because she needs information from the volume of sink sales to develop the production and variable cost budgets. She calls several developers regarding the number of sinks they expect to install in each house. After speaking with several developers, she decides to budget 35% of the total estimated sink sales (units) for production, based on prior year contracts. She also decides to increase their selling price from $70 to $85 since they have not increased their price in 3 years.

Please show the formulas.

Answer 1

Only first four requirements are answered

A D D E F G Requirement 1 Requirement 2 Houses Average sink per house Total Sink sales Price per unit Budgeted Revenue Unique Sinks Sales Budget December 31, 2019 Q1 Q2 6000 24000 3.00 2.90 18000 69600 85.00 $ 85.00 15,30,000 $ 59,16,000 Q3 12000 3.00 36000 85.00 $ 30,60,000 $ Q4 4000 3.10 12400 85.00 10,54,000 Ending Inventory Units sold Finished units needed Less Beginning Inventory Required Production Unique Sinks Production Budget December 31, 2019 Q1 Q2 6960 3600 18000 69600 24960 73200 -600 -6960 24360 66240 Q3 1240 36000 37240 -3600 33640 Q4 1800 12400 14200 -1240 12960 $ $ $ $ 13 14 Requirement 3 Requirement 4 15 16 17 18 Unique Sinks Direct Material and Direct Labor Requirement December 31, 2019 Q1 Q2 Q3 24360 66240 33640 Q4 12960 Required Production Materials Pounds of Material per unit Pounds of Material used Labor Labor hours per unit Labor hours used Unique Sinks Direct Material Purchases December 31, 2019 Q1 Q2 264960 134560 974400 2649600 1239360 2784160 -20000 -264960 1219360 2519200 Q3 51840 1345600 1397440 -134560 1262880 40 Q4 97440 518400 615840 -51840 564000 Ending Inventory Production requirement Total DM required Less Beginning Inventory Required Purchases 40 2649600 40 1345600 40 518400 974400 26 73080 198720 100920 38880 27

Formulas used

2 A H Requirement 1 Requirement 2 oooown - Q2 Q4 Houses Average sink per house Total Sink sales Price per unit Budgeted Revenue Unique Sinks Sales Budget December 31, 2019 Q1 Q2 22 23 24 6000 24000 12000 4000 2.9 3.1 =C8*C9 =D8*D9 =E8*E9 =F8*F9 85 85 85 =C10*c11 =D10*D11 |=E10*E11 |=F10*F11 Unique Sinks Production Budget December 31, 2019 Q1 Q3 Ending Inventory =39*10% =K9*10% I=L9*10% Units sold =C10 =D10 =E10 Finished units needed =+18+19 =+J8+J9 =+K8+K9 Less Beginning Inventory |-600 =-18 Required Production =+|10+111 =+J10+J11 =+K10+K11 =18000*10% =F10 =+L8+L9 =-K8 =+L10+L11 85 |--18 Requirement 3 Requirement 4 13 14 15 16 17 18 19 Unique Sinks Direct Material and Direct Labor Requirement December 31, 2019 | 01 | 02 | 03 | 04 Required Production =+112 =+J12 =+K12 =+L12 Materials Pounds of Material per unit 40 40 40 Pounds of Material used (=C20*c22 =D20*D22 =E20*E22 |=F20*F22 Labor Labor hours per unit Labor hours used =C20*225 =D20*D25 =E20* E25 =F20*F25 Unique Sinks Direct Material Purchases December 31, 2019 01 02 03 04 Ending Inventory =+J21*10% =+K21*10% =+L21*10% =+C23*10% Production requirement =C23 =D23 =E23 =F23 Total DM required =SUM(120:121) =SUM(J20:J21) |ESUM(K20:K21) |=SUM(L20:L21) Less Beginning Inventory -20000 =-120 =-120 =-K20 Required Purchases =SUM(122:123) =SUM(J22:J23) =SUM(K22:K23) |=SUM(L22:L23) =SUN