Assignment - Part II

.docx

School

University of Lethbridge *

*We aren’t endorsed by this school

Course

4130

Subject

Accounting

Date

Jan 9, 2024

Type

docx

Pages

Uploaded by CommodoreValorAnteater42

1 Submitted by Fatema Hossain Id 001231516 Section AA University of Lethbridge ACCT 4130 A & AA Fall 2023 Budgeting Assignment – Part II (Fall 2023) Instructor: Murray Lindsay Deliverables At the beginning of your submission file place your student number. If you do this in a group of two, place both students’ student numbers. Simply place your response immediately below the question. Submit your file through Crowdmark which is located in the Nov 2 tab in Moodle. Before you begin 1. It is imperative that you were able to get your balance sheet to balance for all three parts (A, B, and C) for Part I of the project. If this did not occur see your instructor before proceeding with Part II 2. You will need to use the summary statistics table provided near the top of your assumptions page in columns O through S for the questions in Part B below. It looks like the following: Go back to the original assumptions in the case, i.e., sales growth is 13% and bad debt expense is 2% and the amount collected after the 2 nd month following the sale is 23%. Now save your existing financial model (Part I) under a new name, say, Part II. Part A Add a worksheet after schedule 10 (statement of cash flows) and call it Part II – A.

2 Basic Calculations (perform any required calculations on the new worksheet and link them to the relevant schedules) 1. Calculate the break-even in sales dollars and the margin of safety percentage, showing the formula and intermediate calculations in arriving at your response. 2. Now you will test the accuracy of your answer in 1) by learning an incredibly useful Excel function called GOAL SEEK . Instructions for how to use it appear on the last page of this assignment. a) Using GOAL SEEK, what breakeven figure do you get? Is it the same answer as in 1) above? Answer: Using Goal Seek, Break-Even Sales is $763212 for which the sales growth needs to 17% b) The net income should be close to zero (it won’t be exactly zero because we have used the round-up function). By looking at your income statement (Schedule 8), can you determine why your BE calculation in 1)

3 is some distance away from what the GOAL SEEK function calculated? In other words, what input factor did the manual BE calculation not incorporate? Recognize that this is a flaw in the manual method. Answer: The manual break-even calculations and Goal Seek functions can differ for several reasons. Goal Seek uses numerical methods to iteratively adjust input variables to find the exact point where net income is zero. Manual calculations often use rounded or estimated values, which can result in a less precise break-even point. Also, Goal Seek can handle complex models with interdependencies, while manual calculations may oversimplify the analysis. 3. Examine Schedule 10 carefully. The cash flow generated by the company’s operations should be negative (see cell I18). a) Explain to management why a company cannot perennially operate with a negative operating cash flow. Answer: Protect A Case Corporation (PAC) cannot operate with a perennially negative operating cash flow because it leads to liquidity issues, reliance on external financing, financial instability, increased risk of insolvency, limited growth and investment opportunities, decreased company valuation, and adverse effects on employee morale and retention. Sustainable operations with positive cash flow are crucial for long-term success and financial health. Management should take corrective actions to address these issues. Part B 1. Addition of New Product (Premium case) Go back to the original assumptions (i.e., change your sales growth figure in cell H11 of the assumptions sheet back to 13%) PAC is currently deciding if offering a premium phone case for the upcoming fiscal year (2024) would help boost the organization's profitability. The new premium product would sell for $100 per unit and cost $60 per unit. Sales in Quarter 1 are expected to be 500 units. Because the industry is growing, management believes the premium phone case sales will grow by 20% in each of the following quarters in 2024. (Don’t forget to use the roundup function for the sales quantity you used in Part I beginning in quarter II.) The sales price and cost will remain fixed but you should program it if there is a change when conducting what-if analyses. Specifically, set things up so you can conduct a what-if analysis on selling price, costs, and quantity (see question j below).

4 For both products, sales in Quarter 1 2025 (i.e., the following budget year) will equal quarter 4, 2024 sales, i.e., no change. (As a check figure, the total assets on the balance sheet should be $745,003.) a) Report in the space below the three key financial statistics contained in the green box in the assumptions worksheet. (Simply use the “snipping tool” and place your response below.) c) Calculate and report the contribution margin ratio of the two individual products .

5 b) Help management understand the following conundrum. With the introduction of the premium product, profits increased from a loss of $22,178 to a profit of $39,279, i.e., a 278% improvement in profits even though the contribution margin ratio of the premium product is substantially less than the standard product’s contribution margin ratio. Explain to management why this has happened? Does your response explain why companies try so hard to increase sales? Answer: The increase in profits with the introduction of a premium product, despite its lower contribution margin ratio, is due to the concepts of profit leverage, increased sales volume, and economies of scale. This happens because the premium product likely attracted more customers, resulting in higher sales volume, and these increased sales more than compensated for the lower contribution margin ratio. This demonstrates why companies are so dedicated to increasing sales – it's not just about maximizing the margin on individual products, but also about expanding the customer base, boosting overall sales, and reaping the benefits of improved profitability, economies of scale, and customer loyalty. Sales growth is a critical driver of a company's success and sustainability. c) Given the dramatic percentage increase in profits from introducing the new product, explain to management why credit line borrowing has continued to remain at a high level ? In considering your response, examine Schedule 10 to explain why the company needs to continue with debt financing. Hint: compare Schedule 10 under the one product company with the two-product company.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version