Can you please solve it using Excel?

09:47 7 LTE budgeting case study BUDGETING CASE STUDY WOODY'S REPRODUCTION Woody's Reproduction makes reproductions antique tables and chairs and sells them through three sales outlets. The product line consists of two styles of chairs, two styles of tables, and three styles of cabinets. Although customers often ask Jack Molinari, the owner/manager of Woody's Reproductions, to make other products, he does not intend to expand the product line. The planning group at Woody's Reproductions prepares a master budget for each fiscal year, which corresponds to the calendar year. It is December 2020, and the planners are completing the master budget for 2021. Unit prices are $200, $900, and $1,800, respectively, for the chairs, tables, and cabinets. Customers pay (1) by cash and receive a 5% discount, (2) by credit card (the credit card company takes 3% of the revenue as its fee and remits the balance in the month following the month of sale), or (3) on account (only exporters buy on account). The distribution of cash, credit card, and exporter sales is 25%, 35%, and 40%, respectively. Of the credit sales to exporters, Woody's Reproductions collects 30% in the month following the sale, 50% in the second month following the sale, and 17% in the third month following the sale, with 3% going uncollected. Woody's Reproductions recognizes the expense of cash discounts, credit card fees, and bed debts in the month of the sale. Woody's employs 40 people who work in the following areas: 15 in administration, sales, and shipping; 2 in manufacturing supervision (director and a scheduler); 9 in manufacturing fabrication and assembly (carpenters); and 14 in manufacturing, finishing, and other areas (helpers, cleaners and maintenance crew). The carpenter hours required to make the parts for and assemble a chair, table, or cabinet are 0.4, 2.5, and 6, respectively. Production personnel have organized the work so that each carpenter hour worked requires 1.5 helper hours. Therefore, production planners maintain a ration on average of 1.5 helpers for every carpenter. The company pays carpenters and helpers $24 and $14 per hour, respectively (including all benefits). Woody's Reproductions guarantees all employees their pay regardless of the hours of work available. When the employees are not doing, their regular jobs, they undertake maintenance, training, community service, and customer relations activities. Woody's pays each employee weekly for that week's work. If an employee works 172 hours or less during the month, Woody's still pays the employee for 172 hours at his or her normal hourly rate. The company pays 150% of the normal hourly rate for every hour over 172 that the employee works during the month. Planners add new carpenters if the projected total monthly overtime is more than 5% of the total regular carpenter hours available. Woody's has a policy of no employee layoffs. Any required hiring is done on the first day of each month. For a factory, Woody's Reproductions rents a converted warehouse that costs $600,000 per year. The company pays rent quarterly beginning January 1 of each year. Woody's pays other capacity-related manufacturing costs, which include manufacturing supervision salaries and amount to $480,000, in equal monthly amounts. 09:47 1 : LTE budgeting case study The capital investment policy is to purchase, each January and July, $5,000 of machinery and equipment per carpenter employed during that month. Woody's recognizes depreciation at the rate of 10% of the year- end balance of the machinery and equipment account. Statistical studies of cost behavior have determined that supplies, flexible support, and maintenance costs vary with the number of carpenter hours worked and are $5, $20, and $15, per hour, respectively. The units of wood required for chairs, tables, and cabinets are 1, 8, and 15 respectively. Each unit of wood costs $30. The inventory policy is to make products in the month they will be sold. Two suppliers deliver raw materials and supplies as required. The company pays for all materials, supplies, flexible support, and maintenance items on receipt. Annual administration salaries, capacity-related selling costs, and planned advertising expenditures are $300,000, $360,000,and $600,000, respectively. Woody's Reproductions makes these expenditures in equal monthly amounts. Packaging and shipping costs for chairs, tables, and cabinets are $15, $65, and $135, respectively. Flexible selling costs are 6% of each product's list price. Woody's Reproductions pays packaging, shipping, and flexible selling costs as incurred. Using its line of credit, Woody's Reproductions maintains a minimum balance of $50,000. All line-of-credit transactions occur on the first day of each month. The bank charges interest on the line-of-credit account balance at the rate of 10% per year. Woody's pays interest on the first day of each month on line-of-credit balance outstanding at the end of the previous month. On the first of each month, the bank pays interest at the rate of 3% per year on funds exceeding $50,000 in the company's cash account at the end of the previous month. Realized sales for October and November, and expected sales for December 2020 appear in the following table: WOODY'S REPRODUCTIONS UNIT SALES 2020 ITEM OCTOBER NOVEMBER DECEMBER 900 Chairs Tables Cabinets 175 90 975 188 102 950 201 95 Sales staff estimates the unit demand for 2021 as follows: chairs, 1,000, plus a random number uniformly distributed between 0 and 50, plus 15% of the previous month's sales of chairs; tables, 200, plus a random number uniformly distributed between 0 and 20, plus 15% of the previous month's sales; and cabinets, 100, plus a random number uniformly distributed between 0 and 10, plus 15% of the previous month sales of cabinets. This estimation process resulted in the demand forecasts and the sales plan found in the following table: WOODY'S REPRODUCTIONS PROJECTED UNIT SALES 2021 MONTH CHAIRS TABLES CABINETS January 1020 200 109 February 1191 237 120 March 1179 243 119 April 119 250 126 May 1200 252 122 June 1204 255 125 July 1194 242 123 August 1199 253 121 September 1222 243 127 October 1219 248 126 09:47 7 LTE budgeting case study account at the end of the previous month. Realized sales for October and November, and expected sales for December 2020 appear in the following table: WOODY'S REPRODUCTIONS UNIT SALES 2020 ITEM OCTOBER NOVEMBER DECEMBER Chairs 900 975 950 188 Tables Cabinets 175 90 201 95 102 Sales staff estimates the unit demand for 2021 as follows: chairs, 1,000, plus a random number uniformly distributed between 0 and 50, plus 15% of the previous month's sales of chairs; tables, 200, plus a random number uniformly distributed between 0 and 20, plus 15% of the previous month's sales; and cabinets, 100, plus a random number uniformly distributed between 0 and 10, plus 15% of the previous month sales of cabinets. This estimation process resulted in the demand forecasts and the sales plan found in the following table: WOODY'S REPRODUCTIONS PROJECTED UNIT SALES 2021 MONTH CHAIRS TABLES CABINETS 109 January February March April May June July August September October November December 1020 1191 1179 1195 1200 1204 1194 1199 1222 1219 1207 1192 200 237 243 250 252 255 242 253 243 248 244 255 120 119 126 122 125 123 121 127 126 126 119 Planners project the Woody's Reproductions balance sheet at January 1, 2021, to be as follows: WOODY'S REPRODUCTIONS BALANCE SHEET, JANUARY 1, 2021 Cash $50,000 Bank loan Account Receivable 575,008 Machinery (net book value) 360,000 Shareholder's equity 985,008 Total $985,008 $985,008 SO Required: a. Prepare a sales budget, production budget, labour budget, cash flow statement, pro forma income statement, and pro forma balance sheet for 2021. b. Supposed that the sales staff is considering increasing the advertising budget from $600,000 to $900,000 and cutting prices by 5%. This should increase sales by 30% in 2021 (sales in 2020 will not be affected). Based on the effect of this change on profitability, is it desirable? c. Is there criterion other than profitability that may be used to evaluate the esirability of the ange osed in (b)? If yes, what is that criterion and why is it important? If no, why is profitability the sole relevant criterion? 09:47 7 LTE budgeting case study BUDGETING CASE STUDY WOODY'S REPRODUCTION Woody's Reproduction makes reproductions antique tables and chairs and sells them through three sales outlets. The product line consists of two styles of chairs, two styles of tables, and three styles of cabinets. Although customers often ask Jack Molinari, the owner/manager of Woody's Reproductions, to make other products, he does not intend to expand the product line. The planning group at Woody's Reproductions prepares a master budget for each fiscal year, which corresponds to the calendar year. It is December 2020, and the planners are completing the master budget for 2021. Unit prices are $200, $900, and $1,800, respectively, for the chairs, tables, and cabinets. Customers pay (1) by cash and receive a 5% discount, (2) by credit card (the credit card company takes 3% of the revenue as its fee and remits the balance in the month following the month of sale), or (3) on account (only exporters buy on account). The distribution of cash, credit card, and exporter sales is 25%, 35%, and 40%, respectively. Of the credit sales to exporters, Woody's Reproductions collects 30% in the month following the sale, 50% in the second month following the sale, and 17% in the third month following the sale, with 3% going uncollected. Woody's Reproductions recognizes the expense of cash discounts, credit card fees, and bed debts in the month of the sale. Woody's employs 40 people who work in the following areas: 15 in administration, sales, and shipping; 2 in manufacturing supervision (director and a scheduler); 9 in manufacturing fabrication and assembly (carpenters); and 14 in manufacturing, finishing, and other areas (helpers, cleaners and maintenance crew). The carpenter hours required to make the parts for and assemble a chair, table, or cabinet are 0.4, 2.5, and 6, respectively. Production personnel have organized the work so that each carpenter hour worked requires 1.5 helper hours. Therefore, production planners maintain a ration on average of 1.5 helpers for every carpenter. The company pays carpenters and helpers $24 and $14 per hour, respectively (including all benefits). Woody's Reproductions guarantees all employees their pay regardless of the hours of work available. When the employees are not doing, their regular jobs, they undertake maintenance, training, community service, and customer relations activities. Woody's pays each employee weekly for that week's work. If an employee works 172 hours or less during the month, Woody's still pays the employee for 172 hours at his or her normal hourly rate. The company pays 150% of the normal hourly rate for every hour over 172 that the employee works during the month. Planners add new carpenters if the projected total monthly overtime is more than 5% of the total regular carpenter hours available. Woody's has a policy of no employee layoffs. Any required hiring is done on the first day of each month. For a factory, Woody's Reproductions rents a converted warehouse that costs $600,000 per year. The company pays rent quarterly beginning January 1 of each year. Woody's pays other capacity-related manufacturing costs, which include manufacturing supervision salaries and amount to $480,000, in equal monthly amounts. 09:47 1 : LTE budgeting case study The capital investment policy is to purchase, each January and July, $5,000 of machinery and equipment per carpenter employed during that month. Woody's recognizes depreciation at the rate of 10% of the year- end balance of the machinery and equipment account. Statistical studies of cost behavior have determined that supplies, flexible support, and maintenance costs vary with the number of carpenter hours worked and are $5, $20, and $15, per hour, respectively. The units of wood required for chairs, tables, and cabinets are 1, 8, and 15 respectively. Each unit of wood costs $30. The inventory policy is to make products in the month they will be sold. Two suppliers deliver raw materials and supplies as required. The company pays for all materials, supplies, flexible support, and maintenance items on receipt. Annual administration salaries, capacity-related selling costs, and planned advertising expenditures are $300,000, $360,000,and $600,000, respectively. Woody's Reproductions makes these expenditures in equal monthly amounts. Packaging and shipping costs for chairs, tables, and cabinets are $15, $65, and $135, respectively. Flexible selling costs are 6% of each product's list price. Woody's Reproductions pays packaging, shipping, and flexible selling costs as incurred. Using its line of credit, Woody's Reproductions maintains a minimum balance of $50,000. All line-of-credit transactions occur on the first day of each month. The bank charges interest on the line-of-credit account balance at the rate of 10% per year. Woody's pays interest on the first day of each month on line-of-credit balance outstanding at the end of the previous month. On the first of each month, the bank pays interest at the rate of 3% per year on funds exceeding $50,000 in the company's cash account at the end of the previous month. Realized sales for October and November, and expected sales for December 2020 appear in the following table: WOODY'S REPRODUCTIONS UNIT SALES 2020 ITEM OCTOBER NOVEMBER DECEMBER 900 Chairs Tables Cabinets 175 90 975 188 102 950 201 95 Sales staff estimates the unit demand for 2021 as follows: chairs, 1,000, plus a random number uniformly distributed between 0 and 50, plus 15% of the previous month's sales of chairs; tables, 200, plus a random number uniformly distributed between 0 and 20, plus 15% of the previous month's sales; and cabinets, 100, plus a random number uniformly distributed between 0 and 10, plus 15% of the previous month sales of cabinets. This estimation process resulted in the demand forecasts and the sales plan found in the following table: WOODY'S REPRODUCTIONS PROJECTED UNIT SALES 2021 MONTH CHAIRS TABLES CABINETS January 1020 200 109 February 1191 237 120 March 1179 243 119 April 119 250 126 May 1200 252 122 June 1204 255 125 July 1194 242 123 August 1199 253 121 September 1222 243 127 October 1219 248 126 09:47 7 LTE budgeting case study account at the end of the previous month. Realized sales for October and November, and expected sales for December 2020 appear in the following table: WOODY'S REPRODUCTIONS UNIT SALES 2020 ITEM OCTOBER NOVEMBER DECEMBER Chairs 900 975 950 188 Tables Cabinets 175 90 201 95 102 Sales staff estimates the unit demand for 2021 as follows: chairs, 1,000, plus a random number uniformly distributed between 0 and 50, plus 15% of the previous month's sales of chairs; tables, 200, plus a random number uniformly distributed between 0 and 20, plus 15% of the previous month's sales; and cabinets, 100, plus a random number uniformly distributed between 0 and 10, plus 15% of the previous month sales of cabinets. This estimation process resulted in the demand forecasts and the sales plan found in the following table: WOODY'S REPRODUCTIONS PROJECTED UNIT SALES 2021 MONTH CHAIRS TABLES CABINETS 109 January February March April May June July August September October November December 1020 1191 1179 1195 1200 1204 1194 1199 1222 1219 1207 1192 200 237 243 250 252 255 242 253 243 248 244 255 120 119 126 122 125 123 121 127 126 126 119 Planners project the Woody's Reproductions balance sheet at January 1, 2021, to be as follows: WOODY'S REPRODUCTIONS BALANCE SHEET, JANUARY 1, 2021 Cash $50,000 Bank loan Account Receivable 575,008 Machinery (net book value) 360,000 Shareholder's equity 985,008 Total $985,008 $985,008 SO Required: a. Prepare a sales budget, production budget, labour budget, cash flow statement, pro forma income statement, and pro forma balance sheet for 2021. b. Supposed that the sales staff is considering increasing the advertising budget from $600,000 to $900,000 and cutting prices by 5%. This should increase sales by 30% in 2021 (sales in 2020 will not be affected). Based on the effect of this change on profitability, is it desirable? c. Is there criterion other than profitability that may be used to evaluate the esirability of the ange osed in (b)? If yes, what is that criterion and why is it important? If no, why is profitability the sole relevant criterion