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...

1 answer below »
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
Answered 5 days AfterDec 09, 2022

Answer To: Overview of the Project The project consists of two parts. Part I involves developing a basic...

Shivi answered on Dec 14 2022
25 Votes
Budget Project
Assumptions
                Assumptions
                                                                Hover your mouse cursor over the cell with the red corner; a comment should appear.
        Sales Information:
                2021
        Standard Table:        Q3     Q4     What-if factors
        Sales price        $190    190    5%                        Quick Financial Information
        Sale quantity        1,800    1,800    12.0%                        Operating Income                    - 0
                                                Ending line of credit balance                    - 0
        Adjusted Sales Information                Budgeted Year (2022)                        Cash flow from operations                    241,708
                    Q1    Q2    Q3    Q4
        Sales price            200    200    200    200
        Sales quantity            1,800    2,016    2,258    2,529
        Deluxe Table                What-if factors
                                                Beginning Balance Sheet:
        Sales pri
ce                0%                                        Table Company
        Sale quantity                20%                                        Budgeted Balance Sheet
                                                                31-Dec-21
        Adjusted Sales Information                Budgeted Year
                    Q1    Q2    Q3    Q4                Current assets:
        Sales price            310    310    310    310                    Cash                     6,000
        Sales quantity            340    408    490    588                    Accounts receivable (net of bad debts):
                                                            Q3 amount to collect Q1        24,624
                                                            Q4 amount to collect Q1        102,600
                                                            Q4 amount to collect Q2        24,624    151,848
        Collection Information:                                            Inventory*:
                                                        Finished goods inventory
        % of sales paid with cash                    40%                                Standard table             111,860
        % of sales on credit                    60%                                Deluxe table        0    - 0
                                                        Raw materials inventory
        Percentage of credit sales collected in:                                                    Standard table:
            Current month                35%                                    Wood    25,000
            Following month                50%                                    Metal    14,000    39,000
            Second following month                12%                                Deluxe table            0
            Uncollectible Portion (Bad Debt expense)                3%                        Total current assets                        308,708
                                                            * note: there is no WIP inventory
                                                Property, plant, and equipment:
        Direct Material, Direct Labour, & MOH Payment Information:
                                                    Fixed Assets                    625,000
        Percentage of direct materials purchases paid in:                                            Less Accumulated depreciation                    (40,000)
            Current month                80%                        Total property, plant, and equipment                        585,000
            Following month                20%                    Total assets                            $ 893,708
        All direct labour and MOH are paid in the month occurred.                                        Current Liabilities:
                                                    Interest payable on line of credit                    1,000
                                                    Line of credit                    50,000
        Desired Finished Goods Ending Inventory Information:                                            Accounts payable                    60,000
                                                    Dividends payable (QTR 1)                    12,000
        The company desires to have on hand at the end of each month:                                            Income tax payable                    16,000
                                                    Total liabilities                        139,000
            Finished goods inventory: equal to the % of the following month's sales                30%
                                                Shareholders' equity:
                                                    Common shares                    700,000
            Direct materials inventory: equal to the % of the following month's production needs.                20%                        Retained earnings                    54,708
                                                    Total shareholders' equity                        754,708
                                                Total liabilities and shareholders' equity                            $ 893,708
        Dec 31, 2021 ending inventories:                Units    Dollar Value
            standard tables (finished goods)            650    $ 111,860
            deluxe tables (finished goods)            0    0
            Regular Wood (square feet)            5,000    25,000
            Regular Metal components            2,000    14,000
            Premium Wood            0
            Premium metal components            0
        Direct Material & Direct Labour Production Information:
        Direct materials used to manufacture each product:
        Standard table
            Square foot of wood required per unit                10.00
            Cost per square foot of wood                5.00
            Metal components required per unit                4.00
            Cost per metal component                7.00
        Deluxe Table ...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here