Do the following problems using Excel. Each problem is for 20 points. Once done upload your file. Firm ABC's sales from 2010 to 2020 are $420 million, $480 million, $350 million, $250 million, $305 million, $520 million, $540 million $600 million $720 million, $400 million, and $563 million. ABC's accounts receivables from 2010 to 2020 are $40 million $50 million, $32 million, $20 million, $35 million. $60 million, $65 million, $75 million, $80 million $49 million and $65 million. It is hypothesized that ABC's accounts receivable are highly correlated with its sales and can be estimated by using sales. 1. Use Excel linear regression to estimate whether ABC's accounts receivable are linearly related to its sales. (Hint: Make sure you enter the data column wise, that means in a column, not a row in Excel.) 2. Discuss the regression output that contains R-square, regression coefficients, and t-stat of regression coefficients. 3. Estimate ABC's 2021 sales using Trend Function. 4. Estimate ABC's 2021 accounts receivable using ABC's 2021 estimated sales from 3. above and the linear regression from 2. above. ABC is preparing a cash budget for 2021, 2022 and 2023. It expects to collect cash of $2,630 million, $2,900 million, and $3,580 million for 2021, 2022 and 2023, respectively. It also expects to disburse cash of $2,865 million, $2.990 million, and $3,500 million for 2021 2022 and 2023, respectively. ABC has cash of $300 million and zero cumulative borrowing at the end of 2020, and desires a cash balance no less than $300 million for all years. 1. Prepare a cash budget for ABC that covers 2021, 2022 and 2023. (Hint: Start with beginning cash + cash collection - cash disbursement + current borrowing if any to derive ending cash.) 2. Be sure to use if statement when determining current borrowing, 3. Determine cumulative borrowing and the line of credit needed for 2021 to 2023 cash budget. 4. Use Scenario Manager to show three scenarios with the minimum acceptable cash being $250 million, or $300 million or $350 million and examine the line of credit for each scenario. (Hint: the changing cell is the minimum acceptable cash and the result cell is the line of credit.) The Cold Experience, Inc., a leading manufacturer of The frozen dessert products, is considering the addition of a new products frozen yogurt. The firm estimates that each cup will sell for $5 and that the variable costs per cup will be 60% of the selling price. The fixed costs are expected to be $7 million. The firm expects to sell at least 10M cups the first year and that the marginal tax rate will be 20%. The firm expects to pay $600,000 in preferred dividends and has 1 million shares of common stock outstanding. The firm also has interest expense of $900,000 1. Create an income statement including EPS for the new frozen yogurt's first year using the information provided. 2. Determine the operating break-even point in units and also in dollars. 3. Determine its operating leverage, financial leverage, and combined leverage. 4. If Cold Experience expects sales to go up by -3%, what percentage change in EBIT and EPS should it expects? Explain. Upload Choose a File Do the following problems using Excel. Each problem is for 20 points. Once done upload your file. Firm ABC's sales from 2010 to 2020 are $420 million, $480 million, $350 million, $250 million, $305 million, $520 million, $540 million $600 million $720 million, $400 million, and $563 million. ABC's accounts receivables from 2010 to 2020 are $40 million $50 million, $32 million, $20 million, $35 million. $60 million, $65 million, $75 million, $80 million $49 million and $65 million. It is hypothesized that ABC's accounts receivable are highly correlated with its sales and can be estimated by using sales. 1. Use Excel linear regression to estimate whether ABC's accounts receivable are linearly related to its sales. (Hint: Make sure you enter the data column wise, that means in a column, not a row in Excel.) 2. Discuss the regression output that contains R-square, regression coefficients, and t-stat of regression coefficients. 3. Estimate ABC's 2021 sales using Trend Function. 4. Estimate ABC's 2021 accounts receivable using ABC's 2021 estimated sales from 3. above and the linear regression from 2. above. ABC is preparing a cash budget for 2021, 2022 and 2023. It expects to collect cash of $2,630 million, $2,900 million, and $3,580 million for 2021, 2022 and 2023, respectively. It also expects to disburse cash of $2,865 million, $2.990 million, and $3,500 million for 2021 2022 and 2023, respectively. ABC has cash of $300 million and zero cumulative borrowing at the end of 2020, and desires a cash balance no less than $300 million for all years. 1. Prepare a cash budget for ABC that covers 2021, 2022 and 2023. (Hint: Start with beginning cash + cash collection - cash disbursement + current borrowing if any to derive ending cash.) 2. Be sure to use if statement when determining current borrowing, 3. Determine cumulative borrowing and the line of credit needed for 2021 to 2023 cash budget. 4. Use Scenario Manager to show three scenarios with the minimum acceptable cash being $250 million, or $300 million or $350 million and examine the line of credit for each scenario. (Hint: the changing cell is the minimum acceptable cash and the result cell is the line of credit.) The Cold Experience, Inc., a leading manufacturer of The frozen dessert products, is considering the addition of a new products frozen yogurt. The firm estimates that each cup will sell for $5 and that the variable costs per cup will be 60% of the selling price. The fixed costs are expected to be $7 million. The firm expects to sell at least 10M cups the first year and that the marginal tax rate will be 20%. The firm expects to pay $600,000 in preferred dividends and has 1 million shares of common stock outstanding. The firm also has interest expense of $900,000 1. Create an income statement including EPS for the new frozen yogurt's first year using the information provided. 2. Determine the operating break-even point in units and also in dollars. 3. Determine its operating leverage, financial leverage, and combined leverage. 4. If Cold Experience expects sales to go up by -3%, what percentage change in EBIT and EPS should it expects? Explain. Upload Choose a File