EA9-R1 Perform a Financial Statement Analysis for Ronald’s Beverage Barn In this

EA9-R1 Perform a Financial Statement Analysis for Ronald’s Beverage Barn
In this exercise, you will perform a financial statement analysis for Ronald’s Beverage Barn. You will perform horizontal/vertical analyses and create charts to highlight key information from these analyses. You will also calculate financial ratios and insert cell comments.
Open EA9-R1-FSA from your Chapter 09 folder.
On the Horizontal Analysis tab, select cell K8, type ＝ and select cell G8, and then type – and select cell I8; tap Enter.
Select cell M8, type ＝ and select cell K8, and then type / and select cell I8; tap Tab.
Copy the range K8:M8, highlight the range K10:M16, and choose Home→Clipboard→Paste →Formulas & Number Formatting.
Highlight the ranges C10:C14 and K10:K14 and choose Insert→Charts→Insert Column or Bar Chart→Clustered Column.
Position and size the chart so it covers the range O2:V16.
Hint: Click the chart to select it, move it, and then resize it.
Replace the chart title default text with Expense Change and then click outside the chart.
CHECK FIGURE Cell M8 ＝ 17.93%
Switch to the Vertical Analysis tab and enter ＝G8/$G$13 in cell K8.
Enter ＝I8/$I$13 in cell L8.
Copy the range K8:L8 and then select the range K9:L13 and choose Home→Clipboard→Paste →Formulas & Number Formatting.
Enter ＝G15/$G$18 in cell K15 and ＝I15/$I$18 in cell L15.
Copy the range K15:L15, then select the range K17:L18, right-click, and choose Paste Special→Formulas & Number Formatting.
CHECK FIGURE Cell L8 ＝ 21.06%
Highlight the range K8:K13 and tap F11.
Rename the Chart1 tab to: Asset Chart – 2028
Click in the chart area and choose Chart Design→Data→Select Data, select the ranges C8:C13 and K8:K13, and click OK.
Click in the chart area; choose Chart Design→Chart Layouts→Add Chart Element→Chart Title→Centered Overlay and replace the default title with: Asset Percentages – 2028
Create Different Chart Types
Choose Chart Design→Type→Change Chart Type.
Switch to the All Charts tab if necessary, choose the Pie category and the 3-D Pie type, and click OK.
Choose Chart Design→Data→Select Data, select the ranges C8:C12 and K8:K12, and click OK.
Choose Chart Design→Chart Layouts→Add Chart Element→Legend→Bottom and then click the Chart Elements button and choose Data Labels→Outside End.
Double-click the chart area; in the task pane, click Effects and choose 3-D Rotation→Y Rotation and change the existing entry to: 30
Double-click the chart title; in the task pane, choose Fill & Line→Fill→Solid Fill and choose Yellow from the color palette.
Click the Cash pie slice twice and drag it away from the center of the pie chart.
Switch to the Horizontal Analysis tab, click in the chart area, choose Chart Design→Type→Change Chart Type, choose Clustered Bar from the Bar category, and click OK.
Choose Chart Design→Chart Layouts→Add Chart Element→Gridlines→Primary Major Vertical.
Apply Chart Layouts, Chart Styles, and Sparklines
Click the Chart Styles button and choose Style 2.
Switch to the Asset Chart – 2028 tab and, with the chart selected, choose Chart Design→Chart Layouts→Quick Layout→Layout 6.
Choose Chart Design→Chart Styles→Style 6.
Double-click a data label; in the task pane, navigate to Label Options→Label Options→Number and choose Percentage (in the Category section).
Switch to the Vertical Analysis tab, select the range N8:N12, and choose Insert→Sparklines→Line Sparkline.
Select the range K8:L12 and click OK.
Click cell N15, choose Insert→Sparklines→Line Sparkline, highlight the range K15:L15, and click OK.
Select cell N17, choose Insert→Sparklines→Line Sparkline, highlight the range K17:L17, and click OK.
Highlight the range N2:N19, choose Home→Font→Border →No Border, and then apply an outside border to the range B2:N19.
Type Sparkline Trends in cell N6 and then apply bold formatting, center alignment, and text wrapping.
Calculate Ratios and Insert Cell Comments
Create a new worksheet tab named: Ratio Analysis
Enter 2028 in cell B1 and 2027 in cell C1.
Highlight the range B1:C1 and drag the fill handle through cell F1.
Apply center alignment, bold formatting, and a bottom border to the range B1:F1.
Enter the following into the range A2:A4 in the order shown:
Current Ratio
Equity Ratio
Profit Margin
Format the range with bold formatting and set the column width to: 12
In cell B2, type ＝( and switch to the Vertical Analysis tab, click cell G8, type + and click cell G9, type + and click cell G10, type + and click cell G11, and then type )/ and click cell G15; tap Enter.
Repeat step 42 in cell C2 using the 2027 figures.
In cell B3, type ＝ and switch to the Vertical Analysis tab, click cell G17, and type / and click cell G13; tap Enter.
Repeat step 44 in cell C3 using the 2027 figures.
In cell B4, type ＝ and switch to the Horizontal Analysis tab, click cell G16, and then type / and click cell G8; tap Enter.
Repeat step 46 in cell C4 using the 2027 figures.
Highlight the range B2:F3 and choose Home→Number→Comma Style; then apply the Percent Style to the range B4:F4 and choose Home→Number→Increase Decimal.
Enter this data:
Cell D2 0.28
Cell E2 0.30
Cell F2 0.57
Cell D3 0.32
Cell E3 0.21
Cell F3 0.26
Cell D4 .086
Cell E4 .234
Cell F4 .119
CHECK FIGURE Cell B2 ＝ 0.40
Select the range G2:G4, choose Insert→Sparklines→Column Sparkline, select the range B2:F4, and click OK.
Select cell E3, choose Review→Comments→New Comment, and post this comment: Investigate this decrease.
Right-click cell C4, choose New Comment, and then post the comment: Why did profit decline?
Right-click cell C4, choose Reply to Comment, and then post the response: Temporary economic slowdown.
Select cell A1 to hide the cell comment.
Save and close the file