2012г.
Книга Экономические расчеты и бизнес-моделирование в Excel. Пикуза предназначена для экспертов, профессиональная деятельность которых связана с расчетами, документооборотом и принятием решений: бухгалтеров, финансистов, экономистов, юристов, менеджеров в области логистики, маркетинга и т. д. Электронные таблицы предоставляют большие возможности для эффективного управления данными, позволяя автоматизировать логистические, производственные, экономические и финансовые расчеты. Цель данного пособия - при помощи практических примеров обучить читателей применять методы и приемы обработки информации, применяя для этого офисные приложения, установленные почти на каждом компьютере. В книжке попутно рассматриваются 2 версии редактора электронных таблиц - Microsoft Office Excel 2003 и Excel 2010.
Оглавление книги
Экономические расчеты и бизнес-моделирование в Excel. Пикуза
Введение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Зачем написана эта книга . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Несколько примеров . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Самый последний пример . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Для кого написана книга . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Глава 1. Организация . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Условия существования предприятия . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Предприятие как организация . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Принцип автоматизации управления . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Этапы автоматизации предприятия . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Человеческий фактор . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Глава 2. Рабочее место специалиста . . . . . . . . . . . . . . . 26
Специалист . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Специалист и его рабочее пространство . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Электронное рабочее место . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Главное — увидеть проблему . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Повышение эффективности труда . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Глава 3. Подготовка к работе . . . . . . . . . . . . . . . . . . . . . . 34
Предварительная настройка команд Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Панель быстрого доступа Excel 2010 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Команды быстрого доступа Excel 2003 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Уровень безопасности . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Возвращаемое формулой значение . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Табличные базы данных (списки) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Фильтрация списка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Сортировка списка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Сортировка по нескольким столбцам или строкам . . . . . . . . . . . . . . . . . . . . . 40
Формулы промежуточных вычислений в списках . . . . . . . . . . . . . . . . . . . . . 43
Применение специальной вставки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Лучший метод одновременного ввода формул в выделенный диапазон
ячеек . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Объекты VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Свойства объектов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Методы объектов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Применение VBA для создания моделей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Терминология . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Глава 4. Оформление и регистрация
командировочных удостоверений . . . . . . . . . . . . . . . . 50
Журнал регистрации командировочных удостоверений . . . . . . . . . . . . . . . . . . . 50
Закрепление областей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Заполнение списка на листе ЖурналРегистрацииКУ . . . . . . . . . . . . . . . . . . 52
Форма командировочного удостоверения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Создание лицевой стороны формы удостоверения . . . . . . . . . . . . . . . . . . . . . 54
Формулы поиска информации . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Обратная сторона командировочного удостоверения . . . . . . . . . . . . . . . . . . 56
Создание элементов управления . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Список . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Счетчик . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Вывод на печать бланка командировочного удостоверения . . . . . . . . . . . . . . . . 60
Вывод командировочного удостоверения на печать
стандартными методами . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Вывод командировочного удостоверения на печать
с помощью макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Вставка содержимого выбранной строки в конец списка . . . . . . . . . . . . . . . . . . 70
Исследование кода VBA макроса вставки строки в Excel 2003 . . . . . . . . . . 70
Исследование кода VBA макроса вставки строки в Excel 2010 . . . . . . . . . . 74
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Глава 5. Формирование и учет
кассовых документов . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Техническое задание . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Состав приложения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Кассовый журнал . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Область табличной базы данных кассового журнала . . . . . . . . . . . . . . . . . . . 80
Приемы автоматизации при заполнении журнала
кассовых документов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Изменение параметров Excel перед записью макросов . . . . . . . . . . . . . . . . . 84
Абсолютная, относительная и смешанные ссылки при записи
и выполнении макроса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Создание процедуры автоматического заполнения журнала . . . . . . . . . . . . . . . 93
Запись кода VBA при вводе формул в процессе записи макроса . . . . . . . . 93
Подпрограмма ввода записи в журнал регистрации кассовых
документов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Пользовательские диалоговые окна . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Автоматизация заполнения данных расходного ордера . . . . . . . . . . . . . . . 107
Расходный кассовый ордер . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Приходный кассовый ордер . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Квитанция к приходному кассовому ордеру . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Глава 6. Учет расхода топлива . . . . . . . . . . . . . . . . . . . 112
Схема потоков информации при вводе и обработке данных
по путевым листам . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Состав приложения . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Рабочий лист Модель . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Рабочий лист Водители . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Рабочий лист Нормы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Рабочий лист БазаДанных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Рабочий лист ВводДанных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Создание таблицы ввода данных
на листе ВводДанных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Область ввода и расчета обязательных параметров . . . . . . . . . . . . . . . . . . . 121
Ввод данных по автомобилю и водителю . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Поиск информации по модели автомобиля и водителю . . . . . . . . . . . . . . . 123
Ввод информации по показаниям спидометра
и расходу топлива . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Ввод дат выезда и возвращения в гараж . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Область расчета дополнительных параметров по нормам расхода
топлива . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Составляющие расхода топлива . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Переключатель, группа и флажок . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Структура области расчета дополнительных параметров по нормам
расхода топлива . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Аналитическая область листа ВводДанных . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Добавление сервисных функций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Создание таблицы на рабочем листе Контроль . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Перенос данных на лист БазаДанных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Формирование отчетов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Формирование отчетов вручную . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Автоматизация процесса формирования отчетов . . . . . . . . . . . . . . . . . . . . . 145
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Глава 7. Модель расчета максимальной загрузки
автомобиля (контейнера) . . . . . . . . . . . . . . . . . . . . . . 152
Исходные данные . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Определение оптимального варианта размещения первой
партии груза . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Создание таблицы для расчета объема, занимаемого первой
партией груза . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Автоматизация выбора варианта расположения груза
первой партии . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Формулы расчета количества ящиков в первой партии . . . . . . . . . . . . . . . 158
Анализ и расчет свободного пространства кузова
после загрузки первой партии ящиков . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Создание таблицы расчета размеров оставшегося свободного
пространства в кузове автомобиля . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Определение оставшегося свободного объема кузова автомобиля . . . . . 160
Формулы таблицы расчета оставшегося свободного объема кузова . . . . 160
Дальнейшее заполнение кузова . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Моделирование возможности дальнейшей автоматизации
вычислений . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Расчет заполнения свободного объема кузова
второй партией груза . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Расчет заполнения свободного пространства кузова третьей
партией груза . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Расчет заполнения свободного объема кузова четвертой
партией груза . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Выходные параметры . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Расчет общего количества ящиков, помещающихся в кузов
автомобиля . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Доработка модели Транспорт . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Глава 8. Модель управления затратами —
предпосылки для создания модели . . . . . . . . . . . . . 170
Оценка влияния на прибыль сокращения уровня затрат и увеличения
объема продаж . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Анализ созданной таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Необходимость и назначение модели по исследованию влияния
объемов затрат и реализации на дополнительную прибыль . . . . . . . . . . . 176
Создание модели анализа влияния объема продаж и уровня издержек
на получаемую прибыль при различных соотношениях исходных
затрат и прибыли . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Восстановление исходного состояния . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Режим уменьшения объема затрат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Режим увеличения объема продаж . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Режим изменения пропорций затрат и прибыли в исходных
данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Методы управления вычислениями в Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Подпрограмма изменения пропорций затрат и прибыли
в исходных данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Доработка пользовательского интерфейса созданной модели . . . . . . . . . . . . 186
Защита приложения от неосторожных действий пользователя . . . . . . . . 186
Создание удобного интерфейса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Глава 9. Модель управления затратами — создание
таблицы распределения издержек . . . . . . . . . . . . . . 194
Классификация затрат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Классификация издержек по времени отнесения . . . . . . . . . . . . . . . . . . . . . 194
Классификация издержек по характеру поведения . . . . . . . . . . . . . . . . . . . 194
Классификация издержек на прямые и косвенные . . . . . . . . . . . . . . . . . . . . 195
Методы учета затрат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Требования к создаваемой компьютерной модели . . . . . . . . . . . . . . . . . . . . . . . 195
Состав модели . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Переменные издержки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Общецеховые издержки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Общезаводские издержки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Таблица расчета себестоимости продукции
и финансовых показателей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
Распределение косвенных затрат по видам продукции . . . . . . . . . . . . . . . . 203
Финансовые показатели . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Определение точки безубыточности . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Составляющие стоимости единицы продукции . . . . . . . . . . . . . . . . . . . . . . . 212
Группировка данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Создание таблицы анализа структуры затрат различных моделей
выпускаемой продукции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Глава 10. Модель управления затратами —
определение точки безубыточности
для каждого вида продукции
при многономенклатурном производстве . . . . . . . . 217
Определение точки безубыточности для одного вида продукции . . . . . . . . . 219
Классическая точка безубыточности
для одного вида продукции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Определение точки безубыточности для одного вида продукции
в составе всей выпускаемой заводом продукции . . . . . . . . . . . . . . . . . . . . . . 225
Применение формул при создании таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Использование таблицы подстановки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Автоматизация процедуры создания таблицы определения
точки безубыточности . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Корректировка созданной таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Поведение затрат на производство отдельного продукта
в составе общих затрат завода . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Определение точки безубыточности для всех изделий завода . . . . . . . . . . . . 241
Алгоритм определения нулевой точки рентабельности
для всех изделий предприятия . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Алгоритм определения нулевой точки рентабельности
для всех изделий предприятия при многономенклатурном
производстве . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Анализ полученных данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Корректировка распределения накладных расходов . . . . . . . . . . . . . . . . . . 247
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Глава 11. Модель управления затратами — анализ
поведения финансовых коэффициентов
при многономенклатурном производстве . . . . . . . . 250
Запас финансовой прочности . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Расчет запаса финансовой прочности при плановых объемах
реализации продукции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Расчет запаса финансовой прочности при объеме производства
на уровне точки безубыточности для всех видов продукции . . . . . . . 253
Подпрограмма расчета показателей
финансовой прочности . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
Статический и динамический запасы финансовой прочности
при многономенклатурном производстве . . . . . . . . . . . . . . . . . . . . . . . . . 256
Операционный рычаг . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Расчет динамического операционного рычага . . . . . . . . . . . . . . . . . . . . . . . . 263
Проверка режима вычислений Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268
Глава 12. Бюджетирование — вступительная часть . . 269
Бюджеты АО «Стеклодув» за январь 2011 года . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Кодировка статей учета . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
Бюджет доходов и расходов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Бюджет движения денежных средств . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Постановка задачи для разработки компьютерной модели . . . . . . . . . . . . . . . 276
Система функциональных бюджетов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277
Описание формы функциональных бюджетов . . . . . . . . . . . . . . . . . . . . . . . . 277
Функциональный бюджет административных расходов по заводу . . . . 278
Функциональный бюджет накладных расходов по отделу
исследований . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Функциональный бюджет закупок материалов для производства . . . . . 281
Функциональный бюджет расходов на оплату труда
производственных рабочих . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Функциональный бюджет коммерческих расходов . . . . . . . . . . . . . . . . . . . 282
Функциональный бюджет накладных расходов по цеху
производства бутылок . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Функциональный бюджет накладных расходов по цеху посуды . . . . . . . 283
Функциональный бюджет потребления электроэнергии . . . . . . . . . . . . . . 284
Функциональный бюджет амортизационных отчислений . . . . . . . . . . . . . 284
Функциональный бюджет продаж . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Функциональный бюджет дополнительной финансовой
информации . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Форматы представления основных бюджетов . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
Глава 13. Бюджетирование — создание модели
формирования БДДС и БДР . . . . . . . . . . . . . . . . . . . . 288
Состав модели . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Назначение модели . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Рабочий лист Пульт . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
Формирование функциональных бюджетов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
Подпрограмма для сбора данных функциональных бюджетов . . . . . . . . . 292
Обработка функциональных бюджетов . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Формирование бюджета доходов и расходов . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Глава 14. Бюджетирование — создание предпосылок
для прогнозирования денежных потоков . . . . . . . . 301
Фиксированные платежи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Справочник расшифровки кодов управленческого учета . . . . . . . . . . . . . . 304
Поиск вводимого кода и статьи управленческого учета . . . . . . . . . . . . . . . 305
Ввод кода и статьи управленческого учета . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Ввод даты и суммы фиксированного платежа . . . . . . . . . . . . . . . . . . . . . . . . 307
Удаление последней записи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Возможные нестыковки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Распределенные платежи . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Рабочий лист Прогноз . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Формулы расчета календарных дней планируемого месяца . . . . . . . . . . . 312
Расчеты, проводимые с рабочими днями планируемого периода . . . . . . 312
Автоматизация расчета временного отрезка . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Расчет сумм распределенных платежей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Формулы автоматизации расчета подекадных процентов
распределения сумм . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Формулы расчета распределенных платежей . . . . . . . . . . . . . . . . . . . . . . . . . 315
Создание таблицы распределенных платежей . . . . . . . . . . . . . . . . . . . . . . . . 317
Подпрограмма СозданиеТаблицыРаспрПлатежей . . . . . . . . . . . . . . . . . . . . 318
Макрос ПереносДанныхБДДС . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Макрос УдалениеСтолбцовСтрок . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Подпрограмма СортировкаПлатежей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Макрос ФормулыРаспрПлатежей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Подпрограмма ЦиклФормированияПлатежей . . . . . . . . . . . . . . . . . . . . . . . 322
Отладка . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Панель инструментов отладки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Режим отладки выполнения подпрограммы . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Шаг с заходом, шаг с обходом, шаг с выходом и запуск макроса . . . . . . . 325
Контроль выполнения цикла расчета . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Подпрограммы переноса данных и удаления листа . . . . . . . . . . . . . . . . . . . . . . 327
Макрос ПереносДанныхРаспрПлатежей . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Подпрограмма УдалениеЛиста . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
Глава 15. Модель прогнозирования денежных
потоков — создание таблицы исходных данных . . . 329
С чего начать? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
Таблица ввода исходных данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330
Описание таблицы исходных данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Расчеты в таблице . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Усовершенствование таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Расчет по различным предприятиям, счетам, валютам . . . . . . . . . . . . . . . . 337
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Глава 16. Модель прогнозирования денежных
потоков — создание таблицы расчета суммарных
ежедневных оборотов денежных средств . . . . . . . . 339
Таблица отображения оборотов движения денежных средств
в ежедневном разрезе . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Структура таблицы отображения оборотов движения
денежных средств в ежедневном разрезе . . . . . . . . . . . . . . . . . . . . . . . . . . 340
Элементы управления для выбора валюты . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Область выбора валют . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Алгоритм создания таблицы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Определение дат . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Определение валюты расчета и суммы денежных средств на начало
периода . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Транзакции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Расчет ежедневных оборотов денежных средств . . . . . . . . . . . . . . . . . . . . . . 345
Объединение записанных макросов в одну подпрограмму . . . . . . . . . . . . 346
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Глава 17. Модель прогнозирования денежных
потоков — расчет сумм привлечения внешних
денежных ресурсов (кредиты) . . . . . . . . . . . . . . . . . 348
Создание таблицы расчета суммы кредита . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Расчет кредитной линии . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
Формулы расчета кредитной линии . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Макрос ввода формул расчета кредита . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Отчет по кредитной линии . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Подпрограмма формирования отчета по кредитной линии . . . . . . . . . . . . 355
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Глава 18. Модель прогнозирования денежных
потоков — создание платежного календаря . . . . . . 357
Пример классического платежного календаря . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Задачи автоматизации при формировании платежного календаря . . . . . . . . 359
Создание платежного календаря . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Структура платежного календаря . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Алгоритм формирования платежного календаря . . . . . . . . . . . . . . . . . . . . . 361
Вставка наименований статей денежных потоков . . . . . . . . . . . . . . . . . . . . . 361
Расчет ежедневных потоков денежных средств . . . . . . . . . . . . . . . . . . . . . . . 371
Расчет итоговых сумм . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Дополнение платежного календаря сервисными функциями . . . . . . . . . . 378
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Глава 19. Модель прогнозирования денежных
потоков — полный расчет денежных потоков . . . . . 382
Состав созданной модели формирования платежного календаря . . . . . . . . . 382
Полный цикл расчета движения денежных потоков . . . . . . . . . . . . . . . . . . . . . . 383
Таймер . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Создание диалогового окна с помощью функции MsgBox . . . . . . . . . . . . . 384
Свертывание окна Excel на время выполнения вычислений . . . . . . . . . . . 385
Подпрограмма полного цикла расчета денежных потоков . . . . . . . . . . . . . 385
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Глава 20. Перенос данных из модели
бюджетирования в модель прогнозирования
денежных потоков . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Порядок переноса . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Проверка состояния файла ДенежныеПотокиБюджет . . . . . . . . . . . . . . . . . . . 388
Инструкция If-Then-Else . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
Создание пользовательских диалоговых окон для выбора направления
выполнения подпрограммы . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Создание подпрограммы переноса данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Открытие файла ДенежныеПотокиБюджет . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Перенос данных . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Итоги . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
