Question Details

(solution) Hello Can you help me solve the following case on Excel using


Hello Can you help me solve the following case on Excel using @RISK in the Excel template please
A

 

1

 

2

 

3 B C D E F G Financials at Carco 6

 

7 Inputs, assumptions

 

Current assets in any year equals a "current assets factor" multiplied by this year's sales

 

Distribution of current assets factor each year (normal)

 

Mean

 

Stdev 8

 

9 Fixed assets at cost in any year equals sum of this year's depreciation and net fixed assets 4

 

5 10

 

11 Accumulated depreciation in year 0

 

12

 

13 Accumulated depreciation in any other year equals previous year's accumulated depreciation,

 

14 plus percentage of previous year's fixed assets at cost, where:

 

15

 

Percentage

 

16

 

17 Net fixed assets in any year equals a "net fixed assets factor" multiplied by this year's sales

 

18 Distribution of net fixed assets factor each year (normal) Mean

 

Stdev 19

 

20 21

 

22 Total assets each year equals fixed assets plus current assets

 

23

 

24 Current liabilities in any year equals a "current liabilities factor" multiplied by this year's sales

 

25

 

26

 

27

 

28

 

29

 

30

 

31

 

32

 

33

 

34

 

35

 

36

 

37

 

38

 

39

 

40

 

41

 

42

 

43

 

44

 

45

 

46

 

47

 

48

 

49

 

50

 

51

 

52

 

53

 

54

 

55

 

56

 

57

 

58

 

59

 

60

 

61

 

62

 

63

 

64

 

65

 

66

 

67

 

68

 

69

 

70

 

71

 

72

 

73

 

74

 

75

 

76

 

77

 

78

 

79

 

80

 

81

 

82

 

83

 

84

 

85

 

86

 

87

 

88 Distribution of current liabilities factor each year (normal)

 

Mean

 

Stdev

 

Long-term debt, year 0

 

Long-term debt in any other year equals this year's debt-equity ratio multiplied by the sum of this year's retained earnings

 

and this year's stock.

 

Desired debt-equity ratios Year 1 Year 2 Year 3 Year 4 Year 5 Stock in year 0

 

Stock in any other year equals sum of stock in previous year and new stock this year

 

Retained earnings, year 0

 

Ratained earnings in any other year equals sum of retained earnings in previous year and this year's retention

 

Total liabilities in any year is sum of this year's current liabilities, long-term debt, stock, and retained earnings

 

Amount of new stock issued in any year is enough to make total assets equal total liabilities

 

Annual interest rates Current debt New debt During each of next 5 years, a percentage of year 0 long-term debt is paid off, where

 

Percentage

 

Total amount of new debt in any year is this year's long-term debt minus amount of initial debt still remaining

 

New debt for any year equals the total new debt for this year minus total new debt for the previous year

 

Sales, year 0

 

Sales in any other year equals a "sales factor" multiplied by sales in previous year

 

Distribution of sales factor each year (normal)

 

Mean

 

Stdev

 

Expense in any year equals an "expense factor" multiplied by this year's sales

 

Distribution of sales factor each year (normal)

 

Mean

 

Stdev

 

Depreciation, year 0

 

Depreciation in any other year is a percentage of the previous year's fixed assets at cost, where:

 

Percentage

 

Before-tax profit in any year is this year's sales minus sum of this year's expenses, interest payments, and depreciation

 

Tax rate

 

Dividends each year are a percentage of this year's after-tax profits, where

 

Percentage

 

Retention each year is a percentage of this year's after-tax profits, where:

 

Percentage

 

Simulation

 

Year

 

Current assets

 

Fixed assets at cost

 

Accumulated depreciation

 

Net fixed assets

 

Total assets 89 Current liabilities

 

90 Long-term debt

 

91 Stock

 

92 Retained earnings

 

93 Total liabilities

 

94

 

95 New stock

 

96 New debt

 

97 Total new debt

 

98 Debt/equity ratio

 

99

 

100 Income statement

 

101 Sales

 

102 Expenses

 

103 Interest payments

 

104 Depreciation

 

105 Before-tax profit

 

106 After-tax profit

 

107 Dividends

 

108 Retention

 

109

 

110 Output cells for @RISK

 

111 Total new debt (year 5)

 

112 Total interest (years 1-5)

 

113 After-tax profit (year 5)

 

114

 

115 Selected results from @RISK (see next sheet for more)

 

116

 

Total new debt Total interestAfter-tax profit

 

117 Minimum =

 

118 Maximum =

 

119 Mean =

 

120 Std Deviation =

 

121

 

122 95% Perc =

 

123

 

124 Question 3 - from @Risk Detailed Statistics window (not included here)

 

125 Target #1 (Value)=

 

126 Target #1 (Perc%)= H

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now