Question

In: Accounting

MBA-AC721 Project Part 2 Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet...

MBA-AC721 Project Part 2 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)

NOTE: 1 FTE = 2080 hours

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

Expert Solution

Budgeted Income statement

Budgeted Sales Instore 5,000 Bikes

Online 75,000 Bikes

Total Budgeted sales 80,000 Bikes

Budgeted Sale price /Unit $104

Total Budgeted sales (in $) ($104*80,000) =$83,20,000

Budgeted Purchase Price/Unit $52

Total Budgeted Purcahse Price ($52*80,000)= $41,60,000

Net Contribution ($83,20,000- $41,60,000)= $41,60,000

Other Cost

Managerial Staff Salary ($45,000+20% of $45000(i.e. $9,000)= $54,000

Bookekeeper Salary($40,000 +20% of $40,000(i.e. $8,000) =$48,000

Purachsing and receivingSupervisor salary= ($35,000 +20%of $35,000i.e.($7,000)=$42,000

Other Staff Warehouse Salary (2.5 * 2080* $15) =$78,000

Other benefits for Staff Warehouse (17% of $ 78,000)= $13,260

Online Sales Staff (2 * 2080 *$15) =$ 62,400

Benefits for Online sales staff (17% of $ 62,400)= $10,608

Sales Clerk Salary

(1FTE=2080 HOURS ,OFFICE TIME=10A.M. TO 6P.M. MEANS 8 HRS A DAY.HENCE TOTAL WORKING DAY =2080/8=260DAYS

2 SALES CLERK * 5 HRS/DAY * $ 15/HR * 260 days =$ 39,000

3 SALES CLERK *5HRS/DAY * $15/HR * 260 DAYS= $ 58,500

4 SALES CLERK *8HRS/DAY *$ 15/HR *52 SATURDAYS = $24,960

BENEFITS FOR INSTORE SALES STAFF 17% OF( $39,000+$58,500+$24,960)=$1,22,460

INSTORE SALES STAFF COMMISSION = $52,000

(Instore Sales =5000 Units at $ 104=52,00,000 * 10%)

Budgeted Utility Cost =$ 13,200

Budg.marketing Cost = $1,75,000

Other Cost =$13,00,000

Contribution and Community Service (10% of Instore revenue)= $52,000

Hence Total of Other Cost other than Purcahse price= 54,000+ 48000+42000+78,000+13260+62400+10608+39000+58500+24960+122460+52000+13200+175000+1300000+52000= 21,45,388

Net Revenue= Net Contribution-Other Cost

=4160000-2145388=2014612

Income tax Provision(29% of 2014612)=584237.48

Budgeted Profit after tax = ( Net Revenue -tax)=2014612-584237.48=$1430374.52

ekkarill92 answered 1 year ago

Create the 2018 budget for Buy-Right Bike Store (BRBS). Add a worksheet to your Project Excel...

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 ...

Please provide a step by step solution create a Vacation Budget worksheet Add at least 6...

Please provide a step by step solution create a Vacation Budget worksheet Add at least 6 other expenses related to your planned vacation (car rental etc.) enter an estimate of the cost of for each item Use a function to calculate the total estimated costs. g) Enter a function to calculate the average cost per day. h) Create a chart (you choose the type) in this same worksheet based on this trip to display the estimated costs. Add a title...

When preparing a budget to buy a new home, you should add an additional ____ percent...

When preparing a budget to buy a new home, you should add an additional ____ percent to the monthly payment on the loan for such things as homeowner's insurance, property taxes, maintenance, and upkeep.

Week 5 Project Built Right Bike Company (BRBC) is an established manufacturer of quality bicycles. They...

Week 5 Project Built Right Bike Company (BRBC) is an established manufacturer of quality bicycles. They manufacture three styles of bicycles. Bicycle A is a popular racing bicycle primarily sold to dealers in the bicycle racing circuit. Their material is lightweight and durable with detachable joints for easy disassembly and storage. This market has been declining over the past couple of years. Bicycle B is a sturdy leisure bike typically sold to resorts for use by vacationers. This market is...

Create a project charter for opening a Target store in Canada.Include the purpose of the...

Create a project charter for opening a Target store in Canada. Include the purpose of the project, any assumptions/constraints, scope description, and any milestones the project would incur.

Download the data type class named as MathOperation:(given below) Create the project of part 2 then...

Download the data type class named as MathOperation:(given below) Create the project of part 2 then add class as a data type class and class MathCalculator_yourLastName as a driver class with main() of part 2 a. Draw UML of class MathOperation (See the topic about UML at TIP FOR LAB on eCampus) b. Create the pseudo-code or draw flowchart of the main of a driver class based on the requirement listed below (see the topic about pseudo-code or flowchart at...

Adia Davis Principles of Accounting 2: Summer 2018 - Accounting 206 Master Budget Project instructions |...

Adia Davis Principles of Accounting 2: Summer 2018 - Accounting 206 Master Budget Project instructions | help [The following information applies to the questions displayed below.] Near the end of 2015, the management of Dimsdale Sports Co., a merchandising company, prepared the following estimated balance sheet for December 31, 2015. DIMSDALE SPORTS COMPANY Estimated Balance Sheet December 31, 2015 Assets Cash $ 35,000 Accounts receivable 520,000 Inventory 95,000 Total current assets $ 650,000 Equipment $ 576,000 Less accumulated...

Step 2: Create Stored Procedures to Add/Update/Delete an entity table Create a script to create a...

Step 2: Create Stored Procedures to Add/Update/Delete an entity table Create a script to create a table named ProjectBilling which will have the following columns: • projectBillID char(6) : A 6 character unique identifier (numbers and letters) • TransAmount decimal(16,9) : The amount of the transaction • TransDesc varchar(255): A description of the transaction • TransDate datetime: The date of the transaction • projectID char(4):The Id of the project • accountMgr char(8):The employee who manages the bill ledger Include this...

Chapter 9 project 1 & 2(these are combined into one project) : Add methods to the...

Chapter 9 project 1 & 2(these are combined into one project) : Add methods to the Student class that compare two Student objects. One method should test for equality. The other methods should support the other possible comparisons. In each case, the method returns the result of the comparison of the two students' names. Place several Student objects into a list and shuffle it. Then run the sort method with this list and display all of the students' information. I...

Dietary Analysis Project PART 2: ANALYSIS Part 2 of the Dietary Analysis Project requires you to...

Dietary Analysis Project PART 2: ANALYSIS Part 2 of the Dietary Analysis Project requires you to thoughtfully analyze your current dietary practices. Part 2 should be completed using professional writing. Your analysis should include the following paragraphs. ? First Paragraph: Evaluation of your current energy intake and Body Mass Index (BMI): How does your energy (kcal) intake compare to your recommendation? Were you surprised by your energy intake? Why or why not? Calculate your Body Mass Index (BMI). Does your...