Comes with two premade templates to fill for the assignment. See word document
Overview of the Project The project consists of two parts. Part I involves developing a basic financial model for the case organization. You must submit this Part by the due date. Part II then uses this model to conduct “what-if” calculations to help make recommendations to management to improve the performance of the organization. These scenarios will also teach you the importance of managing working capital (accounts receivable, inventories, and accounts payable) for improving a company’s cash flow. Part II will be covered in class. There is NO submission requirement for Part II. Submission of Part II – will be accepted for overmark consideration. Project requirements · You must use excel (nothing by hand). Download and save the student excel template that is located in the Budget Assignment folder. Complete all required schedules using this template. This template will save you a lot of time and allow your professor to help you quickly should the need arise (otherwise providing help would be time prohibitive). · 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 type a number into a cell. If you do, the financial model will not work when what-if analyses are performed. The marker will expressly check to ensure that every schedule is based on linking cells to other cells (i.e., no number is manually entered into a cell other than on the assumptions worksheet). · Rationale: in the real world (and in this assignment), it is common to change your assumptions to determine how various outcomes of interest might change or to determine how sensitive your results are to a specific assumption being used. By using an assumptions page, you will only need to change the value of the assumption in the assumption worksheet once and not worry about changing your other spreadsheets. This is the foundation for preparing a useful financial model. · By having all your assumptions in one common place, others using the model (or the creator weeks later) will have all the assumptions located in one main location and be able to easily review and assess their appropriateness. · Be sure to use commas (“,”) within your numbers (i.e., 100,000 and NOT 100000) and round calculations to the nearest dollar. For example, it is poor practice to report that total purchases were $282,318.25. You should only report decimals when a number is an input into the model, such as a selling price of $2.52 as even small changes can dramatically impact results. This allows someone reviewing the model to assess the input values that are being used for important inputs. Other than this, calculate your cell totals to the nearest dollar (i.e., no decimals). Your calculations will still be accurate as excel uses the decimal part of the number even if it isn’t showing. Misplaced precision simply detracts from assisting the reader to focus on the real story. (For example, Walmart reports its financial statements rounded to the nearest million.) Following these two suggestions will make your spreadsheets easier to follow and look more professional. Marks will be deducted if these two requirements are not followed. · Utilize the check numbers that are provided below to make sure you are on the right track. · Follow the hints provided. These hints will help steer you through the difficult parts of the assignment. They organized by schedule. · If necessary, review the VM3 and VM4 and accompanying excel file for the comprehensive budgeting problem located in Wiley Plus for the Budget classes. · The key step to ensure your model is correct is to (i) arrive at the same total Assets figure as provided in the check figure below and (ii) to have the total liabilities and shareholders’ equity figure agree with this number (i.e., the balance sheet needs to balance). 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,000 a 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 policy that any accumulated interest must be paid off at the start of the next quarter, i.e., you pay off last quarter’s accumulated interest expense on day 1 of the current quarter. Required: 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 7 – MOH 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 The above schedules will consist of your first of three submissions. The name of this first submission should be called Part A. Email your instructor the excel file containing the spreadsheet model containing Schedules 1-12. This will allow the marker to check for proper linkage among cells (i.e., to check that you have not used a “hard” number except in the assumptions page). If you use Google docs, be sure to convert your final version back to excel. The subject heading in your email should list the last names of the group members as well as the section they are in followed by Part A, e.g., Smith (C ) and Wiley (A) – Part A. The next two parts are incorporated into the assignment to ensure you have not made some basic mistakes and that you have properly linked cells to other cells and/or to the assumptions sheet. If you have made a mistake in your model your balance sheet will not balance. If your balance sheet balances your model would appear to be correct. b) Part B. Change the sales growth (quantity) factor from 12% to 50%. Submit a second electronic spreadsheet called Part B reflecting this change. Sequence your balance sheet so it appears as the first worksheet in the file you submit and not near the last. Make sure you balance sheet balances! If it doesn’t you made a mistake. c) Part C. Go back to the original assumptions. In the accounts receivable collections patterns in the assumption sheet, change the value for the percentage of credit sales collected in the current month to 80%, 10% for the month following sale, 5% in the second month following the sale, and bad debts 5%. The percentage of cash and credit sales does not change from the original assumptions. Submit a new electronic spreadsheet called Part C. Sequence your balance sheet so