Nicole会计培训

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 52|回复: 0

[5/1]Excel Homework

[复制链接]

112

主题

178

帖子

830

积分

高级会员

Rank: 4

积分
830
发表于 2019-1-5 20:58:36 | 显示全部楼层 |阅读模式
本帖最后由 nicky37 于 2019-1-5 21:09 编辑

1. Homework 8 - PnL analysis
Use vlookup and match to complete the PnL analysis template, use conditional formatting to highlight the top 10 variance.

2.Homework 1-AR Analysis
Use Index&March calculate aging(column Y), use vlookup to calculate provision for bad debts(column Z), and finally calculate Accounts Receivable Net(column AA).

3. Homework 9 - Supplier vlookup
Use vlookup/left/search/if formula to compare the check if the invoice amount from supplier is correct compared with system record.


4.Homework 10
User vlookup/index&match to lookup the product name, cost center, department name and sales rep.(Note:client list and department/product lists are in csv or txt format, please import to excel first).
User approximate vlookup to look up the age group.
Create a pivot table per attached 3rd picture.

5.
Homework 11 - Client comparison dashboard
Create a dashboard as per 2nd picture attached from <dashboard raw data file>.

a). Create 4 named tables for monthly sales/monthly consulting margin/monthly costs/monthly mis expense
b). Created 4 option buttons and put cell link in O3
c). Use choose formula to link the index number in cell O3 to the 4 named tables
d). Create 2 drop down lists for customers
e). Create a monthly sales table by comparing two customers, link the customer to drop down lists and use vlookup to get the monthly figures from the table chosen by option button.
f). Create a line chart with the data in the comparison table

6.Homework 12- FTE Sales Dashboard
Finish the calculations in calculations tab with instructions on the excel spreadsheet(see 1st screenshot for example).



本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|小黑屋|Nicole会计培训

GMT+10, 2019-1-18 04:54 , Processed in 0.120538 second(s), 22 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表