Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel

Рубрика: 7. Полезняшки Excel

Рассмотрим инвестиционный проект, связанный с приобретением апартаментов (небольшого отеля), и проанализируем экономические параметры проекта с помощью программы Crystal Ball [1].

Вы – потенциальный покупатель отеля. Прежде чем принять решение о приобретении отеля, вы выяснили следующее. В отеле 40 апартаментов, каждый из которых сдают по цене $ 500 в месяц. Операционные расходы по всему отелю колеблются вокруг суммы  $ 15 000 в месяц. Каждый месяц с равной вероятностью сдаются от 30 до 40 апартаментов. Расчет прибыли для средних доходов и расходов можно выполнить в Excel с помощью простейшей формулы (рис. 1):

(1) Прибыль = Число сданных в аренду апартаментов * Арендная плата – Операционные расходы

Рис. 1. Расчет прибыли для средних доходов и расходов

Читать полностью

Моделирование методом Монте-Карло в Crystal Ball для Excel

Рубрика: 7. Полезняшки Excel

Ранее я рассмотрел пример использования метода Монте-Карло для расчета риска с применением стандартных средств Excel. К сожалению, Excel не очень подходит для решения такого рода задач, так как является детерминистской программой. В каждой отдельной ячейке может располагаться лишь конкретное значение, и для моделирования методом Монте-Карло требуется создать множество строк (сценариев), используя генератор случайных чисел (например, функцию СЛЧИС).

Читать полностью

Использование метода Монте-Карло для расчета риска

Рубрика: 7. Полезняшки Excel

Не так давно я прочитал замечательную книгу Дугласа Хаббарда Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе. В кратком конспекте книги я обещал, что одному из разделов – Оценка риска: введение в моделирование методом Монте-Карло – я посвящу отдельную заметку. Да всё как-то не складывалось. И вот недавно я стал более внимательно изучать методы управления валютными рисками. В материалах, посвященных этой тематике, часто упоминается моделирование методом Монте-Карло. Так что обещанный материал перед вами.

* * *

Приведу простой пример моделирования методом Монте-Карло для тех, кто никогда не работал с ним ранее, но имеет определенное представление об использовании электронных таблиц Excel.

Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув точки безубыточности, вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.

Читать полностью

Excel. Сумма цифр целого числа

Рубрика: 7. Полезняшки Excel

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую начать с Введение в формулы массива и Некоторые примеры использования формул массива. Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

В настоящей заметке использованы материалы книги Джона Уокенбаха MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Читать полностью

Excel. Использование формулы массива для вычисления среднего, не учитывающего нулевые значения

Рубрика: 7. Полезняшки Excel

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

После этого вы, вероятно, сможете оценить возможности и достоинства формул массива, и вам захочется узнать о них побольше. Если вас не нужно агитировать, и вы полны желания разобраться в этом мощном оружии Excel, но не знаете, с чего начать, прочитайте Введение в формулы массива и Некоторые примеры использования формул массива

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Читать полностью

Excel. Некоторые примеры использования формул массива

Рубрика: 7. Полезняшки Excel

Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

После этого вы, вероятно, сможете оценить возможности и достоинства формул массива, и вам захочется узнать о них побольше. Если вас не нужно агитировать, и вы полны желания разобраться в этом мощном оружии Excel, но не знаете, с чего начать, прочитайте Введение в формулы массива.

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Читать полностью

Excel. Введение в формулы массива

Рубрика: 7. Полезняшки Excel

Ранее я уже посвятил несколько заметок использованию формул массива:

Полученные комментарии подсказали мне, что пользователи не часто используют формулы массива. Более того мало кто знаком с ними.

Предлагаю вашему вниманию посвященный массивам фрагмент книги Джона Уокенбаха. Подробное руководство по созданию формул в Excel 2002. – М.: Издательский дом «Вильямс», 2002. – 624 с.

Читать полностью

Excel. Использование циклических ссылок для решения уравнений итерационным способом

Рубрика: 7. Полезняшки Excel

Ранее я описал, как найти и исправить циклическую ссылку. Напомню, что циклическая ссылка появляется, если в ячейку Excel введена формула, содержащая ссылку на саму эту ячейку (напрямую или через цепочку других ссылок). Например (рис. 1), в ячейке С2 находится формула, ссылающаяся на саму ячейку С2.

Рис. 1. Пример циклической ссылки

Но!.. Не всегда циклическая ссылка является бедствием. Циклическую ссылку можно использовать для решения уравнений итерационным способом. Для начала нужно позволить Excel`ю вести вычисления, даже при наличии циклической ссылки. В обычном режиме Excel, обнаружив циклическую ссылку, выдаст сообщение об ошибке, и потребует ее устранения. В обычном режиме Excel не может провести вычисления, так как циклическая ссылка порождает бесконечный цикл вычислений. Можно, либо устранить циклическую ссылку, либо допустить вычисления по формуле с циклической ссылкой, но ограничив число повторений цикла. Для реализации второй возможности щелкните на кнопке «Office» (в левом верхнем углу), а затем на «Параметры Excel» (рис. 2).

Читать полностью

Решение задачи линейного программирования в Excel

Рубрика: 7. Полезняшки Excel

Ранее я писал, что для принятия решений с учетом ограничивающих факторов может использоваться линейное программирование. Напомню, что этот метод решает проблему распределения ограниченных ресурсов между конкурирующими видами деятельности с тем, чтобы максимизировать или минимизировать некоторые численные величины, такие как маржинальная прибыль или расходы.

При решении задач линейного программирования, во-первых, необходимо составить модель, то есть сформулировать условия на математическом языке. После этого решение может быть найдено графически (см., например, здесь), с использованием надстройки Excel «Поиск решения» (рассмотрено в настоящей заметке) или с помощью специализированных компьютерных программ (см., например, здесь).

Рассмотрим линейное программирование в Excel на примере задачи, ранее решенной графическим методом.

Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

Читать полностью

Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

Рубрика: 7. Полезняшки Excel

Формулы массива с каждым днем нравятся мне всё больше и больше! 🙂 Недавно у меня возникла задача – найти адрес ячейки, содержащей максимальное значение в диапазоне. Для начала я создал тестовый диапазон А1:F10 (рис. 1), заполнив его случайными целыми числами от 1 до 100 с помощью функции =СЛУЧМЕЖДУ(1;100).

Рис. 1. Исходный диапазон

Читать полностью