Chapter 3 Case Problem 4: ROSEY'S ROSES

You are to create a financial analysis for Rosey' s Roses as of December 31, 2022,  and December  31, 2023. Following the Chapter 3 examples,use  the  student file ch3-07.xls  to create a vertical analysis of the balance sheet and income statement as of December  31, 2022,  and December 31,  2023-as well as a horizontal  analysis   of  both  the  income  statement  and  balance  sheet  comparing December   31,  2022,  with  December  31,   2023.  Place  the  vertical  analysis in columns  D and  G on the income  statement labeled o/o   of Sales in cells DS  and GS.  Place  the vertical  analysis in columns D and G on the balance sheet labeled %  of  Assets  in  cells  DS   and  GS.   Place  the  horizontal  analysis  in  column  l labeled  % Change  in cell IS for both the income statement and balance  sheet.

Also  create  a pie  chart  of expenses  for the year  ended  December 31, 2023, formatted in a manner similar  to your chapter  work;  a column  chart  of expense for the years  ended  December 31, 2022,  and December  31,  2023,  formatted in a manner  similar  to your  chapter  work;  and  a ratio  analysis   as  of  December   31 2023.  Note:  You will have to use Excel's  help  feature  to  crea~e the  colurn~ chart, because the columns are not  adjacent  to  one  another   as  in  the  chapter example. Use whatever chart layout you prefer.

Save the workbook as ch3-07_student_name   (replacing   student_name   With your name). Print  all  worksheets  in  Value  view,  with   your   name   and  date printed in the lower left footer and the file name in the lower  right  footer.                                                                                 

2   Navigate ~e  Open window to the location of this  text's student  files. (The location would be wherever you saved  the downloaded  student files from Cengage or from your computer  lab's  server.)

3   Double-click  the file ch3-01,  which should be located   in  a Ch 03 folder.   (You may have to click Enable Editing before you begin.)

4   Click the Income Statement tab if it is  not already  active.

5   Right-click  column C and click Insert to insert  a new column.

6   Click cell C4 and type % as a label to the new column.

7   Click cell CS and type the formula =BS/$B$S;  then press [Enter]. (Note: use an absolute reference in the denominator so you can fill down the formula later.)

8 Click cell CS again and reformat the cell by clicking the % tool in the Number group of the Home tab.

9   Resize column C to reveal the value in  cell C5.

10  Fill down the formula in C5 to C 16 by dragging the handle in the lower right corner of cell C5 to Cl6.  (Note:  this  will also copy down the formatting from cell C5, removing border formats that you can fix later.)

11  Eliminate the formula from cell C8 by clicking the cell and pressing

[Delete],  then [Enter].

12  Add appropriate border formats to cells C6,  Cl3,  and Cl6.

13 Repeat Steps 6 to 11  in column E,  substituting  E for C in all column references.  Substitute D for B in the formulas.  Your worksheet should look like Figure  3 .1.

14  Click File again and then click Save As.  (This preserves your original student and provides a unique name for your file.)

3    Click cell 04  and type % Change  as a label to the column.

4   Format cell 04  bold, center, bottom align, and with a bottom border.

5    Click ceJl 05 and type the formula =(C5-BS)/BS; then press [Enter].

6   Format cell 05  as Percent Style by clicking cell OS and selecting the Percent  Style tool on the Number group on the Home tab.

7    Use the fill-down procedure to copy the formula from 05  to 017.

8    Delete the formula in cell 08.

9   Format cells 06  and D 14 with a bottom border.

10   Format cell 017  with a top and double bottom border.

11   Resize column D to 96 pixels. Your completed  income  statement with horizontal  analysis should look like Figure  3.8.

12   Click File and then click Save As. (Again, this preserves the original file by giving a unique name to your file.)

13   Change the file name to include a reference to your horizontal analysis, and then add your name to the file name using the underscore key as before. In this example, the file will be saved as

ch3-01_Horizontal_Analysis_student_name (replace student_name with your name). Save your file to a location from which it can be retrieved later.

14   Switch to a Page Layout view. Place your name in the left section of the footer. Note that the file name is already in the right section of the footer.

15   Switch back to a Normal view.

To calculate the first financial ratio on a new worksheet (refer to preceding ratio table for the  return on owners' investment  formula):

1     Right-click the Income Statement tab of your workbook and click Insert ...;  then click Worksheet  and then OK to insert a new worksheet.

2    Double-click the Sheetl  tab and rename the worksheet Ratio  Analysis.

3    Format the Ratio Analysis worksheet like Figure 3.14 with a heading and listing of ratios as shown. To center and merge the title, select the range of cells Al:Bl  and then click the Merge and Center button from the Alignment  group on the Home tab of the Ribbon.  Repeat these steps for the ranges A2:B2  and A3:B3.

4    Click in cell B6 and type the following  ='Income  Statement'!C17/ (('Balance Sheet'!B26+ 'Balance  Sheet'!B27 + 'Balance Sheet'!C26+'Balance Sheet'!C27)/2) to compute return on owners'  investment.

5    Alternatively,   you could  utilize  multiple  worksheet  referencing. Type  = in cell B6, then  activate  the income  statement  worksheet  and click  cell Cl 7.

6    Type/((   (the forward  slash and two left parentheses,  which determine the all-important  order  of calculation).

7   Activate  the balance  sheet worksheet  and click cell B26.

8   Type  +,  then click cell C26.

9   Type  +,  then click cell B27.

1 O     Type  +, then  click cell C27.

11   Type )/2) to end the summation of equity accounts and divide the result by 2 to derive an average.

12   Press [Enter] to end your formula.

13   Format cell 86 in a percent style.

Tr o u b I e ?  If you choose to type the formula as written here and forget an apostrophe or exclamation point or misspell a word,  then Excel will give you an error message  and you'll have to debug your  formula.  Also, if you don't have the correct number of parentheses, Excel will give you an error message.  Note that when you edit the formula, Excel changes the color of each set of parentheses so that you can clearly see which is which. Also be careful when typing formulas to use the apostrophe and not the accent key to create formulas.  The apostrophe key is usually located next to the Enter key on your keyboard, and the accent key is usually located above the Tab key.

'.'The point-and-click method of cell referencing  on multiple  worksheets  is  sure easier than typing the references  themselves,"  you comment. "Otherwise I would have  to type all of those  apostrophes  and exclamation  points.  With  my typing skills,  I would Probably forget one of them and create an error in the formula." Good point,  Kyle says. "Let's finish  the profitability  ratios next."

3  Use the values at cells B 17 and C 17 on the balance  sheet to add total assets, and then divide the result by 2 to obtain average total assets.

4    Once your formula  is typed,  press  [Enter]  to compute  the ratio.  Your formula  should look like this:  =('Income Statement'!C17+'1ncome Statement'!Cll)/(('Balance Sheet'!B17+'Balance  Sheet'!C17)/2). Be careful to use the correct  parentheses.

5   Format the cell in percent style.

6    In cell BB,  use the values at cells C 17 and CS on the income  statement to compute the profit margin. (Use the ratio table to enter the appropriate formula.) Your formula should look like this: ='Income Statement'!Cl 7/'IncomeStatement'!CS.

7  Format  the cell in percent style.

8    In cell B9, use the values  at cells C7 and C5 on the income statement to compute  the gross margin. (Use the ratio table to enter the appropriate fomula.) Your formula  should  look like this:  = 'Income Statement'!C7/ 'Income Statement'!CS.

9  Format the cell in percent style. Your window should look like Figure 3.15.

10   Click File then click Save As.

11      Change the file name to include a reference to ratios, and then add your name to the file name using the underscore  key as before.  In this example,  the file will be saved as ch3-0 l_Ratios_student_name (replace student_name with your name).  Be sure to save your file to a safe location.