Excel商务应用及建模

excel提升，提高工作效率，避免重复工作，Excel广泛应用，易学易用，形象分析，内置数学和统计工具非常适合建立经济管理模型，与其它专业软件兼容性强。

Excel商务应用及建模

• Excel建模理论与优势（含定义名称）
  • Excel建模优势
    • Excel广泛应用，易学易用，形象分析，内置数学和统计工具非常适合建立经济管理模型，与其它专业软件兼容性强
  • 建模好处
    • 让复杂的事情简单化
    • 避免重复劳动
    • 提高决策效率
    • 适应各种变化
    • 提供可视性表达方式
  • Excel模版
    • 模板是职场达人工作经验的总结，可以通过模板学习Excel
    • 调用模板
      • “文件”——“新建”——选中您所想要的模板，点击“创建”即可。
    • 自建模板
      • “文件”——“另存为”——保存类型选择“Excel模板”。模板文件的后缀名为“xltx”
    • 尽量减少手工录入劳动量，避免重复性劳动。 数据录入一次性原则。其它地方引用 数据录入限制。比如数据的类型、数据的大小、文本型数据的长度等。还可以对单元格设置保护及访问权限，防止员工误操作。
      • 数据录入原则
      • “开始”——“单元格”——“格式”——“设置单元格格式”，“数字”选项卡——自定义。
        • 针对产品编码，前面“4010209”为重复值部分，自定义格式为："4010209"000。后面的3个“0”，代表占位符。
        • 对于不固定长度的编码，可以启用占位符“#”。（输入什么就显示什么，不用补齐数据位）
      • 对单元格命名
        • 单个，【公式】—【定义的名称】—【定义名称】
        • 多个，【公式】—【定义的名称】—【根据所选内容创建】
        • 通过名称管理器，可以新建、编辑、删除、筛选以及命名的单元格（或区域）。
      • 单元格样式，内置样式，表格样式的使用
      • 条件格式，【开始】—【条件格式】
      • 用颜色隔行（隔列）显示Excel表格数据
        • 【开始】—【条件格式】—【新建规则】—【使用公式确定要设置格式的单元格】
        • 如果要偶数行颜色显示，就在公式栏里输入“=MOD(ROW(),2)=0”（奇数行公式“=MOD(ROW(),2)=1”）。点击【格式】选择颜色，最后【确定】
        • 如果要偶数列颜色显示，就在公式栏里输入“=MOD(COLUMN(),2)=0”（奇数列公式“=MOD(COLUMN(),2)=1”）。点击【格式】选择颜色，最后【确定】。
• 销售门店管理（含二级联动）
  • 设计
    • • 建立价目表：商品编号、商品名称、商品类别,规格、计量单位、进价、售价
      • 定义价目表名称
      • 建立参数表：二级联动，定义名称，一级商品名称，二级商品编号
      • 在日报表设置数据验证-序列，
        • • 商品类别
          • • 商品名称
            • • 商品编号
      • 在日报表中使用iferror(vlookup())函数根据商品编号引用规格、进价、售价
      • 在日报表中设置销售量单元格格式
      • 根据日报表中商品名称，销售量，成本额，销售额，毛利列定义名称
      • 在汇总表中用sumif（）函数汇总销售量，成本额，销售额，毛利
      • 在汇总表中用sum（）汇总
      • 给设置了公式的单元格设置保护
  • 相关
• 快速批量打印邀请函类（含邮件合并）
  • 批量制作邀请函
    • 将Excel数据与Word文档之间建立关联；
    • 在邀请函中嵌入已经链接的数据（受邀请人），根据性别判断在客户姓名后面添加“先生”还是“女士”，生成邀请函模板。
    • 利用计算机自动执行功能，快速复制模板
  • 批量制作标签/信封
    • 第一步，选择标签/信封。注意规格。 第二步，数据链接。将Excel数据与Word文档域之间建立关联； 第三步，完善模板。 第四步，利用计算机自动执行功能，快速复制模板。
• 成本/收益-(含规则求解)
  • 工人工资核算
    • 建立基本信息表，工种分类，使用if（），核算
  • 合理订货
    • • 订货量 货款总额 400 32000 420 33600 440 35200 460 36800 480 38400 500 38000 520 39520 540 41040 900 68400 920 69920 940 71440 960 72960 980 74480 1000 72000 1020 73440 1040 74880 1060 76320
  • 交货天数与工资成本
    • 1.交货期限制：5-16天 2.每天加班时间不得超过6小时
    • 订单必要劳动时间=订单数*单位产品劳动时间
    • 标准劳动小时=交货天数*每天标准小时
    • 加班劳动小时=订单劳动时间-标准劳动小时
  • 旅店客房盈亏
    • 某旅游饭店共有客房300间，全部客房年度固定成本总额为300万美元，每间客房每天变动成本为10美元，预计客房年平均出租率为80％，营业税率为5％。 1.求该饭店客房保本时的价格； 2.求该饭店要实现年盈利100万元的目标的价格； 3.分析单位可变成本的变化对盈亏平衡价格的影响。
      • 全年收入=总客房数*A10*客房年平均出租率*全年营业天数*(1-营业税率)
      • 全年成本=全年固定成本+总客房数*单位可变成本*客房年平均出租率*全年营业天数
      • 盈利=全年收入-全年成本
    • 总客房数 300 全年固定成本 3000000 单位可变成本 10 客房年平均出租率 80% 营业税率 5% 全年营业天数 365
      • 客房价格 全年成本 全年收入 盈利 29 3876000 2413380 -1462620 32 3876000 2663040 -1212960 35 3876000 2912700 -963300 38 3876000 3162360 -713640 41 3876000 3412020 -463980 44 3876000 3661680 -214320 47 3876000 3911340 35340 50 3876000 4161000 285000 53 3876000 4410660 534660 56 3876000 4660320 784320 59 3876000 4909980 1033980 62 3876000 5159640 1283640 65 3876000 5409300 1533300
        • • • 求解结果是 数学求解结果 不一定是可行解， 需要多方判断
  • 经济订货量
    • 年需求总量 4000 单位储存成本 3 每次订货成本 20 每次订货量 200 订货次数 20 订货成本 400 储存成本 300 总成本 700
  • 销售盈亏
    • 某种商品生产的直接劳动成本为每单位7.50美元，原材料成本为每单位2美元。目前该商品市场销售价格为22美元，广告费和销售人员费用为40万美元，其他相关固定成本为10万美元。建立模型，计算保本销售的最低销售量。
    • 单位劳动成本 7.5 单位原料成本 2 销售价格 22 "广告费和 销售人员费用 400000 其他固定成本 100000
      • 销售量 总成本 销售额 盈利 10000 595000 220000 -375000 15000 642500 330000 -312500 20000 690000 440000 -250000 25000 737500 550000 -187500 30000 785000 660000 -125000 35000 832500 770000 -62500 40000 880000 880000 0 45000 927500 990000 62500 50000 975000 1100000 125000 55000 1022500 1210000 187500 60000 1070000 1320000 250000 65000 1117500 1430000 312500 70000 1165000 1540000 375000 75000 1212500 1650000 437500 80000 1260000 1760000 500000 85000 1307500 1870000 562500
  • 生产优化,利润最优
    • Blue Ridge卫浴公司生产A、B两种型号的浴缸，在下一个生产周期中，卫浴公司可用的资源有200台水泵、1566个工时和2880英尺水管。 生产一个A型号浴缸需要9个工时和12英尺的水管，可以获得利润350美元；生产一个B型号浴缸需要6个工时和16英尺的水管，可以获得利润300美元。在资源有限的条件下，如何组织生产才能获取最大利润呢？
  • 模拟库存
    • 公司每天可生产A商品1000个单位，每天的订单量不固定，在970上下10％范围内波动，每个整数值都有相同的出现概率。 如果订单量小于生产数量，就会产生库存，库存成本为每件商品每天2元；如果订单量大于生产数量，就可以消耗库存，并产生每件商品50元的额外收益。公司高层需要在库存成本、额外收益之间做出决策。 假设上期库存为30个单位，19天为一个生产周期。请建立模型，模拟一个生产周期内每天的订单量、库存变化，并计算可能的盈利。
• 投资相关（含模拟分析）
  • 财务函数
    • 在财务函数的应用中，凡是投资的金额都以负数形式表示，收益以正数形式表示。
    • 终值函数FV（）
    • 利率函数Rate（），投资回报率
      • 投资30000，连续五年每年得9000，投资回报率多少
        • 年末投资回收率=RATE(偿还周期,每年的回报额,-投资金额,) 年初投资回收率=RATE(偿还周期,每年的回报额,-投资金额,,1)
    • 期数函数Nper（）
      • 投资30000，每年得9000，要求投资回报率20%，问多少年可以收回全部投资
        • 偿还周期（年末）=NPER(投资回收率,每年的回报额,-投资金额,,0) 偿还周期（年初）=NPER(投资回收率,每年的回报额,-投资金额,,1)
    • 财务函数一览
  • 现值敏感性分析-模拟分析
    • 单变量
    • 2个变量
  • 投资评估-经济可行性
    • 判断一项投资经济上的可行性，主要是比较收益和成本。 如果收益>成本，则经济上可行；如果收益<成本，则经济上不可行。 注意这里的成本是投资存银行所得的收入。
      • 首先要建立模型，建立模型时要注意： 1.简单明了，便于阅读 2.成本记为负，收益记为正 3.注意时间序列的连续性 如果某年没有投资，也没有收益，记为0，但不能删除
    • 假设投资10,000元，未来三年可获得3,000、4,200和6,800元的收入。 假定每年的贴现率是10%，评估该项投资是否具有经济可行性。 【净现值函数NPV(),内部收益率函数IRR()】
• 经济预测及分析（含回归分析）
  • 回归分析是一种预测性的建模技术，它研究的是因变量（目标）和自变量（预测）之间的因果关系，
  • 某公司开发出一种新牌子化肥，下表为不同施用量对庄稼产量的试验数据。求化肥最佳使用量
    • • 线性回归，可用一个线性方程y=a+bx 去拟合一系列变量x和y的观测值。
      • 绘制散点图、添加趋势线（一元，线性，非线性）
    • 多元只适合数据分析方法
  • 相关性分析是检验衡量两变量关联强度的过程。其关系表现为以下三种变化； 第一，正相关：一个变量增加或减少时，另一个变量也相应增加或减少； 第二，负相关：一个变量增加或减少时，另一个变量却减少或增加； 第三，无相关：说明两个变量是独立的，即由一个变量值，无法预测另一个变量值。 统计学中，就用“相关系数”来从数量上描述两个变量之间的相关程度，用符号“r”来表示。 相关性系数的计算有两种方法，一是应用Correl()函数，二是数据分析中的相关分析功能。
• 浮动主题
• 浮动主题
• 浮动主题