案例14_预算模版

excel函数数据透视表筛选查询等应用

LDVAssumptions:

LEONARDO DI VINCI CORPORATION2000 BUDGET PLANNERBUDGET PLANNER Please enter your budget by setting the values in the unshaded areas. The blue areas are calculated.

Region Version Tactical Expenditures: Advertising Telemarketing This Year 175,101 225,430 Next Year 215,200 277,600

1 Jan 14,515 24,332 Jan 20,800 21,800

Advertising Telemarketing

Unit Sales Projections: Product Desktop Laptop 12 in. Color Monitor 14 in. Color Monitor 101 Key Keyboard Laser Printer Revenue Calculation: Product Desktop Laptop 12 in. Color Monitor 14 in. Color Monitor 101 Key Keyboard Laser Printer All Products Cost of Goods Sold Product Desktop Laptop 12 in. Color Monitor 14 in. Color Monitor 101 Key Keyboard Laser Printer All Products

Seasonality Markups: Est. Comptn 17% 17% 22% 22% 24% 30%

100% Ours 16% 13% 22% 21% 25% 27% This Year 8,103 12,793 2,618 3,956 851 4,785 Next Year

5% Jan 0 0 0 0 0 0

Cost Cost This Year Next Year 1,217.99 1,300 1,572.01 1,600 124.45 160 165.36 200 13.26 16 121.53 120

Price 1519.95 1819.95 209.95 249.95 29.95 159.95

Next Year -

Jan -

Next Year -

Jan -

Variable Costs

Percent of Revenue

excel函数数据透视表筛选查询等应用

Desktop This Year Next Year Warehousing 3% 2.90% Shipping 2% 1.90% Order Proc Staff 2% 2.10% Variable Benefits 0.00322704 0.00315 Laptop Warehousing Shipping Order Proc Staff Variable Benefits 12 in. Color Monitor Warehousing Shipping Order Proc Staff Variable Benefits 14 in. Color Monitor Warehousing Shipping Order Proc Staff Variable Benefits 101 Key Keyboard Warehousing Shipping Order Proc Staff Variable Benefits Laser Printer Warehousing Shipping Order Proc Staff Variable Benefits All Products Warehousing Shipping Order Proc Staff Variable Benefits This Year Next Year Percent of Telemarketing Long Distance 0.5799135 0.6 Fixed Costs Rent Copying Phones Office Supplies Banking Travel Insurance Legal Office Staff Fixed Benefits

Next Year 0 0 0 0 0 0 0

Jan 0 0 0 0 0 0 0

135258.102

14599.044

This Year Jan 529,051 41,510 38,917 2,884 33,151 2,320 29,602 2,147 15,142 1,284 37,510 3,344 42,002 3,506 31,389 2,734 521,787 37,965 64029.4 5587.87

excel函数数据透视表筛选查询等应用

Rent Copying Phones Office Supplies Banking Travel Insurance Legal Office Staff Fixed Benefits Fixed Benefits Total

Growth Rate Not used Not used Not used Not used Not used Not used Not used Not used Not used Not used

Next Year Jan 795,600 66,300 46,200 3,850 43,800 3,650 40,920 3,410 18,360 1,530 42,360 3,530 48,720 4,060 42,360 3,530 694,680 57,890 78,600 6,550 1773000 154300 Next Year 135,258 (135,258) 215,200 277,600 795,600 46,200 43,800 40,920 18,360 42,360 48,720 42,360 694,680 78,600 (2,479,658) Jan 14,599 (14,599) 20,800 21,800 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (211,499)

Financial Summary Revenue Cost of Goods Gross Margin Warehousing Shipping Order Proc Staff Variable Benefits Long Distance Net Margin Advertising Telemarketing Rent Copying Phones Office Supplies Banking Travel Insurance Legal Office Staff Fixed Benefits Before Tax Profit

excel函数数据透视表筛选查询等应用

re

calculated.

2 Feb 15,570 22,363 Feb 16,900 24,600

3 Mar 17,602 18,558 Mar 17,200 25,800

4 Apr 12,033 17,585 Apr 18,400 20,600

5 May 14,603 22,203 May 15,800 23,700

6 Jun 14,119 17,087 Jun 18,000 19,600

7 Jul 16,793 17,171 Jul 18,200 25,300

6% Feb 0 0 0 0 0 0

8% Mar 0 0 0 0 0 0

8% Apr 0 0 0 0 0 0

9% May 0 0 0 0 0 0

11% Jun 0 0 0 0 0 0

7% Jul 0 0 0 0 0 0

Feb -

Mar -

Apr -

May -

Jun -

Jul -

Feb -

Mar -

Apr -

May -

Jun -

Jul -

excel函数数据透视表筛选查询等应用

Feb 0 0 0 0 0 0 0

Mar 0 0 0 0 0 0 0

Apr 0 0 0 0 0 0 0

May 0 0 0 0 0 0 0

Jun 0 0 0 0 0 0 0

Jul 0 0 0 0 0 0 0

13417.788

11134.962

10551.126

13321.98

10252.224

10302.354

Feb 55,153 3,051 3,262 2,370 1,547 3,060 3,967 3,382 47,291 5134.15

Mar 43,137 3,648 2,999 2,584 1,094 2,645 3,371 2,346 50,792 5657.48

Apr 39,233 3,758 2,253 2,719 1,138 3,595 3,983 2,272 47,925 6026.57

May 45,040 2,704 3,074 2,450 1,093 2,670 3,423 2,917 40,725 4456.63

Jun 40,078 3,182 2,513 2,399 1,106 3,025 3,053 2,810 40,949 4970.47

Jul 42,812 2,959 2,961 2,562 1,389 3,635 3,568 2,382 38,643 4675.7

excel函数数据透视表筛选查询等应用

Feb 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 Feb 13,418 (13,418) 16,900 24,600 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (209,218)

Mar 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 Mar 11,135 (11,135) 17,200 25,800 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (208,435)

Apr 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 Apr 10,551 (10,551) 18,400 20,600 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (203,851)

May 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 May 13,322 (13,322) 15,800 23,700 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (207,122)

Jun 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 Jun 10,252 (10,252) 18,000 19,600 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (202,152)

Jul 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 154300 Jul 10,302 (10,302) 18,200 25,300 66,300 3,850 3,650 3,410 1,530 3,530 4,060 3,530 57,890 6,550 (208,102)

excel函数数据透视表筛选查询等应用

8 Aug 15,908 16,544 Aug 17,200 23,500

9 Sep 10,898 15,732 Sep 18,100 23,200

10 Oct 13,363 20,108 Oct 19,500 24,600

11 Nov 13,65 …… 此处隐藏：5072字，全部文档内容请下载后查看。喜欢就下载吧 ……