Практичне завдання №5

MICROSOFT EXCEL

Використання методики планування.
Логічні функції в розрахунках. Сценарії та підбір параметру

Хід роботи:

1. Відкрити з персональної папки робочий файл.
2. Добавити новий лист, перейменувати його на “Баланс” та перемістити на останнє місце в списку листів (див. завдання №4).
3. На листі “Баланс” створити таблицю за наступним зразком:

* клітинки, відмічені зеленим кольором, містять формули з посиланням на інші листи робочої книги;

* клітинки, відмічені жовтим кольором, містять формули, що використовують дані цього ж листа;

* клітинки, відмічені жовтогарячим кольором, містять остаточні результати;

* в білі клітинки дані будуть заноситися вручну.

1. Використовуючи формули з посиланням на листи “Доходи”, “Витрати”, “Розрахунок ЗП” заповнити клітинки рядка “Факт”, відмічені зеленим кольором:

- - виділити клітинку для рядка “Факт” по “Сумі реалізації(з ПДВ)”;

- - перейти на лист “Доходи”;

- - виділити стовпчик “Сума реалізації з ПДВ” натиснувши на його заголовку (J). Як результат, увесь стовпчик виділиться темним кольором;

- - натиснути “Enter”;

- - записати формулу “=СУММ(Доходи!J:J)”;

* в результаті для даної клітинки буде створена наступна формула “=СУММ(Доходи!J:J)”, яку можна було б ввести з клавіатури;

* запис Доходи!J:J означає, що дані беруться з листа “Доходи” по стовпчику J незалежно від кількості рядків, причому нечислові значення виключаються.

- - аналогічно для клітинки “Сума закупки з ПДВ” створити формулу з посиланням на лист “Доходи”;

- - для клітинки “Виробничі витрати” створити формулу з посиланням на лист “Витрати”;

- - для клітинки “Зарплата” створити формулу з посиланням на лист “Розрахунок ЗП” на клітинку загальної суми “Нараховано всього”.

1. Провести розрахунки для ПДВ:

- - “ПДВ в сумі закупки” складає 20% від “Суми закупки з ПДВ” (=F6/6);

- - “ПДВ в сумі реалізації” складає 20% від “Суми реалізації з ПДВ” (=В6/6);

- - “ПДВ до сплати” – це різниця “ПДВ в сумі реалізації“ та “ПДВ в сумі закупки”.

1. Розрахувати нарахування на заробітну плату, яке умовно складає 38% від загальної суми заробітної плати.
2. Підрахувати “Витрати всього” як суму всіх витрат (виділені червоним кольором).
3. Розрахувати “Балансовий прибуток” як різницю між доходами та витратами.
4. Розрахувати “Податок на прибуток”, який складає 30% від “Балансового прибутку”.
5. Розрахувати “Чистий прибуток” як різницю “Балансового прибутку” та “Податку на прибуток”.
6. Провести розрахунки для рядка “План” (ці значення є запланованими відносно прогнозованих даних – суми закупки з ПДВ та виробничими витратами).

- - поставити заплановані дані для “Суми закупки з ПДВ” та “Виробничих витрат” (наприклад, 40 000,00 та 1 500,00 відповідно);

- - розрахувати суму реалізації за планом використовуючи заплановану суму закупки (=(F5+F5*націнка)*120%);

- - розрахунки по ПДВ провести аналогічно рядку “Факт”.

1. Зарплату розрахувати, посилаючись на фонд заробітної плати (загальна сума) листа “Штат”:

- - перейти на лист “Штат” та виділити клітинку з загальною сумою;

- - присвоїти ім’я “фонд_зарплати” цій клітинці (див. завдання №4);

- - перейти на лист “Баланс”;

- - для стовпчика “Зарплата” рядка “План” поставити посилання на щойно створену пойменовану клітинку (=фонд_зарплати);

- - натиснути Enter.

1. Решту незаповнених клітинок розрахувати у відповідності до рядка “Факт”.
2. Встановити умовне форматування для клітинки “Чистий прибуток” по факту (при умові, що прибуток буде від’ємним, цю клітинку відобразити на червоному фоні жовтими літерами):

- - вибрати необхідну клітинку (М6);

- - вибрати пункт меню “ФорматÞУсловное форматирование...”;

- - у вікні “Условное форматирование” вказати необхідні параметри для випадку прибутку<0, як показано на рис. нижче:

* для встановлення кольорів необхідно скористатися кнопкою “Формат...”.

1. В клітинці С8 встановити логічну функцію для виведення повідомлення про збиток (=ЕСЛИ(M6<0;"Увага!!! Збиток!!!";"")).
2. Використовуючи засіб “Подбор параметра”, підібрати таке значення планової суми закупок, щоб отримати чистий прибуток в розмірі 5 000, 00 грн.:

- - попередньо присвоїти імена клітинкам F5, I5 та M5 відповідно “план_закупок”, “план_витрат” та “плановий_прибуток”;

- - вибрати необхідну клітинку (М5);

- - вибрати пункт меню “СервисÞПодбор параметра...”;

- - у вікні “Подбор параметра” встановити значення та посилання на відповідні клітинки, так, як зображено на наступному малюнку:

* М5 – клітинка планового чистого прибутку;

* 5000 – бажане значення чистого прибутку;

* F5 – клітинка планової суми закупок з ПДВ.

1. Самостійно підібрати значення для прибутку в 10 000, 00 грн.
2. Створити декілька альтернативних планів діяльності підприємства, використовуючи засіб “Сценарії”:

- - вибрати пункт меню “СервисÞСценарии...”;

- - у вікні “Диспетчер сценариев” натиснути кнопку “Добавить...”;

- - у вікні “Добавление сценария” вказати ім’я нового сценарію та клітинки, які будуть змінюватися;

- - натиснути “Ок”;

- - у вікні “Значения ячеек сценария” вказати значення, які будуть підставлятися у відповідні клітинки (сума закупки з ПДВ та виробничі витрати):

- - натиснути кнопку “Ок”;

- - аналогічно створити ще два сценарія для будь-яких значень.

* для підстановки підібраних у сценарії значень в таблицю, необхідно у вікні “Диспетчер сценариев” вибрати з переліку необхідний сценарій та натиснути “Вывести”:

* для знищення створеного сценарію – натиснути кнопку “Удалить”;

* для зміни існуючого сценарію – натиснути кнопку “Изменить...”.

1. Для листа “Штат” самостійно створити 5 різних сценаріїв для планування фонду заробітної плати.
2. Створити підсумковий звіт по сценаріях альтернативних планів для листа “Баланс”:

- - вибрати пункт меню “СервисÞСценарии...”;

- - у вікні “Диспетчер сценариев” натиснути кнопку “Отчет...”;

- - встановити необхідні параметри у вікні “Отчет по сценарию” (тип – структура, клітинка результату – $М$5):

- - натиснути “Ок”.

· як результат буде створений новий лист з звітом по сценаріях.

Поиск по сайту: