Assignment (make excel sheets) Master Budget, Cash Budget and Budgeted Income Statement

After two years study at University, you finally graduate and start a job as Junior accountant at Bigdream Inc. Your manager is responsible for the nationwide distribution of creator design sets. Because of the new social media influence among current generation, the company has grown rapidly, and the prompt growth forces the management team to improve their efficiency and manage their production effectively.

You have just been given responsibility for all planning and budgeting of the entire lighting set division. Today is your first day, you have just given an assignment to prepare master budget for the manager, who needs to present the budget and discuss the financial objectives with the shareholders tomorrow. During your job interview, you clearly stated that you gained managerial accounting knowledge and hand on experience during your MBA study.

Your first assignment is to prepare a master budget for the next fiscal year, starting January 1, 2021. Your co-work has left a pile of files on your desk including the past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture. Now, you dont know where to start. Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your laptop from the accounting course.

Note: The company desires a minimum ending cash balance each month on $850,000. The Bigdream products are sold to retailers for $129 each and the sales have been in demand due to the Covid-19. However, the marketing department has been conservative toward the end of the year due market saturation. The marketing department has just sent you their forecasted quarter sales and marketing budget.

Quarter

2021 Q1

2021 Q2

2021 Q3

2021 Q4

2022 Q1

2022 Q2

Sales in Unit

30,000

35,000

40,000

55000

50000

55,000

Marketing Expenses

$35,000

$20,000

$20,000

$35,000

$35,000

$25,000

Ending finished goods inventories are supposed to be equal to 20% of the next quarters sales in units. Big dream currently does its own assembly production in house. Each unit consists of 3 LED panels and the cost of each is $15. Each unit needs 0.10 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 30% of the next quarter production.

Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The Bigdream has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 30% is collected in the quarter following the sale, and the remaining 20% is collected in the second quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable.

Below is a display of the Bigdream division monthly manufacture overhead and selling and administrative expenses:

Manufacture Overhead

Variable:

Indirect labour $5 per labour hour

Indirect Materials $2.00 per labour hour

Fixed:

Wages and Salaries $2,000 per month

Utilities $1,500 per month

Insurance $2,000 per month

Depreciation $2,000 per month

Selling and Administrative

Variable:

Sales Commissions $5 per unit

Fixed (Monthly):

Wages and Salaries $22,000

Utilities $6,000

Insurance $1,200

Depreciation $1,500

Miscellaneous $3,000

Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course). Bigdream will make a purchase of a parcel of land during the first quarter of 2021 for $50,000 cash. Bigdreams balance sheet at the end of the fourth quarter is shown below:

Assets

Cash $35,000

Uncollected Accounts receivable in Q4 this year: $200,000

Liabilities

Accounts payable $85,700

An agreement with Bank of the West allows Bigdream to borrow up to a total loan amount of $550,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only.

Required:

Prepare a master quarterly budget from Jan, 2021 to Dec, 2021. Include the following budget schedules and financial statements:

1) Master Budget

2) Cash Budget. Show the cash budget by month and in total.

3) Budgeted Income statement

I Normal 11 No Spa. Heading Heading 2 Title Paragraph Styles Individual Case Study Master Budget, Cash Budget and Budgeted Income Statement After two years study at UCW you finally graduate and start a job as Junior accountant at Biadream Inc. Your manager is responsible for the nationwide distribution of creator design sets. Because of the new social media influence among current generation, the company has grown rapidly, and the prompt growth forces the management team to improve their efficiency and manage their production effectively You have just been given responsibility for all planning and budgeting of the entire lighting set division. Today is your first day, you have just given an assignment to prepare master budget for the manager, who needs to present the budget and discuss the financial objectives with the shareholders tomorrow. During your job interview, you clearly stated that you gained managerial accounting knowledge and hand on experience during your MBA study Your first assignment is to prepare a master budget for the next fiscal year, starting January 1, 2021. Your co-work has left a pile of files on your desk including the past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture Now, you don't know where to start Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your laptop from the accounting course Note: The company desires a minimum ending cash balance each month on $850,000 The Bigdream products are sold to retailers for $129 each and the sales have been in demand due to the Covid-19 However, the marketing department has been conservative toward the end of the year due market saturation The marketing department has just sent you their forecasted quarter sales and marketing budget O RE eferences Mailings Review View Help Grammarly Search T 1 No Spac... Heading 1 Heading 2 I Normal Title Paragraph Styles conservative toward the end of the year due market saturation. The marketing department has just send you their forecasted quarter sales and marketing budget Quarter 2021 Q1 2021 Q2 2021 Q3 2021 Q4 2022 Q1 2022 Q2 Sales in 30,000 35,000 40,000 55000 50000 55,000 Unit Marketing $35,000 $20,000 $20,000 $35,000 $35,000 $25,000 Expenses Ending finished goods inventories are supposed to be equal to 20% of the next quarter's sales in units. Big dream currently does its own assembly production in house. Each unit consists of 3 LED panels and the cost of each is $15. Each unit needs 0.10 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 30% of the next quarter production. Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the Individual Case=22222.3: talliranda References Mailings Review View Help Grammarly Search A 20 19. 1 A- . 1 No Spac... Heading 1 Heading 2 T Normal Title Paragraph Styles distributors are made on credit terms with no discount (for now), and payable within 15 days. The Bigdream has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 30% is collected in the quarter following the sale, and the remaining 20% is collected in the second quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the Bigdream division monthly manufacture overhead and selling and administrative expenses Manufacture Overhead Variable: Indirect labour $5 per labour hour Indirect Materials $2.00 per labour hour Fixed: Wages and Salaries $2,000 per month Utilities $1,500 per month Insurance $2,000 per month Depreciation $2,000 per month Selling and Administrative Variable: Sales Commissions $5 per unit Fixed (Monthly) Wages and Salaries $22,000 Utilities $6,000 Insurance $1 200 Depreciation $1,500 d Individual Case=? 2020: ballirandhawa References Mailings Review View Help Grammarly Search A EE-SE-E211 le -- 1 No Space Heading 1 Heading 2 Go Re T Normal Title Styles Ed Paragraph Vagus aramUS 2,000 PER MOTTO Utilities $1,500 per month Insurance $2,000 per month Depreciation $2,000 per month Selling and Administrative Variable: Sales Commissions $5 per unit Fixed (Monthly) Wages and Salaries $22,000 Utilities $6,000 Insurance $1,200 Depreciation $1,500 Miscellaneous $3,000 Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the month in cash, with the exception of depreciation (of course) Bigdream will make a purchase of a parcel of land during the first quarter of 2021 for $50,000 cash Bigdream's balance sheet at the end of the fourth quarter is shown below: Assets Cash $35.000 Uncollected Accounts receivable in Q4 this year. $200,000 Liabilities Accounts payable $85,700 An agreement with Bank of the West allows Bigdream to borrow up to a total loan amount of $550,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded meaning this is simple interest only | bC T No Spac... Heading 1 Heading 2 Find e Repla 1 Normal Title 2 Selec Paragraph Styles Editing An agreement with Bank of the West allows Bigdream to borrow up to a total loan amount of $550,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only Required: Prepare a master quarterly budget from Jan, 2021 to Dec, 2021. Include the following budget schedules and financial statements. 1) Master Budget 2) Cash Budget. Show the cash budget by month and in total, 3) Budgeted Income statement Assignment (make excel sheets) Master Budget, Cash Budget and Budgeted Income Statement After two years study at University, you finally graduate and start a job as Junior accountant at Bigdream Inc. Your manager is responsible for the nationwide distribution of creator design sets. Because of the new social media influence among current generation, the company has grown rapidly, and the prompt growth forces the management team to improve their efficiency and manage their production effectively. You have just been given responsibility for all planning and budgeting of the entire lighting set division. Today is your first day, you have just given an assignment to prepare master budget for the manager, who needs to present the budget and discuss the financial objectives with the shareholders tomorrow. During your job interview, you clearly stated that you gained managerial accounting knowledge and hand on experience during your MBA study Your first assignment is to prepare a master budget for the next fiscal year, starting January 1, 2021. Your co-work has left a pile of files on your desk including the past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture. Now, you don't know where to start Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your laptop from the accounting course Note: The company desires a minimum ending cash balance each month on $850,000 individual case = 2020: Saved to thi: PC talandhel References Mailings Review View Help Grammarly Search A EL Q . 1 Normal 1 No Space. Heading 1 Heading 2 Title Paragraph Styles January 1, ZUZT. TUU W Work as vera pre ories on your desk TTTON me past sale records, product information, manufacture schedule and supplier pricing list. Now, you realized that you should have pay more attention during the lecture. Now, you don't know where to start. Fortunately, you remember that you still kept a copy spreadsheet of the master budget template in your laptop from the accounting course. Note: The company desires a minimum ending cash balance each month on $850,000 The Bigdream products are sold to retailers for $129 each and the sales have been in demand due to the Covid-19. However, the marketing department has been conservative toward the end of the year due market saturation. The marketing department has just sent you their forecasted quarter sales and marketing budget 2021 Q1 30,000 2021 Q2 35,000 2021 Q3 40,000 2021 Q4 55000 2022 Q1 50000 2022 Q2 55,000 Quarter Sales in Unit Marketing Expenses $35,000 $20,000 $20,000 $35,000 $35,000 $25,000 Ending finished goods inventories are supposed to be equal to 20% of the next quarter's sales in units. Big dream currently does its own assembly production in house Each unit consists of 3 LED panels and the cost of each is $15. Each unit needs 0.10 labour hour from assemble to finish package. The hourly pay rate to the assembling workers is $15 per hour. The production manager also required desired direct material ending inventory to 30% of the next quarter production Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the Purchases are paid for in the following manner: 50% in the quarter of the purchase and the remaining 50% paid in the quarter following the purchase. All sales to the distributors are made on credit terms with no discount (for now), and payable within 15 days. The Biadream has determined that only 50% of sales are collected by the end of the quarter in which the sale occurred. An additional 30% is collected in the quarter following the sale, and the remaining 20% is collected in the second quarter following the sale. Bad debts have been negligible, supporting the credit terms as favorable. Below is a display of the Bigdream division monthly manufacture overhead and selling and administrative expenses Manufacture Overhead Variable: Indirect labour $5 per labour hour Indirect Materials $2.00 per labour hour Fixed: Wages and Salaries $2,000 per month Utilities $1,500 per month Insurance $2,000 per month Depreciation $2,000 per month Selling and Administrative Variable: Sales Commissions $5 per unit Fixed (Monthly) Wages and Salaries $22.000 Utilities $6,000 Insurance $ 1.200 1 Normal 1 No Spac... Heading 1 Heading 2 Title Styles Paragraph Utilities $1,500 per month Insurance $2,000 per month Depreciation $2,000 per month Selling and Administrative Variable: Sales Commissions $5 per unit Fixed (Monthly) Wages and Salaries $22,000 Utilities $6,000 Insurance $1,200 Depreciation $1,500 Miscellaneous $3,000 Labour, Manufacture Overhead, and Selling and Administrative expenses are all paid during the month, in cash, with the exception of depreciation (of course) Bigdream will make a purchase of a parcel of land during the first quarter of 2021 for $50,000 cash. Bigdream's balance sheet at the end of the fourth quarter is shown below. Assets Cash $35,000 Uncollected Accounts receivable in Q4 this year. $200,000 Liabilities Accounts payable $85,700 An agreement with Bank of the West allows Bigdream to borrow up to a total loan amount of $550,000. The interest rate on these loans is 12% annually (pretty high considering market rates) but the interest is not compounded, meaning this is simple interest only Required: Prepare a master quarterly budget from Jan, 2021 to Dec, 2021. Include the following budget schedules and financial statements I 1) Master Budget 2) Cash Budget Show the cash budget by month and in total. 3) Budgeted Income statement