Homework SourseCreate the 2018 budget for BuyRight Bike Store BRBS Add a wo
Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel workbook for BRBC Budgeted Income Statement and create the 2018 budget using the following information.
Budgeted Sales of Bike C: Online 75,000 bikes; instore 5,000 bikes.
Bike C is purchased from Built-Right Bike Company, a sister company in the Biltmore Bicycle Corporation (BMBC), for $52 and is sold for $104.
Inventory:
Beginning inventory: $ 52,000
Purchases 4,108,000
Sales ?
Ending Inventory $ 52,000
Employees:
Managerial Staff:
Manager: 1 FTE, $45,000 annual salary plus 20% benefits
Bookkeeper: 1 FTE, $40,000 annual salary plus 20% benefits
Purchasing & Receiving Supervisor: 1 FTE, $35,000 annual salary plus 20% benefits
Other staff:
Warehouse: 2.5 FTEs make $15 per hour plus 17% benefits
Online sales staff: 2 FTEs, $15 per hour plus 17% benefits (no commission)
Store Hours:
Monday – Saturday 10:00 am – 6:00 pm
2 sales clerks work from 10-3 M-F
3 sales clerks work from 3-6 M-F
4 sales clerks work 10-6 on Saturdays
Sales clerks earn $15 per hour plus 17% benefits
Instore sales staff share commission equal to 10% of instore sales.
Budgeted Utility cost: $13,200
Budgeted Marketing Cost: $175,000
Contributions & community service: 10% of instore revenue
Other costs (includes depreciation, insurance, etc): $1,300,000
Income tax 29% of Net Revenue
Solution
FTE are assumed to be paid for 2,080 hours per year.
Cost of Goods Sold is taken only for the quantity sold i.e. 80,000 bikes (including Instore as well as online sales).
| BRBC Budgeted Income Statement | ||
| Sales: | ||
| Online (75,000*104) | 7,800,000 | |
| Instore (5,000*104) | 520,000 | 8,320,000 |
| Cost of Goods Sold (80,000*52) | (4,160,000) | |
| Managerial Staff Salary: | ||
| Manager Salary | (45000) | |
| Manager Benefits (20% of Salary) | (9,000) | (54,000) |
| Bookkeeper Salary | (40,000) | |
| Bookkeeper Benefits (20% of Salary) | (8,000) | (48,000) |
| Purchasing & Receiving Supervisor Salary | (35,000) | |
| Purchasing & Receiving Supervisor Benefits (20% of Salary) | (7,000) | (42,000) |
| Other Staff Salary: | ||
| Warehouse Staff Salary (2.5FTE*2080 Hours *15) | (78,000) | |
| Warehouse Staff Benefits (2.5 FTE*2080 Hours *15) | (13,260) | (91,260) |
| Online Sales Staff Salary (2 FTE*2080 Hours *15) | (62,400) | |
| Online Sales Staff Benefits (17% of Salary) | (10,608) | 73,008 |
| Sales Clerk Salary: | ||
| 2 Staff * 5 Hours Daily * 5 Days * 52 Weeks * 15 Per Hour | (39,000) | |
| 3 Staff * 3 Hours Daily * 5 Days * 52 Weeks * 15 Per Hour | (35,100) | |
| 4 Staff * 8 Hours * 1 Day * 52 Weeks * 15 Per Hour | (24,960) | |
| Benefits (17% of (39,000+35,100+24,960)) | (16,840) | |
| Commission (10% of Instore Sales) | (52,000) | (167,900) |
| Budgeted Utility Cost | (13,200) | |
| Budgeted Marketing Cost | (175,000) | |
| Contribution & community service (10% of Instore Sales) | (52,000) | |
| Other costs (includes depreciation, insurance, etc.) | (1,300,000) | |
| Net Revenue | 2,143,632 | |
| Income Tax (29% of Net Revenue) | 621,653 | |
| Net Profit | 1,521,979 |
