The Case Protect a Case Corporation Protect A Case Corporation (PAC) is a newly established, small...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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% 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%
Expert Answer:
Related Book For
College Mathematics for Business Economics Life Sciences and Social Sciences
ISBN: 978-0321614001
12th edition
Authors: Raymond A. Barnett, Michael R. Ziegler, Karl E. Byleen
Posted Date:
Students also viewed these accounting questions
-
The production manager of Rordan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: 1st Quarter 2nd Quarter 3rd Quarter 4th Quarter...
-
The Dallas Mavericks basketball team has hired you as its new accountant. On your first day on the job, Mavericks owner, Mark Cuban, comes to you and asks, How many tickets must we sell to pay for...
-
A law firm has hired you as a Network Security Consultant to help its operations by providing a secure network to support its growth. The Law firm currently has offices in Philadelphia, Cherry Hill,...
-
During the year ended 30 June 20X5, Gem Stone Ltd acquired two areas of interest Site A and Site B. The exploration and evaluation activities at Site A amounted to $64454401. The costs relate to the...
-
Assume that you were a new entry-level employee in a hotel. What are some things that you would want your supervisor to do for you during your first few weeks on the job?
-
The fair value of Wallis, Inc.'s depreciable assets exceeds their book value by $50 million. The assets have an average remaining useful life of 15 years and are being depreciated by the...
-
Bernardino Santos-Rodiguez was piloting a boat owned by his friend, Raul Viera-Torres, in waters near Guayama, Puerto Rico. The boat was equipped with a hydraulic steering system manufactured by...
-
1. Develop a CPM/PERT network for Moore House Contractors and determine the probability that the contractors can complete a house within 45 days. Does it appear that the Moores might need to increase...
-
IQ scores (as measured by the Stanford-Binet intelligence test) are normally distributed with a mean of 100 and a standard deviation of 16. What percentage of the population has an IQ score between...
-
In 1879, A.A. Michelson measured the velocity of light in air using a modification of a method proposed by the French physicist Foucault. Twenty of these measurements are in table 6E.27 (the value...
-
Conflict is common within criminal justice organizations. Criminal justice administrators have recognized that the best they can do is manage conflict; eradication of organizational conflict is not...
-
Which was more effective in getting us out of the Great Recession of 2007-8, monetary or fiscal policy? Use the case on the American Recovery and Reinvestment Act, as well as your research on the...
-
Write the feature of Super's Career Development Assessment and Counseling (C-DAC) system?
-
Describe one type of role-play technique you could use in a specific group setting. Explain who would benefit from this technique and why. Toseland, R. W., & Rivas, R. F. (2017). An introduction to...
-
A couple is sitting in the middle of an auditorium before the play starts. Even though everyone in the auditorium is talking, they can only hear each other's conversation. What is this an example of?
-
Frank had been a part of the Business Projects area for three years. A young man in his early twenties, he had started with HEDB in the hope of kick starting his career in the finance industry. While...
-
Q39 Members of the baryon decouplets typically decays after 1523 sec into a lighter baryon ( from baryon octet) and a meson ( from pseudoscalar meson octet). Analyze all possible decay modes for 52....
-
What are bounds and what do companies do with them?
-
In Problems 25 - 30, fund the indicated function f of a single variable. f(y) = H(0,y) for H(x,y) = x2 - 5xy - y2 + 2
-
Archaeology: carbon-14 dating. The radioactive carbon-14 (14C) in an organism at the time of its death decays according to the equation A = A0e-0.000124t where t is time in years and A0 is the amount...
-
In Problem , (1) Introduce slack, surplus, and artificial variables and form the modified problem. (2) Write the preliminary simplex tableau for the modified problem and find the initial simplex...
-
Consider a Lagrangian \(L^{\prime}=L+d f / d t\), where the Lagrangian is \(L=\) \(L\left(q_{k}, \dot{q}_{k}, tight)\), and the function \(f=f\left(q_{k}, tight)\). (a) Show that...
-
Show that the function \(L^{\prime}\) given in the preceding problem must obey Lagrange's equations if \(L\) does, directly from the principle of stationary action. Lagrange's equations do not have...
-
In Example 4.8 we analyzed the case of a bead on a rotating parabolic wire. The energy of the bead was not conserved, but the Hamiltonian was: There is an equilibrium point at \(r=0\) which is...
Study smarter with the SolutionInn App