Rachel Janet is preparing the 2024 budget for one of BestKayak’s roto-moulded

Rachel Janet is preparing the 2024 budget for one of BestKayak’s roto-moulded kayaks. You are tasked to help her prepare the Master Budget.

Detailed instructions:

Select Attempt Quiz now and go to Question 1 to obtain your Master Budget case information. 

Next, open a brand new Excel sheet, save it under the file name “COMM305_WT2024_DA2_StudentID.xlsx”, and prepare a complete Master Budget in Excel with dynamic cell references that entails the following parts:

In the first Excel Sheet, prepare by month and in total for 2024 the following:

Sales budget

Production budget

Direct materials budget

Direct labour budget

Manufacturing overhead budget

Selling and administrative budget

Budgeted income statement

In the second Excel Sheet, prepare the cash budget by month and in total for 2024

In the third Excel Sheet, prepare the budgeted annual balance sheet as of December 31, 2024 (no monthly information needed for the budgeted balance sheet).

After you have completed the Master Budget in Excel, answer the remaining 25 quiz questions in the Data Analytics Assignment 2 Quiz. The 25 quiz questions ask about specific values from your Master Budget.

At the end of the quiz, in Question 26, upload your Excel sheet that entails the Master Budget.

Once you have completed the quiz and you are ready to submit your answers, click  “Finish Attempt”, check the Summary of Attempt page, and then select the “Submit all and finish” button. Any open attempts will be automatically submitted on the quiz due date and time, which is displayed at the top left of this page.

The quiz is not timed and will be available to you anytime until the assignment close date. If you wish to exit the quiz and return later to finish it, make sure to not submit the quiz (your answers will be saved and available for changes unless the quiz is submitted).

 

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: Make sure to link your cells and calculation in Excel to avoid any rounding issues. You should not round any numbers in the Excel sheet. Even do not round, for example, the required production units of Kayaks (e.g., 737.800 required production of kayaks), or the amount of direct materials purchases (e.g., 27931.56800 direct materials purchases) in your Excel. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question number ABC from $25.674 (in Excel) to $25.67 (for the Moodle quiz answer).

1) Rachel Janet is preparing the 2024 budget for one of BestKayak’s roto-moulded kayaks. Extensive meetings with members of the sales department and executive team have resulted in the following unit sales projections for 2024.

Jan

Feb

Mar

Apr

410

466

753

857

May

Jun

Jul

Aug

952

1153

1340

1088

Sep

Oct

Nov

Dec

509

459

340

1045

Selling price is $571. For promotion activities, BestKayak’s gives a 10% discount on selling price in September and December. All sales are on credit. The company expects that it can collect 20% of sales in the month of sales, 50% of sales in the next month, and the remaining 30% in second month. 

Sales in November 2023 totaled to 250 units at the selling price of $571 and in December 2023 to 1000 units at the 10% discount selling price.

BestKayaks’ policy is to have finished goods ending inventory in a month equal to 20% of the next month’s anticipated sales. This policy also applied to ending inventory of finished goods on December 31, 2023.

Preliminary sales projections for 2025 are 550 units for January and 420 for February.

 

Production of each kayak requires 47 kg of polyethylene powder and one finishing kit (rope, seat, hardware, etc.). Company policy is that the ending inventory of polyethylene powder should be 22% of the amount needed for production in the next month. Further, BestKayaks’ policy is to always keep 59 finishing kits as ending inventory. This means for example that the ending inventory of finishing kits is 59 units on December 31, 2023. The polyethylene powder used in these kayaks cost $1.5 per kilogram, and the finishing kits cost $153 each.

Production of a single kayak requires 2 hours of time by more experienced, type I employees and 3 hours of finishing time by type II employees. Direct labour costs for type I employees are $15 per hour and $10 per hour for type II employees.

BestKayak expects variable manufacturing overhead costs to fluctuate with the production volume based on the following rates per type II employees direct labour hours:

 

Indirect materials: $1 per type II employees direct labour hour

Indirect labour: $1.40 per type II employees direct labour hour

Utilities: $0.50 per type II employees direct labour hour

Maintenance: $0.20 per type II employees direct labour hour

 

In addition, BestKayak expects the following fixed manufacturing overhead to occur each month:

 

Supervisory salaries: $19964

Depreciation: $3542

Property taxes and insurance: $8137

Maintenance: $5685

 

The Company uses machine hours of type II employees to allocate manufacturing overhead. The predetermined manufacturing overhead is calculated based on annual estimates.

Variable selling and administrative expenses for this line are expected to be 4% of sales for sales commissions and $10 per unit for freight out. In addition, monthly fixed selling and administrative expenses are estimated to be as follows:

 

Advertising: $7500

Sales salaries: $17000

Office salaries: $7000

Depreciation: $735

Property taxes and insurance: $1000

 

BestKayak pays 70% of its direct materials purchases in the month purchased and the remaining 30% in the next month. All other cash-effective costs are paid in the months incurred.

Include following additional assumptions in the preparation of the Master Budget:

The beginning cash balance is 95000, which is the required minimum cash balance.

Ignore income taxes.

Assume that the direct materials prices, direct labour rates, variable manufacturing rates, and monthly fixed manufacturing costs remain constant in 2023 and 2024. This means, for example, that you can use the product unit cost from 2024 to determine the beginning finished goods inventory or the direct materials prices from 2024 to calculate the beginning raw materials inventory.

The balance of Property, Plant, and Equipment, net of depreciation is $420000 as of December 31, 2023.

Equity: BestKayak finances its operations with common share equity of $600000. It plans to pay out dividends of $120000 in October 2024.

 

Use the following formula to determine the beginning retained earnings balance: Beginning retained earnings = $95000 beginning cash balance + Beginning accounts receivable balance + Beginning finished goods balance + Beginning raw materials balance + $420000 Property, Plant, and Equipment, net of depreciation – Beginning accounts payable balance – $200000 beginning long-term liabilities – $600000 beginning common shares balance. Note that the amount of retained earnings is most likely as decimal number – do not round it in your Excel sheet.

Long-term liabilities: BestKayak finances its operations with an interest-bearing long-term loan of $200,000. An interest expense of 7% per annum is expected for the long-term loan, which is paid monthly. Note that the interest expense for the long-term loan is included in the financing section of the cash budget. BestKayak plans to pay back 10% of its long-term loan in December, if the cash balance allows for it.

Short-term financing: In case additional, short-term debt is needed, BestKayak has a line of credit available with its bank at an interest rate of 8% per annum. This potential short-term loan works as follows: The company will borrow and repay in multiples of $5000 from the line of credit. It makes all borrowings from the line of credit at the beginning of a month and makes all repayments at the end of a month. It pays interest only on the portion of the short-term loan (line of credit) that is repaid.

What are the budgeted total sales for the year 2024?

 

 

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

2) What are the budgeted total sales for December 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

3) How many units are required to be produced in January 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

4) What are the desired ending finished goods units in May 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

5) What are the budgeted total costs of polyethyle powder purchases in February 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

6) How many kilograms of polyethyle are desired for the end of March 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

7) How many finishing kits are budgeted for in the beginning balance of March 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

8) What are the budgeted total costs of finishing kit purchases in April 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

9) How many Type I Direct Labour hours are required in May 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

10) What are the budgeted total direct labour cost for Type II in June 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

11) How many Type II Direct Labour hours are required in July 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

12) What are the total budgeted variable manufacturing overhead costs in June 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

13) What are the total budgeted fixed manufacturing overhead costs for the year 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

14) What are the total budgeted variable selling, general, and administrative costs for July 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

15) What are the total budgeted fixed selling, general, and administrative costs for the year 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

16) What are the total cash receipts in October 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

17) What are the cash disbursements for total manufacturing overhead costs in September 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

18) What are the cash disbursements for total selling, general, and administrative costs in November 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

19) What is the amount of interests paid in November 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

20) What is the outstanding amount of a short-term loan (line of credit) on December 31, 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

21) What is the budgeted amount of property, plant, and equipment, net of depreciation on December 31, 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

22) What is the expected accounts receivable balance on December 31, 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

23) What is the expected raw materials inventory balance on December 31, 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).

24) What is the expected accounts payables balance on December 31, 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz)

25) What is the expected total available cash in January 2024?

Important Note 1: Only report the numerical value, do not add a “plus” or minus” sign in your answer to the question.

Important Note 2 on rounding: You should not round any numbers in the Excel sheet. Only round your final answer to two decimals when answering the 25 quiz questions, if applicable. Random example: Round answer to question ABC from $25.674 (in Excel) to $25.67 (in Moodle quiz).