## (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 &quot;current assets factor&quot; 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 &quot;net fixed assets factor&quot; 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 &quot;current liabilities factor&quot; 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 &quot;sales factor&quot; multiplied by sales in previous year

Distribution of sales factor each year (normal)

Mean

Stdev

Expense in any year equals an &quot;expense factor&quot; 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:
STATUS
QUALITY
Approved

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

STATUS

QUALITY

Approved

Sep 13, 2020

EXPERT

Tutor 