The Table Company Case The Table Company (TC) is a small privately held manufacturer of wooden tables that has lost money over the last two years. The company currently manufactures and sells one product a standard table. However, for the first time, beginning in quarter one they will be introducing the manufacture and sale of a deluxe table. The company has hired you as a financial analyst intern to replace Rita Lavalier who is going on maternity leave. Your first task is to complete a master budget for the upcoming fiscal year 2022 and determine the financial viability of the organization going forward. Quarter IV, 2021 numbers are estimates but actuals seem to be tracking them very closely. In the past, TC 's sales have been growing slowly; however, things are more positive as management projects that sales of the standard table will grow in the upcoming year due to the improved economy in regional markets. Specifically, while sales of the standard table in the first quarter of 2022 are expected to be the same as those in quarter 4 of 2021 , they are expected to grow by 12% in each subsequent quarter for the remainder of the year. (In quarter 1 of 2023 , sales are expected to be the same as in quarter 4 of 2022 for both products.) Additionally, management believes the price of the standard table can be increased by 5% in quarter 1,2022 without affecting the sales growth projections. This price will remain in place for the remainder of the year (no further price increases). With respect to the soon to be introduced deluxe table, beginning in QTR 1,2022 , management is planning to sell the deluxe table for $310 for the entire year. In the quarter 1 , management plans to sell 340 tables. However, as the marketplace comes to know about the product, management expects sales volume to increase by 20% in each of the subsequent quarters. Overall, management is optimistic that the increase in sales price and the expected sales growth for the regular table in addition to the introduction of the deluxe table will help turn the company into a profitable organization. To accommodate the sales growth and introduction of the deluxe product, new machinery worth $30,000 will need to be purchased. Management plans to purchase the machinery at the start of quarter one of the upcoming year. This purchase will increase the depreciation by $2,000a quarter beginning in quarter 1. In your discussions, management reiterated the importance company shareholders are placing on the company turning a profit. In addition, management is very concerned with the cash flow. The bank has refused to increase the company's credit line above its current amount and shareholders are not prepared to inject new capital into the business. Management is hoping that the budgeting process will lead to drastic improvements in managing cash flow. From the knowledge gained from taking your managerial accounting course, you understand that putting together a good budget will require more than determining the sales quantities, prices and cost of goods sold. Consequently, your initial task is to meet with relevant employees to gather the remaining information that will serve as inputs into the budget (or financial model). With respect to costs, you know from your management accounting course the importance of separating costs by behavior. After undertaking discussions with various personnel you have compiled information for sales collection patterns for accounts receivable (Exhibit 1), payment policies for direct labor, MOH and materials (Exhibit 2), ending inventory policies for finished goods an direct materials (Exhibit 3), direct material and direct labor information (Exhibit 4), Manufacturing overhead information (Exhibit 5), selling and administration expenses (Exhibit 6), investing and financing information (Exhibit 7), and information for payments of taxes (Exhibit 8). This information is contained in the excel file called List of exhibits. You also come to realize that the company uses a FIFO inventory flow assumption. Meeting with Rita Lavalier On Rita's last day you meet with her to discuss the assignment ahead of you. She tells you that the key to getting the most out of the planning process is to utilize an "Assumptions Sheet." The use of this assumptions sheet allows management to readily determine the impact of changes to key estimates through performing "what-if" analyses. She provides you with an excel template that she used in the previous year (see student excel template) which will provide you with a very good start. While she hopes that providing you with this template does not constitute too much "spoon feeding," she believes you will still find this assignment to be sufficiently challenging. To help you further, she has included numerous comments within the various sections to explain things that she remembers struggling with early on in her career. These comments are included in the various worksheets as little red triangles. You simply need to click on them to read the comment. The assumption sheet she has provided contains cells for all the information that you will need as input into the various schedules that you will have to prepare. It also contains a column for known "what-if" factors that she has found useful to manipulate in the past. Further, it contains the current (budgeted) balance sheet for the fiscal year ending December 31, 2021. You are to assume that this balance sheet is accurate. Finally, in addition to the assumptions sheet, she has provided you with templates for the various schedules that are involved with this assignment. Rita reiterates one final point as she prepares to leave. In preparing the various schedules, the formula in each cell must either reference another cell or the assumptions sheet. Other than in the assumptions sheet, never enter a number into a cell. If you do, the financial model will not work when what-if analyses are conducted. Other Information From talking to Rita, you also find out the following about the operation of the line of credit. Borrowings on the line of credit are assumed to occur on the first day of the Quarter. On the other hand, any repayments made on the line of credit are assumed to occur on the last day of the quarter. While these assumptions are likely to lead to interest payable being slightly overstated, it makes it much easier to calculate interest payments in any given quarter. Also, the bank has a icy that any accumulated interest must be paid off at the start of the next quarter, i.e., you pay last quarter's accumulated interest expense on day 1 of the current quarter. quired: a) Develop a working financial model using the information provided in the exhibits along with the additional information Rita Lavalier provided in the assumptions page of the file called Student template. The following schedules are to be completed and submitted: Schedule 1 - Sales Budget Schedule 2 - Cash collections Budget Schedule 3 - Production Budget Schedule 4 - Direct Materials Budget Schedule 5-Cash Payments for Direct Material Purchases Schedule 6-Direct Labour Budget Schedule 7MOH Budget Schedule 8 - Operating Expense Budget Schedule 9 Cash Budget Schedule 10-Budgeted Contribution Margin Statement As part of this schedule, complete the T accounts that are provided. While unnecessary, this will help solidify what you learnt in Chapter 3 of the course. Schedule 11 - Budgeted Balance Sheet Schedule 12 - Budgeted Statement of Cash Flows Desirad Finished Goods Ending Inventory Information: The corripany desires to hove ori hand at the end of each month: Selling \& Admlnistration Expenses: De uxe Tible Manufacturing Overhead Informatian: V'ariable NOH : \begin{tabular}{l|ll|} \hline Indirect materials (per direct labour houri & $ & 1.00 \\ Indirect labour (per direct labou houri & $ & 3.0X] \\ \hline \end{tabular} Varlable overnead Is allocated on the basis of alrect labour hours. Fixed MOH is estimared to be: Depreciatian per quarter Managemant salary per quarter The Table Company Case The Table Company (TC) is a small privately held manufacturer of wooden tables that has lost money over the last two years. The company currently manufactures and sells one product a standard table. However, for the first time, beginning in quarter one they will be introducing the manufacture and sale of a deluxe table. The company has hired you as a financial analyst intern to replace Rita Lavalier who is going on maternity leave. Your first task is to complete a master budget for the upcoming fiscal year 2022 and determine the financial viability of the organization going forward. Quarter IV, 2021 numbers are estimates but actuals seem to be tracking them very closely. In the past, TC 's sales have been growing slowly; however, things are more positive as management projects that sales of the standard table will grow in the upcoming year due to the improved economy in regional markets. Specifically, while sales of the standard table in the first quarter of 2022 are expected to be the same as those in quarter 4 of 2021 , they are expected to grow by 12% in each subsequent quarter for the remainder of the year. (In quarter 1 of 2023 , sales are expected to be the same as in quarter 4 of 2022 for both products.) Additionally, management believes the price of the standard table can be increased by 5% in quarter 1,2022 without affecting the sales growth projections. This price will remain in place for the remainder of the year (no further price increases). With respect to the soon to be introduced deluxe table, beginning in QTR 1,2022 , management is planning to sell the deluxe table for $310 for the entire year. In the quarter 1 , management plans to sell 340 tables. However, as the marketplace comes to know about the product, management expects sales volume to increase by 20% in each of the subsequent quarters. Overall, management is optimistic that the increase in sales price and the expected sales growth for the regular table in addition to the introduction of the deluxe table will help turn the company into a profitable organization. To accommodate the sales growth and introduction of the deluxe product, new machinery worth $30,000 will need to be purchased. Management plans to purchase the machinery at the start of quarter one of the upcoming year. This purchase will increase the depreciation by $2,000a quarter beginning in quarter 1. In your discussions, management reiterated the importance company shareholders are placing on the company turning a profit. In addition, management is very concerned with the cash flow. The bank has refused to increase the company's credit line above its current amount and shareholders are not prepared to inject new capital into the business. Management is hoping that the budgeting process will lead to drastic improvements in managing cash flow. From the knowledge gained from taking your managerial accounting course, you understand that putting together a good budget will require more than determining the sales quantities, prices and cost of goods sold. Consequently, your initial task is to meet with relevant employees to gather the remaining information that will serve as inputs into the budget (or financial model). With respect to costs, you know from your management accounting course the importance of separating costs by behavior. After undertaking discussions with various personnel you have compiled information for sales collection patterns for accounts receivable (Exhibit 1), payment policies for direct labor, MOH and materials (Exhibit 2), ending inventory policies for finished goods an direct materials (Exhibit 3), direct material and direct labor information (Exhibit 4), Manufacturing overhead information (Exhibit 5), selling and administration expenses (Exhibit 6), investing and financing information (Exhibit 7), and information for payments of taxes (Exhibit 8). This information is contained in the excel file called List of exhibits. You also come to realize that the company uses a FIFO inventory flow assumption. Meeting with Rita Lavalier On Rita's last day you meet with her to discuss the assignment ahead of you. She tells you that the key to getting the most out of the planning process is to utilize an "Assumptions Sheet." The use of this assumptions sheet allows management to readily determine the impact of changes to key estimates through performing "what-if" analyses. She provides you with an excel template that she used in the previous year (see student excel template) which will provide you with a very good start. While she hopes that providing you with this template does not constitute too much "spoon feeding," she believes you will still find this assignment to be sufficiently challenging. To help you further, she has included numerous comments within the various sections to explain things that she remembers struggling with early on in her career. These comments are included in the various worksheets as little red triangles. You simply need to click on them to read the comment. The assumption sheet she has provided contains cells for all the information that you will need as input into the various schedules that you will have to prepare. It also contains a column for known "what-if" factors that she has found useful to manipulate in the past. Further, it contains the current (budgeted) balance sheet for the fiscal year ending December 31, 2021. You are to assume that this balance sheet is accurate. Finally, in addition to the assumptions sheet, she has provided you with templates for the various schedules that are involved with this assignment. Rita reiterates one final point as she prepares to leave. In preparing the various schedules, the formula in each cell must either reference another cell or the assumptions sheet. Other than in the assumptions sheet, never enter a number into a cell. If you do, the financial model will not work when what-if analyses are conducted. Other Information From talking to Rita, you also find out the following about the operation of the line of credit. Borrowings on the line of credit are assumed to occur on the first day of the Quarter. On the other hand, any repayments made on the line of credit are assumed to occur on the last day of the quarter. While these assumptions are likely to lead to interest payable being slightly overstated, it makes it much easier to calculate interest payments in any given quarter. Also, the bank has a icy that any accumulated interest must be paid off at the start of the next quarter, i.e., you pay last quarter's accumulated interest expense on day 1 of the current quarter. quired: a) Develop a working financial model using the information provided in the exhibits along with the additional information Rita Lavalier provided in the assumptions page of the file called Student template. The following schedules are to be completed and submitted: Schedule 1 - Sales Budget Schedule 2 - Cash collections Budget Schedule 3 - Production Budget Schedule 4 - Direct Materials Budget Schedule 5-Cash Payments for Direct Material Purchases Schedule 6-Direct Labour Budget Schedule 7MOH Budget Schedule 8 - Operating Expense Budget Schedule 9 Cash Budget Schedule 10-Budgeted Contribution Margin Statement As part of this schedule, complete the T accounts that are provided. While unnecessary, this will help solidify what you learnt in Chapter 3 of the course. Schedule 11 - Budgeted Balance Sheet Schedule 12 - Budgeted Statement of Cash Flows Desirad Finished Goods Ending Inventory Information: The corripany desires to hove ori hand at the end of each month: Selling \& Admlnistration Expenses: De uxe Tible Manufacturing Overhead Informatian: V'ariable NOH : \begin{tabular}{l|ll|} \hline Indirect materials (per direct labour houri & $ & 1.00 \\ Indirect labour (per direct labou houri & $ & 3.0X] \\ \hline \end{tabular} Varlable overnead Is allocated on the basis of alrect labour hours. Fixed MOH is estimared to be: Depreciatian per quarter Managemant salary per quarter