Введение 3
1. Теоретическая часть 5
1.1. Теоретические основы технико-экономического обоснования 5
1.2. Кредит в банке 6
1.2.1. Таблица технико-экономического обоснования 6
1.2.2. Страхование груза в пути 12
1.2.3. Таможенные пошлины 14
1.2.4. Почем надо покупать и продавать, чтобы избежать убытков? 17
1.3. Кредит с ежемесячным погашением 19
1.3.1. Первый вариант: ежемесячная выплата процентов 19
1.3.2. Второй вариант: ежемесячное погашение кредита 24
2. Практическая часть 27
2.1 Задание 1 27
2.2 Задание 2 29
2.3 Задание 3 31
2.4 Задание 4 33
Заключение 35
Список литературы 37
ВВЕДЕНИЕ
Тема курсовой работы – технико-экономические обоснования и финансовые расчеты в электронных таблицах Excel.
Известно, что-то предпринимая и реализуя какую либо идею, человек прикладывает усилия, и от того, как правильно он поступит, зависит успех будущего дела. Ни финансовые, ни какие-то другие задачи не решаются сами собой, но в то же время существует масса способов, при помощи которых можно облегчить труд. Программа MicrosoftExcel – один из инструментов многих предпринимателей, экономящих время на работе с трудно вычисляемыми и многочисленными формулами. В этом состоит актуальность моей работы.
Задача моей работы – дать возможность оценить помощь Excel в рутинных финансовых расчетах, рассмотрев следующие примеры: создание технико-экономического обоснования для получения кредита в банке на сделку по купле-продаже сахара, а также расчет прибыли при условии ежемесячного погашения кредита. Цифры, которые используются в работе, отличаются от реальной жизни, но логика расчета правильная, проверенная на практике, и может лишь варьироваться в зависимости от исходных условий. Тем не менее, стоит внимательно изучить все формулы для осмысленного их применения в условиях быстро меняющегося законодательства.
В практической части работы рассматривается пример из жизни. Не обходимо получить кредит в банке под 60 % годовых на покупку 1800 тонн товара по цене 2 600 рублей за тонну. За тем перевести товар в другое место и продать по цене не менее 3 700 рублей за тонну.
В первом задании производится расчет необходимой суммы кредита и полученной прибыли. Во втором – расчет страхования при транспортировке. В третьем – вычисления всех пошлин, НДС и получаемой прибыли после выплаты всех налогов. В четвертом – подбор показателей для получения прибыли.
1. ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
1.1. Теоретические основы технико-экономического обоснования
Формулы, с помощью которых можно правильно вычислить сумму кредита и прибыль, представлены ниже. Итак, сумма кредита складывается из:
Сумма кредита = Страховка + Цена всей партии + Накладные расходы,
но страховка складывается из:
Страховка = (Сумма кредита + Проценты за три месяца) * Процент страховки, тогда:
Сумма кредита = ((Сумма кредита + Проценты за три месяца) * Процент страховки) + Цена всей партии + Накладные расходы.
Отсюда следует:
Сумма кредита – Сумма кредита * Процент страховки = Проценты за три месяца * Процент страховки + Цена всей партии + Накладные расходы.
Так как:
Проценты за три месяца = Сумма кредита * (Годовой процент/12 * 3),
то:
Сумма кредита – Сумма кредита * Процент страховки - Сумма кредита * (Годовой процент/12*3) * Процент страховки = Цена всей партии + Накладные расходы.
Тогда:
Сумма кредита * (1 – Процент страховки – (Годовой процент/12 * 3) * Процент страховки) = Цена всей партии + Накладные расходы
или окончательно вычисляем сумму кредита:
Сумма кредита = (Цена всей партии + Накладные расходы)/(1 – Процент страховки – (Годовой процент/12*3) * Процент страховки).
1.2. Кредит в банке
Необходимо получить кредит под 70 процентов годовых на покупку 1000 тонн сахара по цене 1 400 рублей за тонну, затем перевести его в другое место (следовательно, будут накладные расходы) и продать по цене 2 300 рублей за тонну. Кредит нужен на три месяца. Нет залога, но есть договорились, что за дело поручится страховая компания. Так как страховой компании придется выплачивать банку кредит и проценты по нему за три месяца, если ничего не получится, то страховая компания хочет получить 10 процентов от суммы кредита плюс проценты за три месяца. Требуется правильно вычислить сумму кредита, так как со страховой компанией необходимо рассчитываться из полученного кредита, и убедить банк, что дело принесет прибыль после возврата кредита.
1.2.1. Таблица технико-экономического обоснования
Создание таблицы технико-экономического обоснования
1) Запустить Excel. Если это необходимо, то после запуска Excel включить изображение панелей инструментов Стандартная и Форматирование с помощью команды меню Вид - Панели инструментов. При запуске Excel будет автоматически создана новая рабочая книга.
2) Ввести в столбец A заголовки в соответствии с таблицей 1.1.
Ввод заголовков
3) Щелкнуть мышью на ячейке А14 и затем нажать кнопку (По центру) на панели инструментов Форматирование. Слово "или" будет выровнено по центру ячейки.
4) Щелкнуть правой кнопкой мыши на ячейке A1. На экране появится контекстное меню.
5) Выбрать команду Формат ячеек из контекстного меню. На экране появится диалог Формат ячеек.
6) Выбрать вкладку Выравнивание.
7) Выбрать значение По центру из выпадающего списка по горизонтали (Ногiгопа1), а также значение По центру из выпадающего списка по вертикали.
Таблица 1.1
| В какую ячейку? | Что ввести? | |
| 1 | 2 | |
| A1 | Технико-экономическое обоснование для получения кредита | |
| A3 | Товар | |
| A4 | Единица измерения | |
| A5 | Цена за единицу | |
| A6 | Количество единиц | |
| A7 | Цена всей партии | |
| A9 | Кредит | |
| A11 | Проценты по кредиту | |
| A12 | Срок кредита | |
| A1З | Страховка кредита | |
| 1 | 2 | |
| A14 | Или | |
| A15 | Необходимая сумма кредита | |
| A16 | Проценты по кредиту за каждый месяц | |
| A18 | Накладные расходы | |
| A20 | Транспортные расходы | |
| A21 | Непредвиденные расходы | |
| A2З | Реализация товара и возврат кредита | |
| A25 | Цена реализации за единицу | |
| A26 | Цена реализации за всю партию | |
| A27 | Возврат кредита с процентами | |
| A28 | Прибыль до вычета налогов | |
8) Установить флажок Переносить по словам. Это делается для того, чтобы расположить текст в ячейке в несколько строк.
9) Закрыть диалог с помощью кнопки ОК.
10) Выделить диапазон ячеек A1:C1.
11) Нажать кнопку (Объединить и поместить в центре) на панели инструментов Форматирование. Заголовок в ячейке A1 будет расположен по центру выделенной области.
11) Щелкнуть мышью на ячейке A1.
12) Открыть список Шрифт на панели инструментов Форматирование и выбрать из него шрифт TimesNewRoman, Шрифт заголовка в ячейке A1 изменится.
13) Открыть список для установки размера шрифта на панели инструментов Форматирование и выбрать из него размер 12.
14) Нажать кнопку (Полужирный) на панели инструментов Форматирование (Formatting). Заголовок в ячейке A1 будет выделен полужирным шрифтом.
15) Выделить диапазон ячеек A1:C1.
16) Дважды щелкнуть по кнопке (Формат по образцу) на панели инструментов Форматирование. Кнопка (Формат по образцу) будет зафиксирована в нажатом состоянии. Рядом с указателем мыши появится изображение кисти.
17) Щелкнуть мышью на ячейках A9, A18, A2З поочередно. Заголовки "Кредит", "Накладные расходы" и "реализация товара и возврат кредита" будут выровнены и оформлены точно так же, как и заголовок "Технико-экономическое обоснование для получения кредита".
18) Нажать клавишу Esc. Кнопка (Формат по образцу) будет отжата.
19) Щелкнуть мышью на заголовке столбца A. Будет выделен весь столбец А.
20) Выбрать команду меню Формат - Столбец - Автоподбор ширины. Ширина столбца автоматически изменится по самому длинному заголовку в ячейках столбца. При этом ячейки, которые выровнены по центру нескольких столбцов, во внимание приниматься не будут.
21) Ввести числа и текст в столбец B в соответствии с таблице 1.2.
Таблица 1.2
| В какую ячейку? | Что ввести? |
| B3 | Сахар |
| B4 | Тонны |
| B5 | 1400 |
| B6 | 1000 |
| B11 | 70 |
| B12 | 3 |
| B13 | 10 |
| B20 | 10000 |
| B21 | 5000 |
| B25 | 2300 |
Ввод чисел и текста в столбец B
22) Щелкнуть правой кнопкой на ячейке В5. На экране появится контекстное меню.
23) Выбрать команду Формат ячеек из контекстного меню. На экране появится диалог Формат ячеек.
24) Выбрать вкладку Число.
25) Выбрать строку (все форматы) (Custom) в списке Числовые форматы.
26) Щелкнуть мышью на строке # ##0 в списке Тип (Туре).
27) Закрыть диалог с помощью кнопки ОК. Число 1400 будет представлено в виде 1 400.
28) Дважды щелкнуть на кнопке (Формат по образцу) на панели инструментов Форматирование. Кнопка будет зафиксирована в нажатом состоянии. Рядом с указателем мыши появится изображение кисти.
29) Щелкнуть мышью на ячейках B6, B20, B21, B25 поочередно, чтобы скопировать формат представления числа из ячейки В5.
30) Нажать клавишу Esc. Кнопка (Формат по образцу) будет отжата.
31) Ввести числа и текст в столбец C в соответствии с таблицей 1.3.
Таблица 1.3
| В какую ячейку? | Что ввести? |
| C5,C7, C14, C15, C16, C20, C21, C25, C26, C27, C28 | руб. |
| C3 | Сахар |
| C6 | Тонн |
| C11 | процентов годовых |
| C12 | Месяца |
| C13 | процентов от суммы кредита с процентами |
Ввод текста в столбец C
32) Ввести формулы в ячейки в соответствии с таблицей 1.4.
Таблица 1.4
| Ячейка | Формула | Комментарии |
| 1 | 2 | 3 |
| B7 | =B5*B6 | Цена всей партии = цена за тонну * количество тонн |
| B15 | =(B7+В20+В21)/(1-В13/100-(В11/12/100)*В12*В13/100) | Вычисляем сумму кредита с учетом цены всей партии, накладных расходов и страховки кредита с процентами. |
| B14 | =В15*(1+(В11/12/100)*B12)*В13/100 | Вычисляем сумму страховки 10 процентов от суммы кредита с процентами |
| B16 | =В15*В11/12/100 | Сумма процентов, набегающих за месяц |
Продолжение таблицы 1.4
| Ячейка | Формула | Комментарии | |
| B26 | =B25*B6 | Цена реализации всей партии = цена за тонну * количество тонн | |
| B27 | =В15+В16*B12 | Возвраткредита и процентов | |
| B28 | =B26-В27 | Деньги, оставшиеся после возврата кредита | |
Формулы, вводимые в ячейки
33) Щелкнуть мышью на ячейке B5.
34) Дважды щелкнуть на кнопке (Формат по образцу) на панели инструментов Форматирование. Кнопка будет зафиксирована в нажатом состоянии. Рядом с указателем мыши появится изображение кисти.
35) Щелкнуть мышкой на ячейках B7, B14, B15, B16, B26, B27, В28 поочередно, чтобы скопировать формат представления числа из ячейки B5.
36) Нажать клавишу Esc. Кнопка (Формат по образцу) будет отжата.
37) Щелкнуть мышью на заголовке столбца B. Будет выделен весь столбец B. Выбрать команду меню Формат - Столбец - Автоподбор ширины. Ширина столбца автоматически изменится по самому длинному заголовку в ячейках столбца. На этом процесс создания технико-экономического обоснования заканчивается (Рис. 1).
Итак, получилось, что прибыль до вычета налогов составит всего 416006 рублей. Простой способ проверить правильность расчетов – это вычесть из суммы кредита выплату по страховке, цену всей партии и накладные расходы – получится ноль.
Законченный вид технико-экономического обоснования
Рис. 1
1.2.2. Страхование груза в пути
Предполагается, что требуется застраховать товар при перевозке к месту его реализации. При этом если при перевозке товар исчезнет, то все равно придется выплачивать кредит и проценты по нему. Поэтому следует застраховать груз на сумму, равную кредиту с процентами за 3 месяца, также можно добавить к страховой сумме небольшой интерес, на пример, завысить транспортные и непредвиденные расходы в два раза, чтобы не остаться в накладе в любом случае, вне зависимости от того, пропал товар в пути или нет. Это, конечно, уменьшит прибыль в случае, если все пройдет успешно, но зато даст гарантии успеха вне зависимости от обстоятельств. Так как страховку при транспортировке понадобится также платить из кредита, то к проценту страховании кредита необходимо добавить процент страхования груза.
Чтобы учесть страхование товара при транспортировке, необходимо изменить таблицу.
1) Выделить диапазон ячеек A23:C28.
2) Подвести указатель мыши к черной рамке обрамляющей выделенный диапазон. Указатель мыши изменится на стрелку. Нажать и удерживать левую кнопку мыши. Переместить рамку на две строки ниже. В разделе "Накладные расходы" появятся две новых пустых строки с ячейками.
3) Ввести текст, числа и формулы в соответствии с таблицей 1.5.
4) Щелкнуть мышью на ячейке B21.
5) Щелкнуть на кнопке (Формат по образцу) на панели инструментов Форматирование. Рядом с указателем мыши появится изображение кисти.
6) Щелкнуть мышью на ячейке B23, чтобы скопировать формат представления числа из ячейки B21.
Таблица 1.5
| В какую ячейку? | Что ввести? | |
| 1 | 2 | |
| A22 | Страхование при транспортировке | |
| B21 | 10000 | |
| B20 | 20000 | |
| A23 | Итого страхование в пути | |
| B22 | 2 | |
| B23 | =В22/100*В29 | |
| C22 | процентов от суммы кредита с процентами + интерес | |
| 1 | 2 | |
| C23 | руб. | |
| B15 | =(B7+B20+B21)/(1-(B13+В22)/100-B11/12/100)*B12*(B13+B22)/100 | |
Ввод текста, чисел и формул для учета страхования груза в пути
В результате, если груз будет потерян при транспортировке, можно получить моральную компенсацию в размере 15 000 рублей, за счет завышения накладных расходов, вместо 343946 рублей прибыли (Рис. 2).
Законченный вид технико-экономического обоснования с учетом страхования при транспортировке
Рис. 2
1.2.3. Таможенные пошлины
Предполагается, что после покупки сахарного песка при ввозе его внутрь страны потребуется заплатить: налог на добавленную стоимость, таможенный сбор, таможенный налог, акциз. Возможно, что для сахара некоторые из указанных платежей равны нулю, но расчет необходим.
В данном расчете будут использоваться ненулевые вымышленные значения, которые можно легко изменить для конкретного товара и страны его происхождения. Все платежи на таможне относятся в раздел "Накладные расходы" таблицы технико-экономического обоснования. Но сначала рассматривается то, как вычисляются размеры платежей, исходя из того, что сумма контракта, то есть сумма, на которую закуплен сахар, равна 1400 и находится в ячейке B7. Все таможенные платежи, за исключением НДС, вычисляются как процент от суммы контракта. А НДС вычисляется по формуле:
НДС = (Сумма контракта + Сумма всех таможенных платежей) * Процент НДС.
Снова необходимо внести изменения в таблицу технико-экономического обоснования.
1) Выделить диапазон ячеек A25:С30.
2) Подвести указатель мыши к черной рамке, обрамляющей выделенный диапазон. Нажать и удерживать левую кнопку мыши. Переместить рамку на четыре строки ниже. В разделе "Накладные расходы" появятся четыре новые пустые строки с ячейками.
3) Ввести текст, числа и формулы в соответствии с таблицей 1.6.
Таблица 1.6
| В какую ячейку? | Что вводить? |
| A24 | Акциз |
| A25 | Налог на добавленную стоимость |
| A26 | Таможенный сбор |
| A27 | Таможенный налог |
| B24 | 10 |
| B25 | 23 |
| B26 | 0,1 |
| B27 | 15 |
| C24, C25, C26, C27 | процентов или |
| E24, E25, E26, E27 | руб. |
| D24 | =B7*B24/100 |
| D25 | =(D24+D26+D27+B7)*B25/100 |
| D26 | =B7*B26/100 |
| D27 | =B7*B27/100 |
| B15 | =(B7+B20+B21+D24+D25+D26+D27)/(1-(B13+B22)/100-(В11/12/ 100)*В12*(В13+В22)/100) |
4) Щелкнуть мышью на заголовке столбца C. Выделится весь столбец.
5) Щелкнуть правой кнопкой мыши на любой ячейке выделенного столбца. На экране появится контекстное меню.
6) Выбрать команду Формат ячеек из контекстного меню. На экране появится диалог Формат ячеек.
7) Выбрать вкладку Число.
8) Выбрать строку (все форматы) в списке Числовые форматы. Щелкнуть мышью на строке в списке Тип.
9) Закрыть диалог с помощью кнопки ОК. Таким образом, всем ячейкам столбца будет присвоен новый формат чисел.
10) Выбрать команду меню Формат – Столбец - Автоподбор ширины. Ширина столбца C изменится так, чтобы уместить самое длинное число. На этом исправления технико-экономического обоснования заканчиваются (Рис.3).
Ввод текста, чисел и формул для учета таможенных платежей
Законченный вид технико-экономического обоснования для учета таможенных платежей
Рис. 3
Из таблицы технико-экономического обоснования видно, что при выплате всех таможенных платежей убытки составят 687731 рублей. Что делать? Надо увеличивать цену продажи, уменьшать цену покупки, уменьшать размер страховки и годовой процент по кредиту. Можно изменять указанные значения вручную, но это утомительно. В следующий опыт рассматривает способ подбора нужных чисел автоматически, в зависимости от желаемого результата.
1.2.4. Почем надо покупать и продавать, чтобы избежать убытков?
"Почем надо продавать, чтобы получить нулевую прибыль после возврата кредита?". С помощью следующих действий можно легко ответить на этот вопрос.
1) Активизировать рабочий лист с последним вариантом технико-экономического обоснования.
2) Выбрать команду меню Сервис-Подбор параметра. На экране появится диалог Подбор параметра.
Прокручивая содержимое таблицы с помощью мыши при активном диалоге Подбор параметра, щелкнуть на ячейке B34. В поле ввода Установить в ячейке появится адрес ячейки $B$34.
3) Нажать клавишу Tab, чтобы перейти к полю ввода Значение, и ввести с клавиатуры ноль.
4) Нажать клавишу Tab, чтобы перейти к полю ввода Изменяя значение ячейки, и затем щелкнуть мышью на ячейке B31. В поле ввода Изменяя значение ячейки появится адрес ячейки $B$31.
5) Закрыть диалог Подбор параметра с помощью кнопки ОК. На экране появится диалог Результат подбора параметра, в котором отражаются текущие вычисления. После небольшого промежутка времени в диалоге появится сообщение: "Решение найдено".
Если решение не найдено, то необходимо выбрать команду меню Сервис - Параметры, на экране появится диалог Параметры. Выберите вкладку Вычисления. Сбросить флажок Точность как на экране и закрыть диалог с помощью кнопки ОК. Таким образом, повышается точность внутреннего представления чисел в ячейках, и тем самым повышается точность расчетов по подбору параметра. Теперь можно пробовать подобрать параметр еще раз.
В результате подбора параметра получается следующее, чтобы избежать убытков, надо продавать по цене 2988 рублей за тонну (Рис. 4).
Результат подбора параметра в ячейке В31
Предполагается, что покупатели согласны взять товар по цене не выше 2500 рублей за единицу. Значит надо уменьшать цену, по которой покупается товар. Возникает вопрос: "По какой цене надо покупать товар, чтобы продать его по 2500 рублей за единицу и при этом избежать убытков?".
Рис. 4
Чтобы ответить на поставленный вопрос, необходимо выполнить следующие действия.
1) Ввести в ячейку B31 значение 2500.
2) Выбрать команду меню Сервис-Подбор параметра. На экране появится диалог Подбор параметра.
3) Прокручивая содержимое таблицы с помощью мыши при активном диалоге Подбор параметра, щелкнуть на ячейке B34. В поле ввода Установить в ячейке появится адрес ячейки $B$34.
4) Нажать клавишу Tab, чтобы перейти к полю ввода Значение, и ввести с клавиатуры ноль.
5) Нажать клавишу Tab, чтобы перейти к полю ввода Изменяя значение ячейки, и затем щелкнуть мышью на ячейке B5. В поле ввода Изменяя значение ячейки появится адрес ячейки $B$5.
6) Закрыть диалог Подбор параметра с помощью кнопки ОК. На экране появится диалог Результат подбора параметра, в котором отражаются текущие вычисления. После небольшого промежутка времени в диалоге появится сообщение: "Решение найдено".
В результате вычислений получается следующее. Чтобы избежать убытков, надо покупать по цене 1 168 рублей за тонну.
1.3. Кредит с ежемесячным погашением
Можно отвлечься от миллиардных сумм и перейти к более простому и реальному. Необходимо занять $10000 у банка под 5% в месяц на два года, чтобы начать собственную куплю-продажу. Предполагается что, при помощи этих денег можно зарабатывать не менее 10% в месяц и начать выплату процентов и погашение кредита, но не после первого месяца. При этом придется выплачивать проценты на проценты.
Чтобы проанализировать положение дел, нужно создать таблицу, в которой можно рассчитать прибыль в зависимости от срока начала выплат. При этом возможны два варианта: выплата только процентов с выплатой кредита в конце срока и погашение кредита равными долями вместе с процентами. Очевидно, что чем позже возвращаются деньги, тем выгоднее, однако при возврате кредита равными долями у кредитора больше шансов вернуть свои деньги, поэтому конкретный вариант является предметом торга. А чтобы торговаться, надо знать о чем.
1.3.1. Первый вариант: ежемесячная выплата процентов
1) Создать рабочую книгу или активизировать новый рабочий лист.
2) Ввести текстовые заголовки в соответствии с таблицей 1.7.
Текстовые заголовки
3) Установить указатель мыши в строке заголовков столбцов на границе столбца A и В. При этом указатель мыши примет форму вертикального штриха с двунаправленной стрелкой поперек.
4) Нажать и удерживать левую кнопку мыши. Перетащить границу столбцов вправо, чтобы увеличить ширину столбца А.
5) Аналогичным образом установить ширину остальных столбцов.
Таблица 1.7
| В какую ячейку? | Что вводить? |
| A1 | Сумма кредита |
| A2 | Проценты по кредиту в месяц |
| A3 | Прибыль в месяц |
| A5 | Начало выплаты |
| A6 | Число выплат |
| A7 | Сумма |
| A9 | Месяц |
| B9 | Сумма в конце месяца |
| C9 | Ежемесячные выплаты |
| D9 | Сумма в обороте |
6) Ввести в ячейку C1 значение 10000.
7) Щелкнуть правой кнопкой на ячейке C1. На экране появится контекстное меню.
8) Выбрать команду Формат ячеек из контекстного меню. На экране появится диалог Формат ячеек.
9) Выбрать вкладку Число.
10) Щелкнуть на строке (все форматы) в списке Числовые форматы и затем на строке ###0,00 в списке Тип.
11) Щелкнуть мышью в поле ввода Тип и отредактируйте формат, чтобы получить: $ ##0,00.
12) Закрыть диалог с помощью кнопки ОК. В ячейке C1 появится значение "$ 10000,00".
13) Ввести в ячейку C2 значение 0,05, а в ячейку C3 – 0,1.
14) Выделить ячейки C2 и C3 и нажмите кнопку (Процентный формат) на панели инструментов Форматирование.
15) Ввести в ячейки формулы и числа в соответствии с таблицей 1.8.
Таблица 1.8
| В какую ячейку? | Что вводить? | ||||||||||
| C5 | 2 | ||||||||||
| C6 | =24-С5+1 | ||||||||||
| D7 | =АДРЕС(9+$C$5;2) | ||||||||||
| C7 | =ДВССЫЛ(D7) | ||||||||||
| A10 | 0 | ||||||||||
| A11 | 1 | ||||||||||
| A12 | 2 | ||||||||||
| B10 | =C1 | ||||||||||
| B11 | =С1+С1*С2+С11 | ||||||||||
| C11 | =ЕСЛИ($С$5>А11;0;-$С$7*$C$2) | ||||||||||
| D11 | =$C$1+$C$1*$C$3+C11 | ||||||||||
| B12 | =В11+В11*$C$2+C12 | ||||||||||
| C12 | =ЕСЛИ ($С$5>А12;0;-$С$7*$С$2) | ||||||||||
| D12 | =D11+D11*$C$3+C12 | ||||||||||
| A13 | 3 | ||||||||||
| B13 | =В12+В12*$C$2+C13 | ||||||||||
| C13 | =ЕСЛИ($С$5>А13;0;-$С$7*$С$2) | ||||||||||
| D13 | =D12+D12*$С$3+С13 |
Ввод формул и чисел в ячейки таблицы
В ячейке D7 находится ссылка на ячейку, в которой находится сумма денег, с которой начинается платеж процентов, в зависимости от месяца начала выплаты в ячейке С5. В ячейке C7 находится значение из ячейки, ссылка на которую вычислена в ячейке D7. Значения в ячейках А10 и B10 нужны, чтобы правильно работало вычисление ссылки в ячейке D7 при начале выплат с первого месяца. Формула "C1+C1*C2+С11" в ячейке B11 означает, что в конце месяца задолженность составит сумму, равную сумме полученной за предыдущий месяц, плюс проценты, набежавшие за месяц с этой суммы, плюс выплаченные проценты, так как все выплаты отражаются со знаком минус в столбце "Ежемесячные выплаты". Аналогичным образом вычисляются значения в столбце "Сумма в обороте". Ежемесячные выплаты вычисляются по формуле "=ЕСЛИ($С$5>А12; 0; -$C$7*$C$2)" для второго месяца. Здесь, если текущий месяц А12 меньше, чем месяц начала выплаты процентов, то формула равна нулю, в противном случае вычисляются проценты "-$C$7*$C$2".
16) Щелкнуть мышью на ячейке C1 и нажать кнопку (Формат по образцу) на панели инструментов Стандартная. Кнопка зафиксируется в нажатом состоянии.
17) Выделить диапазон ячеек В10:D13. На все ячейки выделенного диапазона будет скопирован формат $# ##0,00.
18) Выделить диапазон ячеек A10:D13 и нажать кнопку (По центру) на панели инструментов Форматирование. Содержимое ячеек будет расположено по центру ячеек.
19) Выделить диапазон ячеек А12:D13.
20) Подвести указатель мыши к нижнему правому углу рамки, обрамляющей выделение. Указатель мыши примет форму черного крестика.
21) Нажать левую кнопку мыши. Перемещая мышь, растянуть рамку до 34 строки включительно. Отпустить левую кнопку мыши. Ячейки автоматически заполнятся значениями и формулами для 24 месяцев.
22) Ввести в ячейку C36 слово "Прибыль:". А в ячейку D36 формулу "=D34-В34" Получилось $46 237,24 прибыли - если начать выплачивать проценты со второго месяца и вернуть кредит в конце срока (Таблица 1.9).
23) Изменить значение в ячейке С5 на 5. Получилось $51 533,20 прибыли – если начать выплачивать проценты с пятого месяца и вернуть кредит в конце срока.
24) Изменить значение в ячейке С5 на 24. Получилось $66 246,33 прибыли – если выплатить проценты и вернуть кредит в конце срока.
Таблица 1.9
| 1 | 2 | 3 | 4 |
| Сумма кредита | $ 10 000,00 | ||
| Проценты по кредиту в месяц | 5% | ||
| Прибыль в месяц | 10% | ||
| Начало выплаты | 2 | ||
| Число выплат | 23 | ||
| Сумма | 10 500 | $B$11 | |
| Месяц | Сумма в конце месяца | Ежемесячные выплаты | Сумма в обороте |
| 0 | $ 10 000,00 | ||
| 1 | $ 10 500,00 | $ 0,00 | $ 11 000,00 |
| 2 | $ 10 500,00 | -$ 525,00 | $ 11 575,00 |
| 3 | $ 10 500,00 | -$ 525,00 | $ 12 207,50 |
| 4 | $ 10 500,00 | -$ 525,00 | $ 12 903,25 |
| 5 | $ 10 500,00 | -$ 525,00 | $ 13 668,58 |
| 6 | $ 10 500,00 | -$ 525,00 | $ 14 510,43 |
| 7 | $ 10 500,00 | -$ 525,00 | $ 15 436,48 |
| 8 | $ 10 500,00 | -$ 525,00 | $ 16 455,12 |
| 9 | $ 10 500,00 | -$ 525,00 | $ 17 575,64 |
| 10
|
$ 10 500,00 | -$ 525,00 | $ 18 808,20 |
| 11 | $ 10 500,00 | -$ 525,00 | $ 20 164,02 |
| 12 | $ 10 500,00 | -$ 525,00 | $ 21 655,42 |
| 13 | $ 10 500,00 | -$ 525,00 | $ 23 295,96 |
| 14 | $ 10 500,00 | -$ 525,00 | $ 25 100,56 |
| 15 | $ 10 500,00 | -$ 525,00 | $ 27 085,62 |
| 16 | $ 10 500,00 | -$ 525,00 | $ 29 269,18 |
Продолжение таблицы 1.9
| 1 | 2 | 3 | 4 |
| 17 | $ 10 500,00 | -$ 525,00 | $ 31 671,09 |
| 18 | $ 10 500,00 | -$ 525,00 | $ 34 313,20 |
| 19 | $ 10 500,00 | -$ 525,00 | $ 37 219,52 |
| 20 | $ 10 500,00 | -$ 525,00 | $ 40 416,48 |
| 21 | $ 10 500,00 | -$ 525,00 | $ 43 933,12 |
| 22 | $ 10 500,00 | -$ 525,00 | $ 47 801,44 |
| 23 | $ 10 500,00 | -$ 525,00 | $ 52 056,58 |
| 24 | $ 10 500,00 | -$ 525,00 | $ 56 737,24 |
| Прибыль: | 46 237,24 |
Готовая таблица для расчета
1.3.2. Второй вариант: ежемесячное погашение кредита
Предполагается, что кредитор настаивает на ежемесячном погашении кредита и процентов равными долями, но опять можно торговаться с какого месяца начать выплаты. Для этого необходимо отредактировать таблицу из предыдущего параграфа. В столбце "Ежемесячные выплаты" нужно использовать функцию:
ПЛТ(СТАВКА ПРОЦЕНТА, ЧИСЛО,_ВЫПЛАТ, СУММА_КРЕДИТА)
С ее помощью можно вычислять ежемесячные выплаты по процентам и погашению кредита.
1) Активизировать рабочий лист из предыдущего параграфа и ввести в ячейку С11 формулу "ЕСЛИ($С$5>А11;0;ПЛТ($С$2;$С$6;$С$7))".
2) Щелкнуть мышью на ячейке C11. Ячейка С11 станет текущей. Нажать комбинацию клавиш Ctrlплюс Insert. Содержимое ячейки будет скопировано в буфер обмена Windows. Нажать клавишу ↓(Стрелка вниз). Ячейка С12 станет текущей. Нажать комбинацию клавиш Shiftплюс Insert. Содержимое буфера обмена будет вставлено в ячейку С12.
Повторить вставку содержимого буфера обмена Windows для всех остальных ячеек столбца "Ежемесячные выплаты". Так как от предыдущего расчета в ячейке С5 осталось значение 24, то в результате получилась прибыль, равная $66 246,33. Точно такая же, как из предыдущего расчета. Потому что все выплаты происходят за один раз в конце срока в обоих случаях. Обратите внимание: в конце 24 месяца долг будет равен 0.
3) Установить в ячейке С5 значение 5. Получилось, что при начале погашения кредита на пятый месяц прибыль составит $42 633,95. В конце 24 месяца долг всегда будет равен 0 (Таблица 1.10).
Таблица 1.10
| 1 | 2 | 3 | 4 |
| Сумма кредита | $ 10 000,00 | ||
| Проценты по кредиту в месяц | 5% | ||
| Прибыль в месяц | 10% | ||
| Начало выплаты | 5 | ||
| Число выплат | 20 | ||
| Сумма | 12 155,0625 | $B$14 | |
| Месяц | Сумма в конце месяца | Ежемесячные выплаты | Сумма в обороте |
| 0 | $ 10 000,00 | ||
| 1 | $ 10 500,00 | $ 0,00 | $ 11 000,00 |
| 2 | $ 11 025,00 | $ 0,00 | $ 12 100,00 |
| 3 | $ 11 576,25 | $ 0,00 | $ 13 310,00 |
| 4 | $ 12 155,06 | $ 0,00 | $ 14 641,00 |
| 5 | $ 11 787,46 | -$ 975,35 | $ 15 129,75 |
| 6 | $ 11 401,48 | -$ 975,35 | $ 15 667,37 |
| 7 | $ 10 996,20 | -$ 975,35 | $ 16 258,75 |
Продолжение таблицы 1.10
| 1 | 2 | 3 | 4 |
| 8 | $ 10 570,66 | -$ 975,35 | $ 16 909,27 |
| 9 | $ 10 123,84 | -$ 975,35 | $ 17 624,85 |
| 10 | $ 9 654,68 | -$ 975,35 | $ 18 411,98 |
| 11 | $ 9 162,06 | -$ 975,35 | $ 19 277,82 |
| 12 | $ 8 644,80 | -$ 975,35 | $ 20 230,25 |
| 13 | $ 8 101,69 | -$ 975,35 | $ 21 277,92 |
| 14 | $ 7 531,42 | -$ 975,35 | $ 22 430,36 |
| 15 | $ 6 932,64 | -$ 975,35 | $ 23 698,04 |
| 16 | $ 6 303,92 | -$ 975,35 | $ 25 092,49 |
| 17 | $ 5 643,76 | -$ 975,35 | $ 26 626,39 |
| 18 | $ 4 950,59 | -$ 975,35 | $ 28 313,67 |
| 19 | $ 4 222,77 | -$ 975,35 | $ 30 169,68 |
| 20 | $ 3 458,56 | -$ 975,35 | $ 32 211,30 |
| 21 | $ 2 656,13 | -$ 975,35 | $ 34 457,08 |
| 22 | $ 1 813,58 | -$ 975,35 | $ 36 927,43 |
| 23 | $ 928,91 | -$ 975,35 | $ 39 644,82 |
| 24 | $ 0,00 | -$ 975,35 | $ 42 633,95 |
| Прибыль: | 42 633,95 |
Таблица для расчета варианта ежемесячного погашения кредита
Рассмотрено лишь несколько примеров использования Excel для обсчета определенных ситуаций, но знания, полученные при этом, можно с успехом применять для многих других случаев.
2. ПРАКТИЧЕСКАЯ ЧАСТЬ
2.1 Задание 1
Необходимо получить кредит под 60 % годовых на покупку 1800 тонн товара по цене 2 600 рублей за тонну. За тем перевести товар в другое место (следовательно, возникнут накладные расходы) и продать по цене 3 700 рублей за тонну. Кредит нужен на три месяца. Залога нет, за сделку поручается страховая компания, необходимо будет выплатить банку кредит и проценты по нему за три месяца. Если забрать деньги, то страховая компания получит 12 % от суммы кредита плюс процент за три месяца. Требуется правильно вычислить сумму кредита и полученную прибыль. По полученным данным построить гистограмму, отображающую цену реализации за всю партию и прибыль до вычета налогов.
Расчет необходимой суммы кредита и полученной прибыли приведен в таблице 2.1.
Таблица 2.1
| Наименование | Значение | Единицы измерения |
| 1 | 2 | 3 |
| Цена за единицу | 2 600 | руб. |
| Количество единиц | 1800 | тонн |
| Цена всей партии | 4 680 000 | руб. |
| Кредит | ||
| Проценты по кредиту | 60 | % годовых |
| Срок кредита | 3 | месяца |
| Страховка кредита | 12 | % от суммы кредита с процентами |
| Или | 751 635,73 | руб. |
| Необходимая сумма кредита | 5 446 635,73 | руб. |
Продолжение таблицы 2.1
| 1 | 2 | 3 |
| Проценты по кредиту за каждый месяц | 272 331,79 | руб. |
| Накладные расходы | ||
| Транспортные расходы | 10 000 | руб. |
| Непредвиденные расходы | 5 000 | руб. |
| Реализация товара и возврат кредита | ||
| Цена реализации за единицу | 3 700 | руб. |
| Цена реализации за всю партию | 6 660 000 | руб. |
| Возврат кредита с процентами | 6 263 631,09 | руб. |
| Прибыль до вычета налогов | 396 368,91 | руб. |
Расчет суммы кредита и прибыли до вычета налогов
По данным таблицы 2.1 построена гистограмма, отображающая цену реализации за всю партию и прибыль до вычета налогов.
Соотношение между ценой реализации за всю партию и прибылью до вычета налогов
Рис. 2.1
По данным таблицы 2.1 и построенной гистограмме видно, что для закупки 1800 тонн товара по цене 2 600 рублей за 1 тонну необходим кредит на сумму 5 446 635,73 рублей под 60 % годовых. Товар реализуется по цене 3 700 рублей за тонну. При этом полученная прибыль до вычета налогов составит 396 368,91 рублей.
2.2 Задание 2
Учитывая страхование товара при транспортировке, произвести изменения в электронной таблице. В результате, если груз будет потерян при транспортировке, вычислить страховку. По полученным итогам страхования и цене всей партии построить круговую диаграмму, отображающую их процентное соотношение.
Расчет страхования при транспортировке выполнен в таблице 2.2.
Таблица 2.2
| Наименование | Значение | Единицы измерения |
| 1 | 2 | 3 |
| Цена за единицу | 2 600 | руб. |
| Количество единиц | 1800 | тонн |
| Цена всей партии | 4 680 000 | руб. |
| Кредит | ||
| Проценты по кредиту | 60 | % годовых |
| Срок кредита | 3 | месяца |
| Страховка кредита | 12 | % от суммы кредита с процентами |
| или | 772 240,76 | руб. |
| Необходимая сумма кредита | 5 595 947,56 | руб. |
| Проценты по кредиту за каждый месяц | 279 797,38 | руб. |
Продолжение таблицы 2.2
| 1 | 2 | 3 |
| Накладные расходы | ||
| Транспортные расходы | 10 000 | руб. |
| Непредвиденные расходы | 5 000 | руб. |
| Страхование при транспортировке | 2 | % от суммы кредита с процентами + интерес |
| Итого страхование в пути | 128 706,79 | руб. |
| Реализация товара и возврат кредита | ||
| Цена реализации за единицу | 3 700 | руб. |
| Цена реализации за всю партию | 6 660 000 | руб. |
| Возврат кредита с процентами | 6 435 339,69 | руб. |
| Прибыль до вычета налогов | 224 660,31 | руб. |
Расчет страхования при транспортировке
По данным таблицы 2.2 построена круговая диаграмма, отображающая процентное соотношение между ценой партии и страхованием в пути.
Процентное соотношение между ценой партии и страхованием в пути
Рис. 2.1
По данным таблицы 2.2 и круговой диаграммы видно, что страхование при транспортировке составит 2% от суммы кредита с процентами плюс интерес. Итого страхование в пути будет равным 128 706,79 рублей. Процентное соотношение между ценой всей партии и страхованием в пути составит 3% к 97%.
2.3 Задание 3
Вычислить таможенные пошлины, которые необходимо выплатить при перевозке груза. Все таможенные пошлины за исключением НДС вычисляются как процент от суммы контракта. НДС вычисляется по формуле:
НДС=(Сумма контракта + Сумма всех таможенных платежей)*Процент НДС
Внести изменения в электронную таблицу, полученную в предыдущем задании. По полученным данным построить диаграмму подходящего вида, отображающую цену всей партии, таможенный налог, реализации за всю партию и полученную прибыль.
Вычисления всех таможенных пошлин, НДС и получаемой прибыли после выплаты всех налогов приведены в таблице 2.3.
Таблица 2.3
| Наименование | Значение | Единицы измерения | Значение | Ед. изм. |
| 1 | 2 | 3 | 4 | 5 |
| Цена за единицу | 2 600,00 | руб. | ||
| Количество единиц | 1800 | тонн | ||
| Цена всей партии | 4 680 000,00 | руб. | ||
| Кредит | ||||
| Проценты по кредиту | 60 | % годовых | ||
| Срок кредита | 3 | месяца | ||
| Страховка кредита | 12 | % от суммы кредита с процентами | ||
Продолжение таблицы 2.3
| 1 | 2 | 3 | 4 | 5 |
| или | 1 047 958,25 | руб. | ||
| Необходимая сумма кредита | 7 593 900,36 | руб. | ||
| Проценты по кредиту за каждый месяц | 379 695,02 | руб. | ||
| Накладные расходы | ||||
| Транспортные расходы | 10 000,00 | руб. | ||
| Непредвиденные расходы | 5 000,00 | руб. | ||
| Страхование при транспортировке | 2 | % от суммы кредита с процентами + интерес | ||
| Итого страхование в пути | 174 659,71 | руб. | ||
| Налог на добавленную стоимость | 18 | % или | 969 602,40 | руб. |
| Таможенный сбор | 0,1 | % или | 4 680,00 | руб. |
| Таможенный налог | 15 | % или | 702 000,00 | руб. |
| Реализация товара и возврат кредита | ||||
| Цена реализации за единицу | 3 700,00 | руб. | ||
| Цена реализации за всю партию | 6 660 000,00 | руб. | ||
| Возврат кредита с процентами | 8 732 985,41 | руб. | ||
| Полученная прибыль | -2 072 985,41 | руб. | ||
Вычисления таможенных пошлин и прибыли
По данным таблицы 2.3 построена диаграмма, отображающая цену всей партии, таможенный налог, реализацию за всю партию и полученную прибыль.
Соотношение между ценой реализации за всю партию и прибылью
Рис. 2.3
По данным таблицы и построенной диаграммы видно, что при уплате всех налогов, а именно: налога на добавленную стоимость 18% или 969 602,40 рублей, таможенного налога 15%, что в денежном выражении составляет 702 000,00 рублей и таможенного сбора 0,1% от суммы сделки (4 680,00 рублей) получается не прибыли, а внушительный убыток -2 072 985,41 рублей.
2.4 Задание 4
В результате расчетов в курсовой работы получаются убытки, а не прибыль. Необходимо увеличить или уменьшить некоторые показатели для получения прибыли. Вычислить эти изменения и рассчитать получаемую при этом прибыль. Подбор показателей для получения прибыли приведен в таблице 2.4.
Таблица 2.4
| Наименование | Значение | Единицы измерения | Значение | Ед. изм. |
| 1 | 2 | 3 | 4 | 5 |
| Цена за единицу | 2 600,00 | руб. | ||
| Количество единиц | 4000 | тонн | ||
| Цена всей партии | 10 400 000,00 | руб. | ||
| Кредит | ||||
| Проценты по кредиту | 30 | % годовых | ||
| Срок кредита | 3 | месяца | ||
| Страховка кредита | 12 | % от суммы кредита с процентами | ||
| или | 2 147 226,94 | руб. | ||
| Необходимая сумма кредита | 16 645 170,10 | руб. | ||
Продолжение таблицы 2.4
| 1 | 2 | 3 | 4 | 5 |
| Проценты по кредиту за каждый месяц | 416 129,25 | руб. | ||
| Накладные расходы | ||||
| Транспортные расходы | 10 000,00 | руб. | ||
| Непредвиденные расходы | 5 000,00 | руб. | ||
| Страхование при транспортировке | 2 | % от суммы кредита с процентами + интерес | ||
| Итого страхование в пути | 357 871,16 | руб. | ||
| Налог на добавленную стоимость | 18 | % или | 2 154 672,00 | руб. |
| Таможенный сбор | 0,1 | % или | 10 400,00 | руб. |
| Таможенный налог | 15 | % или | 1 560 000,00 | руб. |
| Реализация товара и возврат кредита | ||||
| Цена реализации за единицу | 4 800,00 | руб. | ||
| Цена реализации за всю партию | 19 200 000,00 | руб. | ||
| Возврат кредита с процентами | 17 893 557,86 | руб. | ||
| Полученная прибыль | 1 306 442,14 | руб. | ||
Подбор показателей
По данным таблицы 2.4 видно, что при увеличении количества единиц до 4000 тонн и при увеличении цены за реализацию за единицу до 4 800,00 рублей, повысятся, соответственно, цена всей партии до 10 400 000,00 рублей и цена реализации за всю партию до 19 200 000,00 рублей. При уменьшении процента по кредиту до 30%, необходимая сумма кредита возрастет до 16 645 170,10 рублей. При вычислении этих изменений возможно получение прибыли. Она составит 1 306 442,14 рублей.
ЗАКЛЮЧЕНИЕ
Тема курсовой работы была полностью раскрыта, а именно, я показал, как можно использовать Excel для создания технико-экономического обоснования и других финансовых расчетов, сконструировав свою работу в виде решения финансово-экономических задач с пояснениями.
В работе я рассмотрел технико-экономическое обоснование для получения кредита в банке, служащую для предоставления ему уверенности в том, что будет возвращена вся сумма денег. Не большая работа в программе Excel помогла создать документ, который может принести немалые деньги предприимчивому человеку. Расчет прибыли при условии ежемесячного погашения кредита может помочь получить двойную выгоду, при условии, конечно, что банк доверился технико-экономическому обоснованию.
Практической части работы я создал технико-экономическое обоснование. Оно представлено в виде готовых таблиц и диаграмм с вытекающими из них выводами. Эта работа состоит из этапов, представленных в виде заданий.
В первом задании показано, что сумма кредита составит 5 446 635,73 рублей, а прибыль без вычета налогов – 396 368,91 рублей.
Во втором задании рассчитано, что страхование в пути будет равным 128 706,79 рублей.
В третьем задании видно, что при уплате всех налогов, а именно: налога на добавленную стоимость 18% или 969 602,40 рублей, таможенного налога 15%, что в денежном выражении составляет 702 000,00 рублей и таможенного сбора 0,1% от суммы сделки (4 680,00 рублей) получается не прибыль, а внушительный убыток -2 072 985,41 рублей.
В четвертом задании производится подбор более оптимистичных показателей. При этом видно, что при увеличении количества единиц до 4000 тонн и при увеличении цены за реализацию за единицу до 4 800,00 рублей, повысятся, соответственно, цена всей партии до 10 400 000,00 рублей и цена реализации за всю партию до 19 200 000,00 рублей. При уменьшении процента по кредиту до 30%, необходимая сумма кредита возрастет до 16 645 170,10 рублей. При вычислении этих изменений возможно получение прибыли. Она составит 1 306 442,14 рублей.
Существует множество задач, финансовых или иных, решение которых могут облегчить электронные таблицы Excel. В своей работе я показал лишь одни из них. Уверен, что в дальнейшем люди будут чаще и увереннее обращаться к этой, актуальной уже много лет программе.
СПИСОК ЛИТЕРАТУРЫ
1. Гельман В.Я. Решение математических задач средствами Excel: Практикум. СПб.: Издательство Питер, 2003 г.
2. Комягина В. Г. Компьютер для менеджера. Быстрый старт. М.: Издательство ТРИУМФ, 1998 г.
3. Симонович С. В. Информатика для юристов и экономистов. СПб.: Издательство Питер, 2004 г.