Ejercicio Practico Excel Basico

7/23/2019 Ejercicio Practico Excel Basico

1/16

Venta de Vehiculos

con Excel


2/16

EJERCICIO PRACTICO ON ESIONARIA DE VEHI ULOS ON FINAN IA ION

PROPIA

Interesante ejercicio practico, donde aparte de aplicarse funciones financieras, ya explicadas enel presente curso, se emplearan una serie de recursos que Excel posee como el caso deFormularios, donde se podr ver las aplicaciones de: Cuadro combinado, Casilladeverificacin, Barra de desplazamiento, Control de nmeros.Todo lo expuesto, complementado con la combinacin de funciones de Informaciny Lgicaque impiden que Excel devuelva error en caso de valores no encontrados etc.Otro dato interesante es que se relacionan dos ojas de calculo, una para muestra y desarrollode operaciones y la otra encar!ada de suministrar los datos."or ultimo se aplicara, proteccin de la hoja de calculo, proteccin de celdas yocultamiento de formulas

#omen$ando con elejercicio, en primer lu!ar se deber tener un formulario que permita ante el requerimiento de unusuario, ubicar el modelo, cilindrada y costo, como lo muestra la si!uiente ima!en

"ara lo!rar el objetivo en primer lu!ar se debe contar con una base o lista de datos, queconten!a el total de modelos fabricados que se encuentran a la venta, con su correspondientecosto y cilindrada. "ara eso se confecciono una oja con el nombre de atos, que ser la

encar!ada de transferir todos los datos a la %oja principal de !uestra y Calculo&a planilla que se confecciono es la si!uiente

%oja " atos"

B C # F

$ 'F D

()&*O +

% Modelo

Cilind& 'recio +

( $ +

) % *a Base $&+ $+&)+,++ +

- ( *a 'lus $&+ $%&$+,++

. ) *a 'lus $&. $$&+,++

/ - *a 'lus $&. $(&.+,++0 . *a 12 3port $&. $)&0)+,++

/ *a Blac4 $&. $-&)+,++

$+ 0 Fiesta L1 (p& $&. $$&+,++

$$ Fiesta L1 -p& $&. $%&$+,++

$% $+ Fiesta L1 -p& $&0 $(&-+,++

$( $$ Fiesta CL1 !otor $&. L $&. $)&%0+,++

$) $% Fiesta CL1 !otor $&. L 3port $&. $.&%(+,++

$- $( Fiesta CL1 $&0 $-&/+,++

$. $) Ford 5a6i 7afta )p& $&. $%&+,++

$/ $- Ford 5a6i iesel )p& $&0 $)&/++,++

$0 $. #scort Cross 7afta -p& $&. $%&+,++

$ $/ #scort Cross 5I -p& $&0 $)&/+,++


3/16

%+ $0 #scort L1 7afta -p& 'lus $&. $)&.+,++

%$ $ #scort L1 5I -p& 'lus $&0 $.&)++,++

%% %+ #scort CL1 7afta $.v -p $&0 $.&(++,++

%( %$ #scort CL1 5I $.v -p $&0 $0&.++,++

%) %% #scort 3tation 8agon 7afta 'lus $&. $-&(++,++

%- %( Focus L1 7afta $.v -p $&0 $/&/+,++%. %) Focus CL1 7afta $.v -p $&0 %+&%++,++

%/ %- Focus L1 5I -p $&0 %+&+,++

%0 %. Focus 9hia 7afta $.v -p %&+ %%&%++,++

% %/ Focus 9hia 7afta $.v )p %&+ %(&)++,++

(+ %0 Focus 9hia 5I -p $&0 %%&-++,++

($ % Focus 9hia 5I )p $&0 %(&/++,++

(% (+ !ondeo 9hia 7afta $.v )p %&+ (+&/++,++

(( ($ !ondeo 9hia 7afta :. %)v -p& %&- (/&++,++

() (% !ondeo 9hia 5I $.v )p %&+ (%&.++,++

(- (( #6plorer )6) Limited )&+ ))&-$/,++

(. () #6plorer )6) 3port )&+ (&%$(,++(/ (- #6plorer 1L5 )6% )&+ ($&($+,++

(0 (. #scape )6% 1L3 %&+ %&.+,++

( (/ #scape )6) 1L3 %&+ ($&/+,++

)+ (0 #scape )6) 1L5 %&+ (-&%+,++

)$ ( Courier :an $&0 $)&%++,++

)% )+ Courier 'ic4;up iesel $&0 $(&%-+,++

)( )$ Courier 'ic4;up iesel


4/16

#on la oja atos podemos empe$ar a fabricar la primer parte del formulario que se mostr enforma de ima!en. #reamos en primer lu!ar una $ona de ran!o )':-'+.

< B C # F 9

$

%

( FORD

)

-

.

/ Modelo Cilin Precio

0

$+

En la celda # incluimos (O/ con fondo de celda rojo en este caso y letras blancas.

#olocamos en las celdas 0 !odelo, E0 Ciliny en (0 'recioTeniendo dia!ramado la primer parte del (ormulario, comen$amos con la tarea de incluir en 1l,los elementos que permitan su funcionamiento.En primer lu!ar colocaremos un Cuadro combinado, para ejecutarlo, se!uir los si!uientespasos.$; !en;:er%; *e desplie!a una lista, pulsar en Barra de herramientas(; *e desplie!a una se!unda lista, pulsar sobre Formularios, obteni1ndose la si!uiente

ima!en de pantalla #asilla de verificacin

2arra de despla$amiento

#ontrol de n3meros #uadro combinado

45 "ulsar sobre Cuadro combinado, indicado por la fleca, arrastrar con el puntero del mouse,transformndose en una peque6a cru$. &levar asta la celda )creando un rectn!ulo de)@-)l reali$ar esta operacin se transforma el rectn!ulo en un #uadro similar al presentado en laima!en al inicio del ejercicio.Obtenido el cuadro respectivo, corresponde aora reali$ar las vinculaciones correspondientespara el funcionamiento del mismo, entrando en actividad la oja auxiliar atos&

#jecucin'5 #lic sobre el #uadro combinado presionando la tecla dereca del mouse75 *e desplie!a una lista, pulsar sobre Formato de control


5/16

5 )parece una serie de casillas para llenar con t8tulos a la i$quierda

2ango de entrada atosAC(@C-0

:incular con la celda atosAB$

Lneas de unin verticales 0

Tildar 3ombreado (

' reservado a laprimer fila que se encuentra en blancoEs decir los modelos van desde el =>7 al => 9?."or otra parte en la celda correspondiente a:

:incular con la celdase a completado con atosAB$& Es decir la celda que se a vinculadoen el #uadro combinado es la celda B$correspondiente a la oja atos&En esa celda el #uadro combinado volcara un numero que ser coincidente con la ubicacindel modelo en el listado, es decir si nos ubicamos en el listado desple!able de la ima!en,Escort #&@ =afta '?v 9p, podemos observar que se encuentra ubicado en el lu!ar 7+ a partirde la primer fila que se encuentra en blanco.Ese numero es el que el #uadro combinado ubicara en la celda 2', dando por terminada sutarea.El prximo paso, ser resolver la incorporacin en las celdas A, EA y (A, de los datoscorrespondientes al modelo ele!ido."ara solucionar esto se emplea la funcin 2B*#)/C, corresponde al !rupo de funciones de23squeda y /eferencia.#elda DBE3C


6/16

observar, se encuentra en la columna 2, y en este caso en la celda A vuelca el dato de lase!unda columna, el resto de las celdas o sea #y F cambian solamente la ubicacin decolumna respecto a los datos.#elda #DBE3C


7/16

En las celdas combinadas 2'9, #'9 y '9 Financiamiento#elda ('0 #on recuadro#eldas 27+, #7+ Importe#elda 7+ #on recuadro#elda 27', #7'


8/16

Control#ompletar el casillero donde se especifica.:incular con la celda atosA#$

)ctivar 3ombreado (

*e a relacionado la #asilla de verificacin con la celda E' de la oja atos, esta casilla al estaractivada devuelve :#2< ;FJ3I=#$(DatosA#$+$>>Esta formula tiene dos funciones combinadas de informacin E*E//O/ y l!ica *I.En primer lu!ar se resuelve una parte con la funcin *I es decir:#elda%+DFJ3I=#$(DatosA#$+$>)qu8 se establece que el costo del ve8culo que se encuentra en la celda F, si la #asilla deverificacin no se encuentra activada y devuelve F


9/16

previsto un mximo de 9 a6os, se incorpora una Barra de desplazamiento en la celdareservada F%(El procedimiento es el mismo que el empleado al incorporar el #ontrol de n3meros, es decircon el puntero del mouse se ubica en al celda y se le da el tama6o dentro de la misma."ulsar con el botn dereco del mouse sobre la 2arra de despla$amiento.*eleccionar Formato de control

Control

:alor mnimo +:alor mM6imo )++Incremento $Cambio de pagina $+

:incular con la celda atosAF%)ctivar 3ombreado (#on esto se consi!ue la vinculacin con la celda (7 de la oja atos que recibe los valores

emitidos por la barra de despla$amiento.

Esta celda se vincula con la celda 7 del (ormulario de la si!uiente manera.

#elda %(D atosAF%?$+++Obteni1ndose valores porcentuales de + a 4+K con intervalos de +,'+K

#elda %), en este caso se recurre nuevamente a colocar un Control de nmerosen lasceldas reservadas en #%) y #%-& *e si!ue el mismo procedimiento se!uido anteriormente.Formato de controlControl:alor mnimo +:alor mM6imo -Incremento $

:incular con la celda atosAF(

Este 2otn traslada a la celda atosJL(L valores que se extienden asta 9 a6os conincremento de '"or lo tanto la celda 74 se vincula con la mencionada celda de atos e incorpora el numeroque en ella se encuentra.#omo ya se menciono con un mximo de 9 a6os, e incrementos de a ' a6o#elda %)D atosAF(

#elda %., en esta celda se colocan los meses en caso de que la financiacin no se computecomo a6o entero, por ejemplo 7 a6os y medio, en este caso se indica en la celda la fraccin de? meses.Empleamos en este caso una Barra de desplazamiento, que se ubica en la celda #%.reservada.*e si!ue el mismo procedimiento y se completa en:Formato de controlControl:alor mnimo +:alor mM6imo $%Incremento $

:incular con la celda atosAF)"or lo tanto la celda 7? se la vincula con la celda atosJL(L4#elda %.DatosAF)

#elda F$/, en esta celda se calcula el valor de la cuota que mensualmente se deber abonar,producto de la financiacin#elda F$/D 3I=#3#22G2='P%.%%>J;$>N O 'P%.%%>J;$>


10/16

&a formula esta compuesta por la combinacin de la funcin (inanciera ")-O, que es la que

calcula la cuota, y la funcin de Informacin E*E//O/, esta ultima evita en caso de no existir

datos en el formulario que Excel devuelva error tipo HICM+J

*e multiplica la formula por N', para que el resultado devuelto sea positivo.

#elda %, en esta celda se coloca la feca en que deber abonarse la cuota => 'e funcionar correctamente todo lo expuesto, asta la ultima celda del (ormulario, el

procedimiento de aplicacin de la financiacin respectiva, se ejecutara en forma automtica a

trav1s de los controles incorporados, siendo la celda 7A la 3nica que recibir una car!a en

forma manual

:erificacin del #jercicio

*e mantiene el ve8culo de la ima!en

Godelo #scort CL1 7afta $.v -p #ilindrada $,0 "recio $.&(++,++

*e incorpora un anticipo de $-Q

Bna tasa anual del $),.+Q

"la$o de (inanciamiento % aHos y 0 meses

El resultado obtenido es el si!uiente

< B C # F 9


11/16

$

%

( FORD

)

-

.

/ Modelo Cilin Precio

0

#scort CL1 7afta $.v -p $&0 $.&(++,++

$+

$$ #jecucin de la operacin

$% Contado

$( Financiado

$)

$-FINAN IAMIENTO

$.

$/ -%-,(+

$0

$

%+ Importe $.&(++,++

%$ Anticipo $-Q %&))-,++

%% Saldo a Financiar $(&0--,++

%( Tasa $),.+Q

%) Plazo en aos %

%-

%. Fraccin meses 0

%/ Periodicidad $%

%0

% Fecha de Cuota N 1 /;ago;+%

(+

*e puede apreciar la ima!en terminada de la totalidad del formulario


12/16

*e a resuelto asta el momento todo lo relacionado a la financiacin, en lo que respecta a la

parte informativa !eneral es decir #osto5financiacin5pla$o5cuota.

El paso si!uiente es calcular la amorti$acin y el inter1s de cada cuota a abonar por lafinanciacin, que como se expreso tiene un mximo de 9 a6os o sea ?+ cuotas

"ara reali$ar la operacin se confecciono una planilla que se llena automticamente desde elformulario creado, transfiriendo => de cuotas, y reali$ando calculo de vencimiento de cadacuota, calculo de amorti$acin e inter1s de las mismas, composicin de la cuota se!3n => yfeca de la misma.

El llenado de la planilla se mostrara completo de acuerdo al calculo ya efectuado en elformulario, se!3n modelo ele!ido y forma de pa!o, explicando lue!o su ejecucin en cuanto alas funciones empleadas y su combinacin.

)barcara la planilla en este caso 7 cuotas que son las que corresponden a los 7 a6os y ocomeses de financiacin, pero las funciones que se aplican y su combinacin permitirn lle!adoel caso ocupar 1l limite de la misma o sea ?+ cuotas D9 a6osF

'lanilla de desarrollo de la financiacin cuota por cuota

*e ocultaron desde la fila asta la 90 por ra$ones de tama6o, al no emplearse en el ejemplo.

R I S * L !


13/16

$ Financ.Ma

N decuota

Fecha Amortizacin Inter!s Cuota

% $ $ /;ago;+% ; (-.,/) ; $.0,-/ ; -%-,(+

( % % /;sep;+% ; (.$,+0 ; $.),%( ; -%-,(+

) ( ( /;oct;+% ; (.-,)/ ; $-,0) ; -%-,(+

- ) ) /;nov;+% ; (.,% ; $--,( ; -%-,(+. - - /;dic;+% ; (/),)% ; $-+,0 ; -%-,(+

/ . . /;ene;+( ; (/0,/ ; $).,(( ; -%-,(+

0 / / /;feb;+( ; (0(,-0 ; $)$,/% ; -%-,(+

0 0 /;mar;+( ; (00,%- ; $(/,+. ; -%-,(+

$+ /;abr;+( ; (%,/ ; $(%,(( ; -%-,(+

$$ $+ $+ /;may;+( ; (/,/- ; $%/,-- ; -%-,(+

$% $$ $$ /;jun;+( ; )+%,- ; $%%,/$ ; -%-,(+

$( $% $% /;jul;+( ; )+/,) ; $$/,0$ ; -%-,(+

$) $( $( /;ago;+( ; )$%,)- ; $$%,0. ; -%-,(+

$- $) $) /;sep;+( ; )$/,)/ ; $+/,0) ; -%-,(+

$. $- $- /;oct;+( ; )%%,-- ; $+%,/. ; -%-,(+

$/ $. $. /;nov;+( ; )%/,. ; /,.% ; -%-,(+

$0 $/ $/ /;dic;+( ; )(%,0 ; %,)$ ; -%-,(+

$ $0 $0 /;ene;+) ; )(0,$. ; 0/,$- ; -%-,(+

%+ $ $ /;feb;+) ; ))(,) ; 0$,0% ; -%-,(+

%$ %+ %+ /;mar;+) ; ))0,00 ; /.,)% ; -%-,(+

%% %$ %$ /;abr;+) ; )-),(- ; /+,. ; -%-,(+

%( %% %% /;may;+) ; )-,0/ ; .-,)( ; -%-,(+

%) %( %( /;jun;+) ; ).-,)/ ; -,0) ; -%-,(+

%- %) %) /;jul;+) ; )/$,$( ; -),$/ ; -%-,(+

%. %- %- /;ago;+) ; )/.,0. ; )0,)) ; -%-,(+

%/ %. %. /;sep;+) ; )0%,./ ; )%,.) ; -%-,(+

%0 %/ %/ /;oct;+) ; )00,-) ; (.,// ; -%-,(+

% %0 %0 /;nov;+) ; )),)0 ; (+,0% ; -%-,(+

(+ % % /;dic;+) ; -++,-+ ; %),0$ ; -%-,(+

($ (+ (+ /;ene;+- ; -+.,- ; $0,/% ; -%-,(+

(% ($ ($ /;feb;+- ; -$%,/- ; $%,-- ; -%-,(+

(( (% (% /;mar;+- ; -$0, ; .,($ ; -%-,(+

() (( ; ; ;

(- () ; ; ;

(. (- ; ; ;

(/ (. ; ; ;-0 -/ ; ; ;

- -0 ; ; ;

.+ - ; ; ;

.$ .+ ; ; ;

.% 5G5


14/16

&a primer cuota se ubica en la celda R%olumna 7 de cuotasEn esta columna se ubican automticamente el numero de las cuotas que contemplan el pla$o

de financiacin adoptado, en este caso 7 cuotas D7 a6os y mesesF

#elda I%D3I=$TD==%)J%/>P%.>$+>

#eldaI(D3I=B%D++3I=$PB%TD==%)J%/>P%.>$PB%+>>Esta ultima formula se traslada desde la celda I asta la celda I ?'

#olumna Fecha de vencimiento

#elda S%D% se traslada la feca que se especifico en el formulario como vencimiento de laprimer cuota.

#elda S(D3I=C%D++3I=B(TD+NNF#CR>>&a aplicacin de la funcin (E#%).GE* con el ar!umento ', determina la feca de

vencimiento un mes despu1s

&a formula de la celda P se traslada asta la celda P ?'

#olumna P%.%%>>+ ='>>

&e corresponde la misma explicacin que la celda Q 7&a formula de la celda & 7 se traslada asta la celda & ?'

#olumna Cuota

#elda !%D3I=B%D++'P%.$$>>

En este caso la funcin 3Icondiciona a la funcin '


15/16

Imagen terminada

En este caso, como ultima aplicacin al ejercicio practico, se van a aplicar dos funcionesfinancieras que permiten al seleccionar un periodo de inicio y otro final, calcular entre los

mismos, incluido los adoptados la amorti$acin y el inter1s acumulado, como se muestra en alima!en.&as funciones mencionadas son '


16/16

"esarrollo del #$ercicio dentro de la ho$a de calculo

Formulario principal 'lanilla de desarrollo

Formulario complementario

'roteccin de la hoja de calculo, proteccin de celdas y ocultamiento de formulas

#omo ultimo toquesito al trabajo, vamos a prote!er todo el ejercicio, es decir solamente se va a

permitir in!resar en la celda 7A la feca de pa!o de la primer cuota, el resto se reali$a a

trav1s de los elementos incorporados en formulario y por otro lado, se ocultan todas las

formulas aplicadas en las celdas.

O sea 3nico acceso directo, feca de la primer cuota

' "aso5 clic con el indicador del mouse en el n!ulo superior i$quierdo de la planilla, es deciren el rectn!ulo entre la columna ) y la fila '.#on esto se selecciona toda la oja de calculo donde se encuentra el formulario y la planilla dedesarrollo.7 "aso5!enu5Formato;celda5 seleccionar proteger )ctivar Gculta "aso5 *eleccionar la celda 7A !en;Formato;celda;proteger esactivar bloVueada esactivar Gculta4 "aso !en;herramientas;'roteger *eleccionar 'roteger hoja

)parece un peque6o formulario donde se puede in!resar una contraseHay lue!o

Documents

Ejercicio Practico Excel Basico