The Case Protect a Case Corporation Protect A Case Corporation (PAC) is a newly established, small privately held retail business that sells a standard phone case at competitive prices. The company has hired you as a financial analyst intern to assist with company planning to replace Jane Honeywell who is going on maternity leave. Your first task is to complete a master budget for the upcoming fiscal year 2021 and determine the financial viability of the organization going forward. Since its inception, PAC has struggled to become profitable and, consequently, has failed to add shareholder value. However, for the upcoming 4 quarters, management projects that unit sales of standard cases will grow by a "splendid" 13% over each of the previous quarters due to the positive reception of its revised standard product in the marketplace. (QTR 1 2022 sales are expected to be the same as QTR IV 2021 sales.) The base for the sales increase in quarter 1, 2021 are unit sales in quarter 4 of the prior year. In addition, management believes it has the capability to raise the price of the standard product by 5% for the entire 2021 year (over what it was in QTR 4, 2020) without impacting these sales growth projections. This price is expected to last at least until the end of quarter 1, 2022. Management is optimistic the increase in sales price and quantity will allow the company to turn the corner and become profitable. The last bit of good news you discover is that the wholesale cost of the standard product is anticipated to remain the same as in the final quarter of 2020. Management has signaled their intention to remodel the interior of their store in the first quarter of the upcoming year to provide customers with a more appealing atmosphere. The remodel is estimated to cost $22,000 and must be paid when the services are completed. The additional depreciation associated with this renovation is $2,200 per quarter beginning in quarter 2. In your discussions with management, they reiterated the importance shareholders are placing on profitability. In addition, management is concerned about cash flow generation. The bank will not increase the company's credit line above its current amount and the shareholders are not prepared to inject new capital into the business. (See the explanation of how a credit line works in Exhibit 4.) Management is hoping the budgeting process will lead to drastic improvements in these areas. From the knowledge gained from taking your managerial accounting course last year, 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) you are being asked to prepare. You also know from your course the importance of separating costs by behavior. After undertaking discussions with various personnel you have compiled information for selling and administration expenses (Exhibit 1), sales collection patterns for accounts receivable (Exhibit 2), purchases (Exhibit 3), investing and financing decisions (Exhibit 4), and payments for taxes (Exhibit 5). These exhibits are contained in an excel file that accompanies this case (see PAC Exhibits). Meeting with Jane Honeywell On Jane's last day, you meet with her to discuss the assignment ahead of you. She tells you that the key to being able to get the most of the planning process for the purposes of making good decisions and establishing sound plans is to utilize an "Assumptions Sheet." The use of this assumptions sheet allows management to readily determine the impact of changes to key estimates in the course of performing "what if analyses She Meeting with Jane Honeywell On Jane's last day, you meet with her to discuss the assignment ahead of you. She tells you that the key to being able to get the most of the planning process for the purposes of making good decisions and establishing sound plans is to utilize an "Assumptions Sheet." The use of this assumptions sheet allows management to readily determine the impact of changes to key estimates in the course of performing "what-if" analyses. She provides you with an excel template that she used in the previous year (see PAC Budget Project - student excel template). 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 indicated in the various worksheets as little red triangles. You simply need to click on them to read the comment. If you no longer wish to see the comment click on the cell, right click and press "hide comment" on the menu option when you have understood the point. The assumption sheet she has prepared contains cells for all the information 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 balance sheet for the last fiscal year ending December 31, 2020. Finally, in addition to the assumptions sheet, she has provided you with templates for the various schedules that are involved with this assignment. Jane 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 "hard" number into a cell. If you do, the financial model will not work when what-if the son os as prop information 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 balance sheet for the last fiscal year ending December 31, 2020. Finally, in addition to the assumptions sheet, she has provided you with templates for the various schedules that are involved with this assignment. Jane 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 "hard" number into a cell. If you do, the financial model will not work when what-if analyses are conducted and you will become very frustrated (and you will lose a lot of marks as well). Other Information From talking to Jane you also find out the following about 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 payments 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. Finally, at present, PAC only sells one product (called internally the base case). But it is giving consideration to begin selling a premium case. In anticipation of this possibility, Jane has set up the templates to accommodate this possibility (to be used in Part II of the case which will be conducted in class). Ignore this possibility in Part I (the part you have to hand in). occur on the first day of the quarter. On the other hand, any payments 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. Finally, at present, PAC only sells one product (called internally the base case). But it is giving consideration to begin selling a premium case. In anticipation of this possibility, Jane has set up the templates to accommodate this possibility (to be used in Part II of the case which will be conducted in class). Ignore this possibility in Part I (the part you have to hand in). Required a) Develop a working financial model using the information provided in the exhibits along with the additional information Jane Smith provided in the assumptions page of the file called Student template. The following schedules are required: Schedule 1 Sales Budget Schedule 2 Schedule 3 Case collections Budget Inventory Purchases Budget Schedule 4 Cash Payments for Inventory Purchases Schedule 5- Ending Inventory Budget Schedule 6- Operating Expenses budget Schedule 7 Cash Budget - Schedule 8 Budgeted Contribution Margin format Income Schedule 9 Budgeted Balance Sheet Schedule 10-Budgeted Statement of Cash Flows B Variable Costs: C Fixed Costs: D Exhibit 1-Selling & Administration Expenses E Sales commissions (% of sales) Shipping and handling (per unit) Advertising Employee Salaries Management Salaries Insurance Property Taxes Depreciation Utilities *All items except for depreciation are paid for in the quarter F $ $ G 6% 5.00 5,000 30,000 25,000 2,000 2,000 10,000 4,000 H Exhibit 2- Collection Information % of sales paid with cash % of sales on credit Percentage of credit sales collected in: Current quarter Following quarter Second following quarter Uncollectible Portion (Bad Debt) 40% 60% 40% 35% 23% 2% Exhibit 3- Merchandise Purchasing Information Percentage of purchases paid in: Current quarter Following quarter Desired Ending Inventory Goal (% of the following quarter's expected unit sales) Units in December 31, 2020 ending inventory: Standard Product Premium Product 740 units 0 units 80% 20% 30% Exhibit 4 - Investment & Financing Information Line of Credit (LOC) Information* Maximum line of credit Annual Interest Rate on LOC 70,000 8% 22,000 Purchase of Assets - renovations (quarter 1) Dividends paid each quarter Required minimum cash balance *A line of credit reflects an amount that a borrower can borrow from their bank. The borrower pays interest only on the amount borrowed. If no amount is borrowed then no interest is owed. The borrower can borrow, repay, borrow again and repay, etc as long as they do not exceed the total limit that has been established. In your financial model, do not worry if you exceed the established credit line limit. While in reality you cannot exceed the limit, running the financial model will reveal to management that it must take action to lower the amount to be borrowed so that it is within the limit established. To get the quarterly interest rate, simply divide the annual rate by 4. 4,500 5,000 Exhibit 5 Tax Information - Income Tax Rate Note: income tax expense must be accrued each year but it is not paid until QTR 2 of each year. 18%