案例14_预算模版.xls

LEONARDO DI VINCI CORPORATION 2000 BUDGET PLANNERBUDGET PLANNER Please enter your budget by setting the values in the unshaded areas The blue areas are calculated Assumptions Region1 Version Tactical Expenditures This YearJan Advertising175 10114 515 Telemarketing225 43024 332 Next YearJan Advertising215 20020 800 Telemarketing277 60021 800 Seasonality100 5 Unit Sales Projections Markups ProductEst ComptnOursThis YearNext YearJan Desktop17 16 8 1030 Laptop17 13 12 7930 12 in Color Monitor22 22 2 6180 14 in Color Monitor22 21 3 9560 101 Key Keyboard24 25 8510 Laser Printer30 27 4 7850 Revenue Calculation CostCost ProductThis YearNext YearPriceNext YearJan Desktop1 217 991 3001519 95 Laptop1 572 011 6001819 95 12 in Color Monitor124 45160209 95 14 in Color Monitor165 36200249 95 101 Key Keyboard13 261629 95 Laser Printer121 53120159 95 All Products Cost of Goods Sold ProductNext YearJan Desktop Laptop 12 in Color Monitor 14 in Color Monitor 101 Key Keyboard Laser Printer All Products Variable CostsPercent of Revenue DesktopThis YearNext YearNext YearJan LDVLDVLDV Warehousing3 2 90 Shipping2 1 90 Order Proc Staff2 2 10 Variable Benefits0 003227040 0031500 Laptop Warehousing Shipping Order Proc Staff Variable Benefits00 12 in Color Monitor Warehousing Shipping Order Proc Staff Variable Benefits00 14 in Color Monitor Warehousing Shipping Order Proc Staff Variable Benefits00 101 Key Keyboard Warehousing Shipping Order Proc Staff Variable Benefits00 Laser Printer Warehousing Shipping Order Proc Staff Variable Benefits00 All Products Warehousing Shipping Order Proc Staff Variable Benefits00 This YearNext Year Percent of Telemarketing Long Distance0 57991350 6135258 10214599 044 Fixed Costs This YearJan Rent529 05141 510 Copying38 9172 884 Phones33 1512 320 Office Supplies29 6022 147 Banking15 1421 284 Travel37 5103 344 Insurance42 0023 506 Legal31 3892 734 Office Staff521 78737 965 Fixed Benefits64029 45587 87 Growth RateNext YearJan RentNot used795 60066 300 CopyingNot used46 2003 850 PhonesNot used43 8003 650 Office SuppliesNot used40 9203 410 BankingNot used18 3601 530 TravelNot used42 3603 530 InsuranceNot used48 7204 060 LegalNot used42 3603 530 Office StaffNot used694 68057 890 Fixed BenefitsNot used78 6006 550 Fixed Benefits Total1773000154300 Financial SummaryNext YearJan Revenue Cost of Goods Gross Margin Warehousing Shipping Order Proc Staff Variable Benefits Long Distance135 25814 599 Net Margin 135 258 14 599 Advertising215 20020 800 Telemarketing277 60021 800 Rent795 60066 300 Copying46 2003 850 Phones43 8003 650 Office Supplies40 9203 410 Banking18 3601 530 Travel42 3603 530 Insurance48 7204 060 Legal42 3603 530 Office Staff694 68057 890 Fixed Benefits78 6006 550 Before Tax Profit 2 479 658 211 499 234567 FebMarAprMayJunJul 15 57017 60212 03314 60314 11916 793 22 36318 55817 58522 20317 08717 171 FebMarAprMayJunJul 16 90017 20018 40015 80018 00018 200 24 60025 80020 60023 70019 60025 300 6 8 8 9 11 7 FebMarAprMayJunJul 000000 000000 000000 000000 000000 000000 FebMarAprMayJunJul FebMarAprMayJunJul FebMarAprMayJunJul Please enter your budget by setting the values in the unshaded areas The blue areas are calculated 000000 000000 000000 000000 000000 000000 000000 13417 78811134 96210551 12613321 9810252 22410302 354 FebMarAprMayJunJul 55 15343 13739 23345 04040 07842 812 3 0513 6483 7582 7043 1822 959 3 2622 9992 2533 0742 5132 961 2 3702 5842 7192 4502 3992 562 1 5471 0941 1381 0931 1061 389 3 0602 6453 5952 6703 0253 635 3 9673 3713 9833 4233 0533 568 3 3822 3462 2722 9172 8102 382 47 29150 79247 92540 72540 94938 643 5134 155657 486026 574456 634970 474675 7 FebMarAprMayJunJul 66 30066 30066 30066 30066 30066 300 3 8503 8503 8503 8503 8503 850 3 6503 6503 6503 6503 6503 650 3 4103 4103 4103 4103 4103 410 1 5301 5301 5301 5301 5301 530 3 5303 5303 5303 5303 5303 530 4 0604 0604 0604 0604 0604 060 3 5303 5303 5303 5303 5303 530 57 89057 89057 89057 89057 89057 890 6 5506 5506 5506 5506 5506 550 154300154300154300154300154300154300 FebMarAprMayJunJul 13 41811 13510 55113 32210 25210 302 13 418 11 135 10 551 13 322 10 252 10 302 16 90017 20018 40015 80018 00018 200 24 60025 80020 60023 70019 60025 300 66 30066 30066 30066 30066 30066 300 3 8503 8503 8503 8503 8503 850 3 6503 6503 6503 6503 6503 650 3 4103 4103 4103 4103 4103 410 1 5301 5301 5301 5301 5301 530 3 5303 5303 5303 5303 5303 530 4 0604 0604 0604 0604 0604 060 3 5303 5303 5303 5303 5303 530 57 89057 89057 89057 89057 89057 890 6 5506 5506 5506 5506 5506 550 209 218 208 435 203 851 207 122 202 152 208 102 89101112 AugSepOctNovDec 15 90810 89813 36313 65516 043 16 54415 73220 10817 24316 504 AugSepOctNovDec 17 20018 10019 50019 90015 200 23 50023 20024 60022 90022 000 5 8 9 10 14 AugSepOctNovDec 00000 00000 00000 00000 00000 00000 AugSepOctNovDec AugSepOctNovDec AugSepOctNovDec 00000 00000 00000 00000 00000 00000 00000 9926 169438 91212064 9810345 9569902 616 AugSepOctNovDec 41 36338 14646 17247 19749 210 3 0212 9423 6043 7363 427 2 7642 4483 0862 4343 038 2 4232 0312 6802 7422 494 1 4791 1171 1221 5001 273 3 4743 0843 3702 8032 806 2 9513 2493 5503 6023 779 2 5132 4202 5422 8152 256 38 66149 14040 66348 32240 711 5968 85352 145008 585936 035254 98 AugSepOctNovDec 66 30066 30066 30066 30066 300 3 8503 8503 8503 8503 850 3 6503 6503 6503 6503 650 3 4103 4103 4103 4103 410 1 5301 5301 5301 5301 530 3 5303 5303 5303 5303 530 4 0604 0604 0604 0604 060 3 5303 5303 5303 5303 530 57 89057 89057 89057 89057 890 6 5506 5506 5506 5506 550 154300154300154300154300154300 AugSepOctNovDec 9 9269 43912 06510 3469 903 9 926 9 439 12 065 10 346 9 903 17 20018 10019 50019 90015 200 23 50023 20024 60022 90022 000 66 30066 30066 30066 30066 300 3 8503 8503 8503 8503 850 3 6503 6503 6503 6503 650 3 4103 4103 4103 4103 410 1 5301 5301 5301 5301 530 3 5303 5303 5303 5303 530 4 0604 0604 0604 0604 060 3 5303 5303 5303 5303 530 57 89057 89057 89057 89057 890 6 5506 5506 5506 5506 550 204 926 205 039 210 465 207 446 201 403 1998 Revenue NAME 225430 2 BUDGET EXAMPLE Company is one which sells computers and peripherals world wide by mail order and assembles PC s from purchased parts Budgets by Region by Product and by Month Forecasts updated in 4 month moving window Primary driver variable is sales level of each product units This is related to advertising and telemarketing expe