Excel File Edit View Insert Format Tools Data Window Help () A Australian $ 2 Q 8 0 Thu 12 Sep 1:58:30 PM AutoSave O h A ? ? . C ... MAA103 Budgeting Assignment (2) ~ Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share & Cut Calibri (Body) v 24 AA 27 v ab Wrap Text v General Auto-sum " As [A Copy Fill Paste B Merge & Centre v % " Cell Format U v Av Conditional Format Insert Delete Format Sort & Analyse Formatting as Table Styles Clear v Find & Sensitivity Add-ins Filter Select Data C20 v fx w AA AB AC AD AE AF AG AH AJ AK AL Student ID Student Username Student 1 24486369 $224486369 @ deakin.edu.au Student 2 223469684 $223469684 @deakin.edu.au Student @deakin.edu.au Student 4 @deakin.edu.au Cloud Deakin Group Number: 97 Instructions BEFORE starting your assign Please note that the clier ered in correctly BEFORE starting. Once you have entered your information, please review the PDF Budgeting Assignment Instructions on Cloud Deakin and choose a project below. *Note: As part of the assessment, you need to complete the Cash Budget and Budgeted Income Statement. All of the 'other' budgets are not assessed and will not be marked. They have only been included to elp you carry out any calculations you might need to fulfil the Cash Budget and Budgeted Income Statement. Chosen Project: Cost Management Strategy Cost of Project: Cash Balance at 31 Dec 2024: 409,812 By switching suppliers, the unit cost to purchase Pedal Classic ebikes will drop by 30% while the unit cost of Folding Urban Dream ebikes will remain the same. Tracie would like to keep the selling price of Pedal Classic the same. Due to the slightly less well known branding of the ebikes, Tracie believes that sales volume for the Pedal Classic will fall by 10% for each month in 2025 compared to the corresponding month in 2023. (e.g. Jan 2025's growth/decline is based on Jan 2023, Feb 2025's growth/decline is based on Feb 2023 etc). Tracie believes that the Folding Urban Dream ebikes don't make enough margin and so the plan is to increase the selling price on Folding Urban Dream by 25%. Tracie believes that due to the increase in selling price, Folding Urban Dream's sale volume will decrease by 30% for each month in 2025 compared to the corresponding month in 2023. As a result of the decrease in ebike's demand, Conor estimates the repair hours will also decrease by 20% for each month in 2025 compared to the corresponding month in 2023. The selling price of repairs will remain the same. The catch with the new supplier is that there will be a more stringent credit term in purchases of inventory. All purchases with the new supplier are still on a credit basis, however, 80% of the amount needs to be settled in the same month of purchase with the remaining 20% settled one month later. For the credit term in sales of ebikes, the business plans to continue its instore credit policy for their customers. To further reduce costs, Tracie and Conor would like to reduce the salary of some staff. Specifically, they would like to reduce the office staff's and sale assistants' salary by 15% The repair technicians' salary will remain the same. The business will also be implementing 'green' measures to reduce the utilities consumption of water, gas and electricity by 20%. Conor also believes the business insurance has been a waste of money as they have never needed to make a claim. He plans to change the insurance policy to reduce the annual premium by 50%. In addition, the marketing is considered to be too high, and thus Tracie plans to switch to a new online marketing channel which will reduce the annual marketing expense by 30%. All these changes are relative to 2023 amounts. All other fixed costs will decrease by based on 2023 amounts. After the 1st year of the new project, in 2026, both the service volume and sales volume are expected to increase by 5% for each month in 2026 compared to the corresponding month in 2025. Group_Information FactSheet Cash_Budget IncomeStatement_Budget Sales Budget Cost of Sales Budget Purchases Budget Selling & Admin Expense Budget Schedule of Cash Collection Schedule of Cash Payments + Ready Ex Accessibility: Investigate - + 68% 23 SEP 12 4 GExcel File Edit View Insert Format Tools Data Window Help () A Australian $ 2 Q 8 0 Thu 12 Sep 1:58:34 PM AutoSave O h A ? ? . C ... MAA103 Budgeting Assignment (2) ~ Q Search (Cmd + Ctrl + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share & Cut Calibri (Body) v 24 AA 27 V ab Wrap Text v General Auto-sum " As [A Copy Fill Paste Format B U v AV Merge & Centre v v % " 00 -20 Conditional Format Cell Insert Delete Format Sort & Find & Sensitivity Add-ins Analyse Formatting as Table Styles Clear v Filter Select Data A x v fx AA AB AC AD AE AF AG AH FactSheet - Data on 2023 Financial Year Jan-2023 Feb-2023 Mar-2023 Apr -2023 May- 2023 Jun- 2023 Jul -2023 Aug -2023 Sep-2023 Oct - 2023 Nov - 2023 Dec- 2023 Sales Volume - Folding Urban Dream 279 294 258 243 -252 Service Volume - No. of Hours (Repairs) 254 267 272 257 267 368 362 290 261 256 Selling Price - Pedal Classic 500.00 Cost Price - Pedal Classic 300.00 Selling Price - Folding Urban Dream 800.00 Cost Price - Folding Urban Dream 650.00 Service Revenue Per Hour (Repairs) 80.00 Number of Sale Assistants: 3 Wage Rate Per Employee Per Month 6,000 Number of Office Staff: Wage Rate Per Employee Per Month 5 3,500 Number of Repair Technicians Wage Rate Per Employee Per Month 5,500 Additional Information: oth Tracie and Conor are owners within the business and each of them draws an amount of $1989 per month. Each owner plans on increasing the drawings to $5967 per month starting from January of 2025. As the manager, Tracie keeps a close eye on inventory management and would like to always maintain a monthly ending inventory requirement of 20% of next month's sales. You can assume the beginning inventory for Jan 2025 to be 0 units for both Pedal Classic and Folding Urban Dream ebikes. All purchases of inventory with the current supplier are on a credit basis, with 60% of the amount settled in the same month of purchase and the remaining 40% settled one month after the month of purchase. You can assume that all credit amounts for purchase of inventory prior to January 2025 have been settled. he business typically will have 70% of their sales of Pedal Classic and Folding Urban Dream ebikes as cash, with the remaining 30% on credit. Of the credit sales, 50% is collected in the month of sales, 30% is collected one month after the sales, and 0% is collected two months after the sales. You can assume that all credit amounts for sale of inventory prior to January 2025 have been settled. All customers coming in for ebikes repairs pay for the service in cash 100% of the time the business has the following non-current assets. In 2023, the business purchased one specialised equipment to provide repairs for the ebikes. The total cost of such one equipment is $26424 and it is expected to last 6 years. Other office equipment has a total cost al cost of $18480 and depreciates at 20% pe clares at 2076 per annum. Shop fittings were originally purchased at $86520 and depreciate at 10% per annum. All non-current assets have $0 residual value and are d e depreciated using the straight-line method of depreciation. Other Fixed Costs: Marketing expense of $1968 per annum is paid and incurred evenly across 12 months 6084 of business insurance is paid in advance every year on 1st January. This insurance covers the period Ist Jan to 31st Dec every year (50% store; 50% office). bank fees work out to be $100 per month and are paid at the end of each quarter On average, the office supplies are purchased, paid and expensed every month to the amount of $135. Council rates are $1152 per year and are usually paid in December each year. The rates cover the period Ist Jan to 31st Dec every year. (60% store; 40% office) ent is paid at the start of each quarter and works out to be $8050 per month. (80% store; 20% office) Water, gas and electricity are combin ds up to $615 per quarter and is usually paid at the end of each quarter. (70% store; 30% office) "Note: Quarter 1 runs from 1 Jan- 31 Mar; Quarter 2 runs from 1 Apr - 30 Jun; Quarter 3 runs from 1 Jul - 30 Sep; Quarter 4 runs from 1 Oct - 31 Dec. Group_Information FactSheet Cash_Budget IncomeStatement_Budget Sales Budget Cost of Sales Budget Purchases Budget Selling & Admin Expense Budget Schedule of Cash Collection Schedule of Cas Ready TX Accessibility: Investigate 1070 23 SEP 12 GExcel File Edit View Insert Format Tools Data Window Help ()) A Australian $ 2 Q 8 0 Thu 12 Sep 1:58:38 PM AutoSave O h A ? ? . C ... MAA103 Budgeting Assignment (2) ~ Q Search (Cmd + Ctri + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share & Cut Calibri (Body) 11 AA 2 7 V de Wrap Text v General Auto-sum " Av [A Copy Fill Paste Cell Format BIU MVAv Merge & Centre v $ ~ % 9 00 -20 Conditional Format Insert Delete Format Sort & Analyse Formatting as Table Styles Clear v Find & Sensitivity Add-ins Filter Select Data B24 x fx 'Salaries Expense - Office Staff D G M N O CASH BUDGET Jan-25 Feb-25 Mar-25 Apr-25 May-25 Jun-25 Jul-25 Aug-25 Sep-25 Oct-25 Nov-25 Dec-25 TOTAL OPENING CASH BALANCE CASH INFLOWS: Collections From Customers Bank Loan 10 11 TOTAL CASH AVAILABLE: 12 13 CASH OUTFLOWS 14 Payments For Purchases 15 Insurance Expense 16 Bank Fees 17 Council Rates 18 Office Supplies Expense 19 Utility Expense 20 Marketing Expenses 21 Rent Expense 22 Salaries Expense - Sales Assistant 23 Salaries Expense - Technicians 24 Salaries Expense - Office Staff 25 Drawings By Owners 26 Capital Investment (initial setup) 27 Purchase of Equipment 28 Interest Expense 29 30 31 32 CLOSING CASH BALANCE 34 35 37 48 49 Group_Information FactSheet Cash_Budget IncomeStatement_Budget Sales Budget Cost of Sales Budget Purchases Budget Selling & Admin Expense Budget Schedule of Cash Collection Schedule of Ca Ready x Accessibility: Investigate 23 SEP 12 GExcel File Edit View Insert Format Tools Data Window Help ()) A Australian $ 4 Q 8 0 Thu 12 Sep 1:58:41 PM AutoSave O h A ? ? . C ... MAA103 Budgeting Assignment (2) ~ Q Search (Cmd + Ctri + U) Home Insert Draw Page Layout Formulas Data Review View Automate Comments Share & Cut Calibri (Body) 11 AA 2 7 V ap Wrap Text v General Auto-sum " As [A Copy Paste Fill Format B U v MvAv Merge & Centre v $ ~ % " 00 -20 Conditional Format Cell Insert Delete Format Sort & Find & Sensitivity Add-ins Analyse Formatting as Table Styles Clear v Filter Select Data D55 4 X V fx BUDGETED INCOME STATEMENT Jan-25 Feb.25 Mar-25 Apr-25 May-25 Jun-25 Jul-25 Aug-25 Sep-25 Oct-25 Nov-25 Dec-25 TOTAL Sales Revenue Cost of Goods Sold Gross Profit other Income Service Revenue Operating Expenses Selling Expenses: Marketing Expenses Depreciation Expense - Shop Fittings Depreciation Expense - Repair Equipment Salaries Expense - Sales Assistant Salaries Expense - Technicians Utility Expense Council Rates Insurance Expense Depreciation Expense - Initial Setup General & Administrative Expenses: Insurance Expense Council Rates Office Supplies Expense Utility Expense Depreciation Expense - Office Equipment Salaries Expense - Office Staff Total Operating Expense Net Operating Profit on-Operating Income and Expenses Interest Expense Profit (Loss) before income tax 4 Group_Information FactSheet Cash_Budget IncomeStatement_Budget Sales Budget Cost of Sales Budget Purchases Budget Selling & Admin Expense Budget Schedule of Cash Collection Schedule of Ca Ready Ax Accessibility: Investigate UJ 70 23 SEP 12 G