会计、Excel 和Financial Model交流分享帖

Aotearoa

jiaqi_518

how can I attend your workshops?

vicpark

有没有什么学习资料可以分享呢，谢谢

AFMA

jungle 发表于 2015-5-15 15:16
呵呵，本人自认为也算是EXCEL方面的高手，好歹当年读MBA时在Financial Modelling这门课拿到全班唯一的HD。 ...

用Excel的PowerPivot可以实现你要的Distinct Count，详见附件。

jungle

AFMA 发表于 2015-5-18 10:12
用Excel的PowerPivot可以实现你要的Distinct Count，详见附件。

LZ果然是高手，，不过看起来POWERPIVOT是个要额外安装的Add-in，我们公司这个环境比较成问题，还要找IT来安装，比较麻烦的。先多谢啦！

我出道题哈，这个是我当年FINANCIAL MODELLING这门课里面的individual assignment，应当说是一个相对比较复杂的优化计算模型。我认为它完全是个拿来考程序员的题，需要通过深度优先的递归遍历算法来解决。我写了几天程序把它搞定了。。。老师说，有简单的办法可以解决~~但是直到最后他也没有说这个简单的办法究竟是啥。我严重怀疑他是忽悠的，所以纠结至今。

题目如下：
-----------------------------------------------------------------------
HTC (Hardwood Timber Corp) 是一家生产实木材料的公司。其原材料为Dressed Log，就是说横截面已经切割成矩形的原木（即一个长条形的长方体柱，比如截面积长400mm, 宽300mm，高1m）。现在该公司基于该原材料进行切割，生产两种产品：
1. 横截面150mm*150mm的木条，每米售价$35
2. 横截面45mm*45mm的木条，每米售价$3.5

现在要注意，锯子的宽度为8mm，就是说锯子所到的8mm范围内的木头会被锯成木屑，但是木屑不是说就浪费了，也可以卖。每升（立方分米）木屑售价$0.5。

请设计一个MODEL计算最优的生产方案，使得基于原材料生产的最终的产品组合，包括产品1，产品2和木屑的总价值最高。
-----------------------------------------------------------------------

当然这个model需要考虑到，题目里面每一个给定的数字其实都可变。比如说，如果木屑价格提高，高过其他产品的话，把整个原材料全部切割成木屑也是可以的。

LZ请考虑一下这个模型看看？

sunnyclh1211

jungle 发表于 2015-5-18 12:03
LZ果然是高手，，不过看起来POWERPIVOT是个要额外安装的Add-in，我们公司这个环境比较成问题，还要找IT来 ...

这个是几何数学题啊？

jungle

sunnyclh1211 发表于 2015-5-18 12:40
这个是几何数学题啊？

这位同学，我当时认为它应该是拿来考程序员的算法题。但是偏偏它就是拿来考MBA学生的Financial Model题目。

sunnyclh1211

jungle 发表于 2015-5-18 13:14
这位同学，我当时认为它应该是拿来考程序员的算法题。但是偏偏它就是拿来考MBA学生的Financial Model题目 ...

我在纸上画了半天也没搞出个所以然

本以为可以画出几个senario， 然后每个senario 放入变量，最后做sumif最优选择. 可是要画出几个senario不就是几何题吗？

还是说modeling 自有算法可以automatic 算出有几种切法？而且还要考虑到一刀损失8mm, 边缘那块没有8mm损失

你如何做的，偶好有兴趣哦。

jungle

sunnyclh1211 发表于 2015-5-18 17:28
我在纸上画了半天也没搞出个所以然

本以为可以画出几个senario， 然后每个senario 放入变量，最后做sumi ...

呵呵，难得这位同学那么好奇啊，那我贴几个图给你看我的解法：

模型参数设置：


图形化展示切割方案：


表格展示切割方案具体内容，包括切割顺序，每一刀下去产生的产品，以及每个产品的价值计算：


这里具体解释一下。总之每一刀下去都产生A，B，C三个产品，A和C可能是一个中间产品（留待后面继续切割），也可以是产品1，产品2或者woodchip（木片，即剩余的尺寸不足以再切成任何产品）；而B永远是木屑。产品的编号是这样：
第一刀下去，产生 A B C 三个产品
接下来如果选择A作为第二刀的切割对象，产生 AA，AB，AC 三个产品
接下来如果选择AC作为第三刀的切割对象，产生 ACA，ACB，ACC三个产品，依此类推。

我的解法很简单，纯属回溯法编程，即遍历所有的可能切割方案，每一个都切到不能再切为止，最后选择产生的产品价值最大的那一个方案。实际上，参数稍微设置复杂点，所有可能的切割方案轻易就可以达到几万的数量级。产品再复杂点的话，达到几百万的数量级也不奇怪。这时候程序就要run比较久了。

所以，我严重怀疑老师说的，这道题有很简单的，基于EXCEL本身提供的功能的解法。

AFMA

jungle 发表于 2015-5-18 20:35
呵呵，难得这位同学那么好奇啊，那我贴几个图给你看我的解法：

模型参数设置：

我用Excel做了一个简单的模型，结果和你的很接近，详见附件。只是我的几何知识几乎都还给数学老师了 ，有可能Model中的条件设置会有些问题，特别是米，分米那些刻度的转换

sunnyclh1211

jungle 发表于 2015-5-18 20:35
呵呵，难得这位同学那么好奇啊，那我贴几个图给你看我的解法：

模型参数设置：

我觉得可以把木条木屑价格统一转换为price per m3
然后compare price if a>b>c, 走option 1, maximize cutting a, rest maximize cutting b, rest 木屑
if b>a>c, option 2
if c>a>b, option 3, 一共6个option


价格已知就更容易了， a= 35*/0.158*0.158*1+木屑=1402/m3+木屑
b=3.5/0.053*0.053*1+木屑=1246/m3 +木屑
c= 0.5*1000*木屑膨胀系数假设2=1000/m3

因为a>b>c, 走option 1
maximise cutting a
方程： 横排数量=取整【（400+8）/158】=2
         竖排数量=取整【（300+8）/158】=1
a木块总数=横排X竖排=2X1=2块

         横排数量=取整【（400+8）/53】=7
         竖排数量=取整【（300-158X1+8）/53】=2
         剩余木块切割
         横排数量=取整【（400+8-158*2）/53】=1
         竖排数量=取整【（300-53*2+8）/53】=3
b木块数量=7X2+1*3=17

剩余的可切割成木屑的木块容积为0.4*0.3*1-2*0.15*0.15*1-17*0.045*0.045*1=0.040575立方米
木屑体积为0.040575*膨胀系数假设2=0.08M3=80升

总价格自己算吧

楼主看看这个方法如何？
输入价格，价格为变量
输出为： 木块a数量，木块b数量，木屑数量，最优价格组合和总价值

sunnyclh1211

AFMA 发表于 2015-5-20 11:38
我用Excel做了一个简单的模型，结果和你的很接近，详见附件。只是我的几何知识几乎都还给数学老师了  ...

能解释一下这模型啥意思吗？

AFMA

sunnyclh1211 发表于 2015-5-20 13:04
能解释一下这模型啥意思吗？

假设150mm的木条数量为X，假设45mm的木条数量为Y。每块150mm的木条会产生(150+150+8)*8平方mm的木屑，每块45mm的木条会产生(45+45+8)*8平方mm的木屑。

然后可由Excel进行在以下条件约束下进行最优决策，使得X*35+X*(150+150+8)*8*0.5/1000000+Y*3.5+Y*0.5/1000000*8*(45+45+8)的值最大。

条件1. （150+8）*X<=400
条件2.   所有150mm的木条、45mm的木条、木屑和最后的边角料的面积总和要小于原材料的横截面积：
（150^2+(150+158)*8）*X+45^2+(45+45+8)*8*Y+MOD(400,53)*300+MOD(300,53)*400-MOD(400,53)*MOD(300,53)<=300*400

取整后得出X=2,Y=17

Adam123

AFMA 发表于 2015-5-21 21:45
假设150mm的木条数量为X，假设45mm的木条数量为Y。每块150mm的木条会产生(150+150+8)*8平方mm的木屑，每 ...

我试了一下，楼主的这个很直接明了，谢谢分享～

stephanie2009

LZ, pls pm wechat id, thx

bring32

楼主看在我们周五周六晚上还在虚心求教的份上就收了我们进wechat吧
最近需要做个小型modeling，正愁着没灵感

gavin_zk

留爪

ziyu

I am working as an analyst, normally, we use excel to extract raw data, clean and manipulate data. Excel only can build some financial models for  simple projects.

Very complex models cannot be built in Excel, they are usually developed in special ABC software or business software, such as BI,

AFMA

ziyu 发表于 2015-6-19 15:27
I am working as an analyst, normally, we use excel to extract raw data, clean and manipulate data. E ...

我们也用BI软件，但Excel还是财务分析和管理会计工作中应用最广泛的工具。我们经常需要把BI cube的数据slice到Excel中作进一步的裁剪、分析和综合。
而且对于财务分析工作，最头痛的不是routine的分析报告，而是一些突发的、one-off的分析报告。这时候我们没时间在BI 软件里建模，一般先用Excel 做，然后再考虑是否需要把它放到BI中。

天籁

Good

ziyu

AFMA 发表于 2015-6-21 20:40
我们也用BI软件，但Excel还是财务分析和管理会计工作中应用最广泛的工具。我们经常需要把BI cube的数据sl ...

我们不是用BI, 我们是大学，有自己的独立的costing/revenue system.

这之前我还用其他的一些系统做过model。

Excel肯定是经常总用，但是也只是上传数据，下载报告。所有的model都不用excel了。

aboutblank

ddddddddddd

rebecca83

最近正好遇到烦心的 一个季度要做一次的分析 每次都是n张几十万行数据的excel表要link到一起 得出最终数据然后才可以进行分析 用很多formula 然后因为数据太庞大每次计算都要很久 等的我崩溃......

不会用macro，请问这种情况用macro可以解决问题吗？可以的话我就去学......

工作地方没有给统计软件.....

Lilikachen

同行 加一个。互相学习

weverydayl

还请问如果要学习macro 是不是很难？

晴天宝宝

我老公是macro writer，但是不懂modelling，他说似乎澳洲对macro没啥需求啊

AFMA

rebecca83 发表于 2015-6-23 22:45
最近正好遇到烦心的 一个季度要做一次的分析 每次都是n张几十万行数据的excel表要link到一起 得出最终数据 ...

这个你可以用Power Pivot（Excel的一个插件）。

AFMA

weverydayl 发表于 2015-6-23 23:57
还请问如果要学习macro 是不是很难？

其实也不是很难。

AFMA

浅析SQL在Excel中的应用

SQL 是StructuredQuery Language 的缩写，是 relational database management systems的标准语言。虽然SQL有不同的版本，但都遵循ANSI (AmericanNational Standards Institute) standard。

目前，我们大多数人都认为SQL只能使用在MicrosoftSQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access等相关数据库软件的应用上。其实，在Excel导入外部数据QLEDB和ODBC等方式中，也可以运用基本的SQL语言。这样就为我们大家在使用Excel时，打开了一扇崭新的大门，我们可以不用Excel函数，技巧和VBA来解决一些问题，简化一些繁琐的流程，建立更好的模型。

替代Vlookup的LEFT OUTER JOIN/LEFT JOIN
我们大家都熟悉Excel的常用公式Vlooup，那么Vlookup这种多对一或一对一的关系我们可以用多种SQL查询来实现。例如LEFTOUTER JOIN/LEFT JOIN。




上面第一张图，是我们的原始数据表，左边的Data表有城市，州的信息，右边的Table表有城市和人口数量。我们当然可以用Vlookup来查找左边Data表中城市所对应的人口数量，但这里我们用SQL来查询这些城市的人口。


上面第二张图，Lookup表中是我们用SQL来查询到的城市人口数量。请注意人口数量列的空白处是因为在Table表中没有这些城市的数据。ConnectionProperties中的Commandtext: 部分是我们所写的SQL命令，这里我们用到了SELECT，FROM，LEFTJOIN…ON等常用命令

这里只是抛砖引玉，接下来会从SQL理论和实践的基础展开介绍。也希望各位平时使用SQL的大拿们参与讲解讨论。

向阳小太阳

谢谢分享此帖