Creating A Sales Report 创建销售报告
In this project you will create a sales report for the company “Universal Exports”. This report will include two “Sheets”. The first “Sheet” will be an internal report for the sales department. The second “Sheet” will be a summary for the client.
为“Universal Exports”公司创建一个销售报告。该报告将包括两个“表”。第一个“表”是销售部门的一份内部报告。第二个“表”是提供给客户的总结。
In addition to the sales price and taxes for a product, companies will need to keep track of a products cost, profits, as well as commissions for sales representative. It is important not to share this information with clients though. Therefore it is important for large companies to make documents for internal use as well as reports for their clients that may share some of the same information.
除了产品销售价格和税收之外，企业还需要跟踪产品成本，利润，以及销售代表的提成，但是这些信息是对客户保密的。因此一些大型公司需要有内部的报告，同时也要将报告的备份信息呈现给客户。
For this project you will decide what type of company “Universal Exports” will be and the types of products they will sell. Then decide on a color scheme that you will use for the company.
在这个项目中，首先为“Universal Exports”定性，即该公司属于哪一类公司，销售什么类型的产品。其次为该公司选择一组你将使用的色彩。
For example if you decide that “Universal Exports” is a company selling environmental products you might chose various shades of Green for your company colors. If you chose women’s fashion you might chose the color Pink. The colors you chose for your company will be the colors you use to design your report.
例如，如果你决定“Universal Exports”是一家销售环保产品的公司，你可能会为你的公司选择不同深浅的绿色。如果你决定其是一家销售女性时尚品的公司，你可能会选择粉红色。为公司选择的颜色将成为你报告中使用的颜色。
You will then decide 10 different products to include in this sales report.
然后，在此销售报告设定10种不同的产品。
Instructions for formatting the “Internal Sales Report” on Sheet 1
表1设计“内部销售报告”的说明
- Row 1 Leave blank
第1行 空白 - Row 2 Merge Cells “B” to “L”, and type:
第2行合并B栏到L栏，并输入:
“Universal Exports | Internal Sales Report 内部销售报告” - Row 3 leave blank
第3行 空白 - Row 4 type the following headings starting in Column B:
第4行在B栏输入下列小标题:
“Product, Sales Price, Cost, Gross Profit, Gross Profit Margin, Commission, Operational Expense, Net Profit, Net Profit Margin, Import Tax, Total Retail” - In Row 5 through 14 enter product information for the company “Universal Exports”.
从第5行到14行输入“Universal Exports”公司的产品信息。 - Format the table using the colors you chose for the company.
用为公司选择的颜色设计该表格。 - Make the Columns “A” and “M’ narrow like the example.
把A栏和M栏调成如例子所示的宽度。 - The values for “Gross Profit Margin” and “Net Profit Margin” should stand out.
“Gross Profit Margin”和“Net Profit Margin”两栏须突出。 - Your table should look similar to the example once you have finished formatting it.
当表格设计完成时应该类似于如下例子。 - Instructions for calculations for the “Internal Sales Report” are as follows:
“内部销售报告”的计算说明在下一页。
- Column B: Product description
B栏：产品描述 - Column C: Sales Price
C栏：销售价格 - Column D: Manufacturing Cost
D栏：制造成本 - Column E: Enter a formula to calculate “Gross Profit”.
E栏：输入计算“毛利润”的公式
=sum(“Sale Price” – “Cost”) - Column F: Enter a formula to calculate “Gross Profit Margin”
F栏：输入计算“毛利率”的公式。 *把单元格设置为%数字格式。
=(“Gross Profit” / “Sale Price”) *Then format cells as a percent % number format. - Column G: Enter a formula to calculate a commission
G栏：输入计算提成的公式：用0.02，也就是2%的提成率。
=(“Price” * “Commission”) Use 0.02 ,that is, a 2% commission rate. - Column H: Enter a formula to calculate Operational Expense
H栏：输入计算“Operational Expense”的公式。每一个公司的操作花费都不同，但是大多数公司的平均数是“销成本格的24%”- Every company has a different operational expenses: However the average operational expense for most companies is 24% of their “Cost”.
=”Gross Profit” – (“Cost” * “0.24”)
- Every company has a different operational expenses: However the average operational expense for most companies is 24% of their “Cost”.
- Column I: Enter a formula to calculate “Net Profit”
I 栏：输入计算“净利润”的公式。
= “Gross Profit” – “Commission” – “Operational Expenses” - Column J: Enter a formula to calculate “Net Profit Margin”
J栏：输入计算“净利率”的公式。 *用百分数设计单元格。
=(“Net Profit” / “Sale Price”) * format cells using Percentage. - Column K: Enter a formula to calculate “Import Tax”
K栏：输入计算“Import Tax”的公式。用0.13，也就是13%的税收率。
=(“Sales Price” * “Tax Rate”) use 0.13. This is a 13% tax rate. - Column L: Enter a formula to calculate “Total Retail”
L栏：输入计算“Total Retail”的公式
=SUM (“Sales Price” + “Import Tax”) - In Row 15 enter an addition formula for each column that relate to prices, or If a column relates to profit margins use an average formula.
第15行为每一栏输入一个与价格相关的加法公式，若该栏是与利率相关的话用平均数公式。
=SUM ( “First Cell” : “Last Cell” ) or =AVERAGE ( “First Cell” : “Last Cell” )
Instructions for formatting the “Client Invoice” on Sheet 2
表2设计“顾客发票”说明
When providing information to a client (customer) it is important not to disclose your “cost”, “commissions” and “margins” for the products or services you sell. On “sheet 2” you will create an invoice for your client (customer). Some of the information between both reports will be the same. However each report will have its own unique information.
当为客户提供信息时，千万不要透露产品的“成本”、“提成”和“利润”。 “表2”将创建客户的发票。该报告的部分的信息与表1的“内部销售报告”相同，当然，每个报告都其独特之处。
Look at the example for the “Client Invoice” at the bottom of this page. This report will be bilingual. Look at where information is located in this report and recreate the layout. You will use the same colors that you used for your “Internal Sales Report”
如本页下例“客户发票”所示，这份报告须是双语的。注意每个信息在报告中的位置并重新创建报告，颜色须与之前的“内部销售报告”相同。
Make up an “invoice number” 制作“发票号码”
Enter your name as the “Employee” 输入你的姓名作为“员工”
Enter todays date: 输入当天日期：
- Column B: Enter a formula to reference the “product” descriptions on “sheet 1”
B栏:输入公式，即“表1”对“产品”的描述。也可以输入“=”之后选择你需要引用的单元格。或输入公式=“表单名” ！“引用单元格”（“X”栏 和 “#”行）。- This can also be done by typing “=” then selecting the cell you want to reference, or by typing the formula = “Sheet Name” ! “Cell Reference (column “X” and row “#”)”
- Column C: Enter a formula to reference the “Sale Price” on “sheet 1”
C栏：输入公式，即“表1”中的“销售价格”。 - Column D: Enter a “Quantity” for the number of each product the client will order .
D栏：为客户所订购的每一件产品输入“数量”。 - Column E: Use a multiplication formula to calculate the “Total Pre Tax” cost.
E栏：用乘法公式来计算“税前总成本”
= ( “Sales Price” * “Quantity Ordered” ) - Column F: Use a multiplication formula to calculate the total cost with “Import Tax”
F栏：用乘法公式来计算加上“进口税”之后的总成本。用0.13，也就是13%的税率。
=(“Sales Price” * “Tax Rate”) use 0.13. This is a 13% tax rate. - Column G: Use an addition formula to calculate the “Total Retail” cost.
G栏：使用加法公式计算“零售总额”的成本。
= ( “Total Pre Tax” + “Import Tax” ) - In Row 21: Use an addition formula to calculate the totals for each column.
第21行：使用加法公式计算每一栏的总和。
At the very bottom create a small table that shows the client (customer) the final total to be paid for the order.
最后创建一个小表格表明客户该支付的总金额。