Create a financial format and analysis.

PROJECT STEPS

Kamal Haman runs the San Antonio office of Cello Worldwide, a global communications company. He’s been tracking income and expenses along with client data in an Excel workbook, including charts to help him visualize the data. He’s asked you to help him complete the workbook and insert additional charts. Go to the Income and Expenses worksheet . In cell K1, insert a formula using the TODAY function to display today’s date.

Fill the range D4:F4 with a series based on the value in cell C4 to provide the missing month names.

Format the text in cell A4 as follows to make it readable and more meaningful:

Merges and centers the contents of the range A4:A17.

Rotate the text in the merged cell 90 degrees so that it reads from bottom to top.

Middle align the merged cell.

Resize column A to a width of 6.00 .

Use AutoFit to resize column B to the best fit to display all types of income and expenses.

Complete the calculations for the Income data as follows:

In cell C7, enter 45,600 as Business Services Revenue for September.

In cell C8, use the SUM function to total the revenue values for September.

Copy the formula from cell C8 to the range D8:F8 and to cell H8 to complete the totals.

Format the nonadjacent ranges C13:F16 and H13:H16 using the Comma style and no decimals to match the format of the Income data.

Kamal wants to display the highest and lowest income amounts from September to December. Enter and format this information as follows:

In cell C24, enter a formula using the MIN function to display the lowest income in the range C5:F7.

In cell C25, enter a formula using the MAX function to display the highest income in the range C5:F7.

Apply Outer Borders to the range B24:C25 using Black, Text 1, Lighter 35% as the border color to show that the information is joined together.

In the clustered column chart in the range J3:P17, Kamal wants to display expenses by type, not by month. He also wants to clarify the content of the chart. Provide this information for him as follows:

Change rows and columns to display expenses by type.

Move the legend to the right side of the chart.

Add Monthly Amount as the title for the main vertical axis.

Add Expenses by Type as a chart title.

Change the fill color of the December data series to Dark Red, Accent 1 .

Add a chart border using shape outline color Black, Text 1, Lighter 50% .

Kamal wants to include a graph showing the monthly profits for the San Antonio office to determine which months have been most favorable. Create a new graph as follows:

Create a donut chart based on the range C21:F22.

Resize and position the chart so that its upper left corner is inside cell J19 and its lower right corner is inside cell P31.

Enter September-December Earnings as the chart title.

Apply Layout 6 to the chart to show percentages for each part of the donut.

Kamal also wants to include a chart showing revenue from mobile phones, wireless services, and business services. Create and format a chart for him as follows:

Cree un grfico de columnas apiladas basado en el rango B4:F7.

Mueva el grfico a una nueva hoja denominada Grfico de ingresos .

Cambie el estilo del grfico al Estilo 7 para que coincida con el estilo del grfico de columnas agrupadas en Ingresos y gastos. hoja de clculo.

Cambie el tamao de fuente de todo el texto del grfico a 14 puntos para que sea ms fcil de leer.

Elimine el ttulo del grfico, ya que la pestaa de la hoja indica el propsito del grfico.

Clarifique los datos del cuadro de la siguiente manera:

Formatee los valores en el eje vertical usando el formato de nmero de contabilidad sin decimales para aclarar que los valores son montos en dlares.

Agregue una tabla de datos con claves de leyenda al grfico para mostrar los valores de ingresos.

Elimine la leyenda, que ahora es redundante.

Kamal quiere realizar un seguimiento de las tendencias para cada tipo de ingresos y gastos y para el anlisis de beneficios. Proporcione esta informacin para l de la siguiente manera:

Vaya a la hoja de trabajo de Ingresos y Gastos . En la celda G5, inserte un minigrfico de lnea basado en los datos del rango C5:F5.

Incluya marcadores en el minigrfico y luego cambie el color del marcador a Negro, Texto 1 .

Copie la celda G5 y luego pguela en el rango G6:G8, el rango G12:G17 y la celda G22.

Vaya a la hoja de trabajo Anlisis de clientes comerciales , que recopila datos sobre las cuentas comerciales de Texas que maneja Kamal. Calcule la cantidad de aos que un cliente ha estado con Cello Worldwide de la siguiente manera:

En la celda E5, ingrese una frmula sin usar una funcin que reste la fecha de inicio del cliente de la fecha actual y divida el resultado por 365,25 , el nmero de das de un ao, teniendo en cuenta el ao bisiesto.

Utilice una referencia absoluta a la celda C2 en la frmula.

Muestre el valor en la celda E5 con un decimal.

Complete el rango E6:E18 con la frmula de la celda E5.

Cello Worldwide ofrece un descuento a los clientes que han estado en la empresa durante al menos cuatro aos. Determine si cada cliente califica para un descuento de la siguiente manera:

En la celda H5, ingrese una frmula usando la funcin SI que pruebe si la cantidad de aos es mayor o igual a 4 . Si es as, muestre "Y" en la celda H5. Si no es as, muestre "N" en la celda H5.

Complete el rango H6:H18 con la frmula de la celda H5.

Kamal planea ofrecer contratos nuevos y ms favorables a los clientes comerciales que ahora reciben un descuento y utilizan servicios inalmbricos. Determine si cada cliente debe recibir un nuevo contrato de la siguiente manera:

In cell I5, enter a formula using the AND function that tests whether the wireless value equals "Y" and whether the discount value equals "Y" .

Complete the range I6:I18 with the formula in cell I5.

Kamal also plans to offer a free cell phone to business customers in North Texas who use an unlimited plan. Determine whether each customer should receive a free phone as follows:

In cell J5, enter a formula using the OR function that tests whether the location equals "N" or whether the plan type equals "Unlimited" .

Complete the range J6:J18 with the formula in cell J5.

Kamal wants to display the total number of business customers. In cell M4, enter a formula using the COUNTA function to count the customer IDs.

Kamal wants to determine the average number of years customers have been with Cello Worldwide. In cell M5, enter a formula using the AVERAGE function to average the number of years.