BUS-4072_FouchTashia_Week6Assignment

pdf

School

Capella University *

*We aren’t endorsed by this school

Course

4072

Subject

Business

Date

Apr 30, 2024

Type

pdf

Pages

Uploaded by SayeedandMe2023

Week 6 Assignment: Forecasting, Budgeting and Projecting Requirements Tashia Fouch Capella University BUS-4072: Analysis for Financial Management Professor Robert Watson February 2024

Problem 8 TABLE 3.5 Forecasting with a Spreadsheet: Pro Forma Financial Forecast for R&E Supplies, Inc., December 31, 2018 ($ thousands) Table 3.5 presents a computer spreadsheet for estimating R&E Supplies’ external financing required for 2018. The text mentions that with modifications to the equations for equity and net sales, the forecast can easily be extended through 2019. Write the modified equations for equity and net sales. Equity = Common Stock + Retained Earnings Equity = 1,808 + 78 Equity = 1,886 Net Sales = Net sales + (Net sales x 25%) Net Sales = 25,766 + (25,766 x 25%) Net Sales = 25,766 + 6,441.50 Net Sales = 32,207.50

Problem 9 A spreadsheet containing R&E Supplies’ 2018 pro forma financial forecast, as shown in Table 3.5, is available for download from McGraw- Hill’s Connect or your course instructor (see the Preface for more information). Using the spreadsheet information presented next, and the modified equations determined in question 8 earlier, extend the forecast for R&E Supplies contained in Table 3.5 through 2019. a. What is R&E’s projected external financing required in 2019? How does this number compare to the 2018 projection? To answer this, I first had to figure out the amounts in 2017 by using the given forecasted amounts of 2018 and the percentage changes. Based on this, I got the following figures for 2017: Forecast: Net sales: 20,613 25% increase from 2017 COGS: 17,727 86% of net sales Gross profit: 2,886 General/selling/admin expenses: 2,474 12% of net sales Interest expense: 210 10% increase from 2017 Earnings before tax: 202 Tax: 91 45% of earnings before tax Earnings after tax: 111 Dividends paid: 56 50% of earnings after tax Additions to retained earnings: 56 Next, I forecasted for 2019 using the amounts for 2017 and 2018, and got the following figures: Forecast: Net sales: 26,798 30% increase from 2017 COGS: 23,045 86% of net sales Gross profit: 3,752 General/selling/admin expenses: 2,948 11% of net sales Interest expense: 231 10% increase from 2017 Earnings before tax: 573 Tax: 258 45% of earnings before tax Earnings after tax: 315 Dividends paid: 158 50% of earnings after tax Additions to retained earnings: 158 With the forecasted amounts for 2019, I then used the balance sheet for 2018 to project the balance sheet for 2019 and compare the external financing required:

Your preview ends here

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

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

BALANCE SHEET 2018 2019 Forecast Current assets 7,472 7,771 >29% of net sales Net fixed assets 280 270 Total assets 7,752 8,041 Current liabilities 3,736 3,859 Long-term debt 660 560 Equity 1,808 1,966 >RE 2018 + additions to RE 2019 Total liabilities & owners ’ equity 6,204 6,384 External financing required 1,548 1,657 Based on the above, R&E ’ s projected external financing required in 2019 is $1,657. Compared to the 2018 projection, this amount represents an increase of $109, which is a 7% increase. b. Perform a sensitivity analysis on this projection. How does R&E’s projected external financing required change if the ratio of cost of goods sold to net sales declines from 86.0 percent to 84.0 percent? With a decrease from 86% to 84% for the cost of goods sold, the external financing required for 2019 would then become less than the external financing required for 2018 (whereas before 2019 required more external financing). Also, since net income increases as a result of the decrease in the cost of goods sold, the amount of external financing required decreases for both 2018 and 2019. c. Perform a scenario analysis on this projection. How does R&E’s projected external financing required change if a severe recession occurs in 2019? Assume net sales decline 5 percent, cost of goods sold rises to 88 percent of net sales due to price cutting, and current assets increase to 35 percent of net sales as management fails to cut purchases promptly in response to declining sales. As an effect of the decrease of net sales and the subsequent increase of COGS, the additions to retained earnings decreases from the original amount of 158 to a new amount of 65. Due to this decrease, the total amount of equity for 2019 amounted to only 1,814. And since current assets as a percentage of net sales increased to 35%, the total external financing required also increased exponentially, from the 1,657 to 2,947. Problem 10 The treasurer of Westmark Industrial, Inc., a wholesale distributor of household appliances, wants to estimate his company’s cash balances for the first three months of 2018. Using the following information, construct a monthly cash budget for Westmark for January through March 2018. Westmark’s sales are 20 percent for cas h, with the rest on 30-day credit terms. Its purchases are all on 60-day credit terms. Does it appear from your results that the treasurer should be concerned about investing excess cash or looking for a bank loan? Monthly cash collections

Since 20% of sales are for cash, cash collections can be calculated as follows: January: 600 x 0.2 = 120 February: 240 x 0.2 = 48 March: 240 x 0.2 = 48 Monthly credit sales Since the remaining 80% of sales are on 30-day credit terms, credit sales can be calculated as follows: January: 600 x 0.8 = 480 February: 240 x 0.8 = 192 March: 240 x 0.8 = 192 Cash payments for purchases January: 510 February: 540 March: 1,200 Cash payments for wages: January: 110 February: 110 March: 110 Cash payments for the principal payment on debt due in March: 210 Cash payments for interest due in March: 90 Cash payments for the dividend payable in March: 300 Cash payments for taxes payable in February: 180 Addition to accumulated depreciation in March: 30 Cash balance for each month I calculated the cash balance as follows: January: 300 + 120 - 510 - 110 = -200 February: -200 + 48 - 540 - 110 - 180 = -982 March: -982 + 48 - 1,200 - 210 - 90 - 300 + 30 = -2,504 Based on these numbers, we see that the cash balance is negative for all three months, which means that there is a cash shortfall. Therefore, I think that the treasurer of Westmark Industrial should definitely be concerned about investing excess cash or looking for a bank loan. Either of these options can help cover this cash deficit.

Related Documents

Motivation_&_Retention_in_IT Organization Analysis.docx

ch10-bai-tap-chuong-10.pdf

MBA 530 GROW Model Template_Reedy.docx

BUS-4072_FouchTashia_Week9Assignment.pdf

DB2.docx

BUS-4072_FouchTashia_Week4Assignment.pdf

Discussion Thread Reply_ Processes of Global Logistics.docx

BUS-4072_FouchTashia_Week5Assignment.pdf

BUS-4072_FouchTashia_Week3Assignment.pdf

BUSI650 ILP - Kaiser Permanente Introduction.docx

Discussion Thread_ Lean Six Sigma Implementation.docx

DB4.docx

Recommended textbooks for you

Practical Management Science

Operations Management

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:Cengage,

Purchasing and Supply Chain Management

Operations Management

ISBN:9781285869681

Author:Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson

Publisher:Cengage Learning

Marketing

ISBN:9780357033791

Author:Pride, William M

Publisher:South Western Educational Publishing

Principles of Management

Management

ISBN:9780998625768

Author:OpenStax

Publisher:OpenStax College

SEE MORE TEXTBOOKS

Recommended textbooks for you

Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,
Purchasing and Supply Chain Management
Operations Management
ISBN:9781285869681
Author:Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:Cengage Learning
Marketing
Marketing
ISBN:9780357033791
Author:Pride, William M
Publisher:South Western Educational Publishing
Principles of Management
Management
ISBN:9780998625768
Author:OpenStax
Publisher:OpenStax College